Passing Parameter to Report

Topic

New Reply

How do I pass a string parameter from a Form (frmStartup)to a Report. In frmStartup I define the string as
strDateRange = “For the Period ” & StartDate & ” To ” & StopDate
A command button then initiates the report, i.e.
DoCmd.OpenReport stDocName, acViewNormal
I want to put strDateRange in the report header; however, the DoCmd.OpenReport doesn’t have any place to put an argument to pass to the Report module.

Reply | Quote

Replies

Chuck:
Although DoCmd.OpenReport doesn’t have a means of passing the value, the Report can Get the value from the form as long as the form is still open. In the Report Header you can reference the form: i.e. =Forms![frmStartup]![strDateRange]

Reply | Quote

Hi Brian,

That’s exactly what I tried, and my report displays #Error in the text box with =Forms![frmStartup]![strDateRange] in it. I’m really puzzled with this one

In the Sub that calls the report I have Dim strDateRange As String. I then have the expression strDateRange = “For The Period ” & Starttxt & ” To ” & Stoptxt. Starttxt and Stoptxt are the names of text boxes that have the dates.

What am I missing? BTW I’m running Access97.

Reply | Quote

I don’t understand why you are using a sub with the expression =Forms![frmStartup]![strDateRange], when it seems that the textboxes on the form are named Starttxt and Stoptxt. Have you tried putting a textbox on the report with the source as =”For The Period ” & Forms![frmStartup]![Starttxt] & ” To ” & Forms![frmStartup]![Stoptxt]?

Reply | Quote

OK,
Confessions of a newbie to the world of VBA. Now you’re forcing me to get down to the real crux of the problem. The string =”For the Period ” & [Forms]![frmStartup]![Starttxt] & ” To ” & [Forms]![frmStartup]![Stoptxt] works just fine EXCEPT, the reports come out with an extra blank page. Of course, this is usually an indication that the report width exceeds the distance between the left and right margins. Soooo, I checked and sure enough, it was too wide. I tried to drag the right edge of the report back to the left, but it won’t budge. Guess what? In the design view the above string runs right off the right edge of the report even though when printed it fits on the page. I then tried to manually set the report width property to the max available space. However, looking back after supposedly saving that entry I find that Access took upon itself to revise it back where it was!!!

Of course, I could print in a smaller font — small enough so that the string in design view would fit on the page, but having to accommodate an obvious flaw in Access really bugs me.

So, my thought was to set up the string as a variable in the form with the calling procedure and use that variable in the report text box because it would be shorter in the report design review. It seems like such a simple concept. Can’t a called report acquire variables defined in the calling form? There MUST be a way.

Reply | Quote

Maybe I am misunderstanding you, but the width of the textbox in the design view does not need to accomodate the entire length of the source statement, only the width required for the data to print on the report. So, try decreasing the width of the textbox in the design view, so that it prints on the report properly, but doesn’t exceed the page width.
Then, of course, there is the obvious solution of moving it somewhere else on the page. If the page is 8.5″x 11″ wide – can’t it fit somewhere else?

Reply | Quote

Thanks Thomas,

That did it, and the reports all come out looking great. I don’t know why I didn’t think of it.

However, I remain curious as to how one passes a variable from one object to another. I’m just a beginner regarding Visual Basic and right now I’m slugging my way through VB 6.0 for Dummies which has been helpful but doesn’t cover everything. I’ll figure it out someday.

Thanks again for your patience with a newbie.

Reply | Quote