?
Solved

Changing Report Properties in Visual Basic

Posted on 2004-10-08
21
Medium Priority
?
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 11

Expert Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

771 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