Solved

Changing Report Properties in Visual Basic

Posted on 2004-10-08
21
243 Views
Last Modified: 2013-11-25
Hello All,

Just having a little bit of trouble changing report properties in Visual Basic. What I have is a report that can be run from two separate queries, and I want to dictate which query to run it off of using code.

Here is what I have so far:

Dim myRpt As Report
Set myRpt = Reports!rptRoomStatistics
myRpt.RecordSource = "qryArchivedRoomStatistics"
***Or it may be set to what below depending on where i want to pull the information
***from.
myRpt.RecordSource = "qryDayTableRoomStatistics"


I can compile the code fine, but then when I go to run it I get a run time error.

Run-time Error '2451'
The report name 'rptRoomStatistics' you entered is misspelled or refers to a report that isn't open or doesn't exist.

Now I've checked the spelling, and it's fine, so my next question is? Does this mean that I have to have the report open to change the "Record Source". It seems counter productive to have to open the report, change the record source, then requery/reopen the report.

Kinda baffled right now, and seeking outside input.

Thanks again to all who respond. FreeLancer
0
Comment
Question by:FreeLancer
  • 8
  • 6
  • 3
  • +2
21 Comments
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 12262342
You can't refer to a form or report properties unless it is open, at least in design view if nothing else.
0
 
LVL 10

Accepted Solution

by:
calpurnia earned 250 total points
ID: 12262446
Try using:  

Dim strReportName As String
strRecordSource = "qryArchivedRoomStatistics"
DoCmd.OpenReport "rptRoomStatistics", acViewPreview, , , , strReportName

then in the Open event of your report, put

    Me.RecordSource = Me.OpenArgs
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12262478
first, could you tell us what you are trying to achieve.

you can also set the recordsource of a report on the open event

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "qryInvoices"
End Sub


0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 12262534
calpurnia's got it.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12262580
not quite,

youhave to change strReportName to strRecordSource
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 12262642
Oops, that'll teach me to go renaming things on the fly!
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 12262679
I stand corrected, I'll learn how to read someday! LOL
0
 
LVL 1

Expert Comment

by:BASESLydia
ID: 12288766
The error you are getting comes from the line "Set myRpt = Reports!rptRoomStatistics", not the record source stuff. Because rptRoomStatistics is not open, it is not in the Reports collection so you cannot reference it. In addition once it is open, you cannot change the recordsource. This makes calpurnia's solution the best.
0
 

Author Comment

by:FreeLancer
ID: 12291029
Sorry about the delay in responding, it was the holiday weekend up here in the "Great White North", well not white yet at least, but ask me again in a month!

Ok so let me get this straight then, I can't change the "Record Source" property of a report unless it is open, and in that case I have to do it during the "Open Form" event. Fair enough, that's what I figured I'd have to do anyways, I just needed to be sure.

As to what exactly I was trying to do is this. The program in question is a "Client Registration" database tracking when our clients come in. It also tracks what they were here for, who they saw, and if they were met in an interview room. The report in question was to track when the interview rooms are being used.

Now when I set up the database I separated the registration data into two separate tables, one contains the information for the day, and the other contains all the archived information.

Now when it came to doing these "Room Statistics" reports I sometimes need to do it for one table or the other, therefore I wanted a quick way to change between the two queries. One for the "Archived Table" information, and one for they "Day's Table" information. I know how to adjust the queries record source in VB, and figured the reports would be similar, I just didn’t count on the them needing to be open to do it.

Thanks to calpurnia for spawning a new idea for me to this with, but before I give the points, which I’m going to up in lieu of this added question. Is the method your describing a way of passing arguments to a report? If so then my problems solved, I just do the changes in the “Open” event, instead of through the form that loads the report. I’m outta work for today, so I’ll check on this tomorrow morning.
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 12292950
Yes - more specifically, a way of passing *an* argument to a report.

The final parameter for DoCmd.OpenReport is OpenArgs, the value of which is then available from within the open report by using Me.OpenArgs.



0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:FreeLancer
ID: 12299260
Awesome, thanks calpurnia, you've been a great help heres the points, and thanks to all who responded also.
0
 

Author Comment

by:FreeLancer
ID: 12339232
I'm not sure if anyone is still getting email notifications about this, but if calpurnia is listening, the OpenArgs only works for forms, not for Reports. It is part of the docmd, but everytime I've tried to run the code I get a compile error.
0
 
LVL 1

Expert Comment

by:BASESLydia
ID: 12339268
Can you paste in your code that wont compile?
0
 

Author Comment

by:FreeLancer
ID: 12339322
This is the code that should work to pass arguments to the report, but after the code wouldn't compile, I did some digging around in the Access help files, and according to them you can pass arguments to forms, but not reports.

Either way this is the code that I have to open the report and pass the argument:

DoCmd.OpenReport "rptRoomStatistics", acViewPreview, , , , RoomArgs

Everytime I try and compile I get and error, I even tried running it, but I get a compile error there too.
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 12339851
What about the surounding code? Should be:

Dim RoomArgs As String
RoomArgs = "qryArchivedRoomStatistics"  ' or whatever youwant to set this to
DoCmd.OpenReport "rptRoomStatistics", acViewPreview, , , , RoomArgs

0
 

Author Comment

by:FreeLancer
ID: 12340285
The code is nestled in a Select Statement. I use those as an easy way of differentiating between the different report types. With the data we have we can ask for 8 different types of reports, from registration lists to hours stats, to reason stats. The “Room Statistics” report is the last type of report created therefore it is Case 8.

Also when I’m doing the reports I do them in two separate parts, one where I gather the data into a report table/or prep the query, the other section actually calls on the selected report.

So to get the room stat’s report the first section preps the query.
(As a note all of the code compiles correctly, even if it looks incorrect here, it was only because I copied and pasted and all the tabbing and everything is out of whack)


        Case 8: 'Room Statistics
       
            Dim myQueryDef As QueryDef
       
            If txtEndDate < Date Then 'This does the report in the Archived table
               
                Set myQueryDef = dbs.QueryDefs("qryArchivedRoomStats")
               
                sSQL = "TRANSFORM Count(ArchivedDataTable.Name) AS CountOfName  
                               SELECT ArchivedDataTable.Room_Number "
                sSQL = sSQL & "FROM ArchivedDataTable WHERE Room_Number Is Not  
                              Null AND Reg_Date BETWEEN #"
                sSQL = sSQL & txtStartDate & "# AND #" & txtEndDate & "# GROUP BY
                              ArchivedDataTable.Room_Number "
                sSQL = sSQL & "PIVOT Format([Checkin_Time],'hh')"
               
                myQueryDef.SQL = sSQL
               
                RoomArgs = "qryArchivedRoomStats"
                ShowReport
               
            ElseIf txtEndDate = Date And txtStartDate = txtEndDate Then 'This does the
                                                                                       report in the Day Table
               
                Set myQueryDef = dbs.QueryDefs("qryDayTableRoomStats")
               
                sSQL = "TRANSFORM Count(daytable.Name) AS CountOfName SELECT
                            daytable.Room_Number "
                sSQL = sSQL & "FROM daytable WHERE Room_Number Is Not Null "
                sSQL = sSQL & "GROUP BY daytable.Room_Number "
                sSQL = sSQL & "PIVOT Format([Checkin_Time],'hh')"
               
                myQueryDef.SQL = sSQL

                RoomArgs = "qryDayTableRoomStats"
                ShowReport
           
End if

Now the program calls the show report function it is within this function that the reports are called, and any preps for those completed. RoomArgs is a form variable so it is available within any procedures called on that form.
             
        Case 8: 'Room Statistics
       
            DoCmd.OpenReport "rptRoomStatistics", acViewPreview, , , , RoomArgs      

End Select
0
 

Author Comment

by:FreeLancer
ID: 12340292
Oops sorry hit submit before I was finished, as I said it is only with the Docmd.OpenReport that I get errors, everything else works fine. If I comment out the Docmd line, the program executes fine.
0
 

Author Comment

by:FreeLancer
ID: 12340335
Also I checked the reports code and even OpenArgs is not one of the commands listed, when you enter Me. and scroll through the list. Also I entered it anyways, and tried compiling the program and I get this error:

Compile Error

Method or data member not found.
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 12342175
Hmm, which version of Access are you using?
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 12342338
Earlier versions of Access don't support OpenArgs on Reports. However, if RoomArgs is a variable on a form, and that form remains open when you call the report, you can forget all the OpenArgs stuff and in the Open event of your report use:

Me.RecordSource = Forms!YourForm!RoomArgs

(replacing YourForm with the name of your form!)

0
 

Author Comment

by:FreeLancer
ID: 12346846
I'm using Access 2000, and RoomArgs was the name I gave to the variable that was to pass the arguments to the room statictics report. It'll be just my luck too that they included that in Access 2002 and higher, right? I'm sorry I should have mentioned earlier what version of Access I was using.

Oh ok I see what you mean, similar to what I had been doing to change the To & From Dates on my reports. That'll work perfectly too.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now