Passing a value to a report via VBA

[RachH]

Is there a way to pass a value to a report via VBA so the value can then be displayed in the report as a concantenation with a string?

I have a report that displays all pricings that have occured in one day. I would like to display the pricing date in the report header. However, if there were no pricings for the date being viewed, I return no records so I can't pull the pricing date from my query results and thus want to set it programatically .

The form to display the pricing reports has two buttons. One for 'display today's pricings' and another for 'display pricings for x date'. Both buttons open the same report and the OpenReport command includes Where criteria to show only the desired date's data. With the exception of the db admin, all users will only ever look at pricings for the current day so the 'display today's pricings' button has:

strCriteria = "PricingDate = Date()"
DoCmd.OpenReport "DailyPricings", acViewPreview, , strCriteria

The db admin will sometimes need to view past pricing reports so the 'display pricings for x date' button has:

strReportDate = InputBox("Please enter the desired report date", "Report Date", Date-1)
strCriteria = "PricingDate = #" & strReportDate & "#"
DoCmd.OpenReport "DailyPricings", acViewPreview, , strCriteria

I could have all users use the inputbox and set the inputbox default to Date then move all the code to the Report_Open event, but that means the majority of users will always have to click an extra button in the process of viewing the day's report.

In the Report_Open event, I want to do something like:

Dim strHeader as String
strHeader = "2008 Pricings for " & datReportDate
Me.lblHeader.Caption = strHeader

So is there a way I can pass the value for datReportDate to the Report_Open event as either the current day's date if user clicked 'view today's date' or as the user-entered date if user clicked 'view pricings for x date'? I apologize for the long post. The answer seems like it should be simple but, at this point, I'm so backwards in cleaning up this database I inherited that I can't see straight.

[jpatchak]

You should set the caption property of the label while form is in design mode, then open in preview. For example:

Dim strHeader as String
strHeader = "2008 Pricings for " & datReportDate
DoCmd.OpenReport "DailyPricings", acViewDesign
With Reports!DailyPricings
.lblHeader.Caption = strHeader
End With
DoCmd.Save acReport, "DailyPricings"
DoCmd.OpenReport "DailyPricings", acViewPreview, , strCriteria
 

[Stewart Ross]

Hi. You can also use the OpenArgs property to pass a string value to the report which can be retrieved using event code in the On Activate event of the form:

in the form code that opens the report:

DoCmd.OpenReport "Reportname", acPreview,,,,me.yourcontrolvalue

in the report's On Activate property

Private Sub Report_Activate()
If Not IsNull(me.openargs) then
  Me.YourHeaderCtrl = Me.OpenArgs
end if
End Sub

You could also use the On Open event, but unfortunately you would not be able to set a value for a report control that way as the control's value is not available until after the report is opened.

If you look up the Help for OpenArgs you will see examples of record matching.

-Stewart

[RachH]

Thank you both so much for your help! I was able to implement jpatchak's suggestion (although I did need to close the form between saving and opening in Preview view in order for the strCriteria to take effect).

I will now work my way through the openargs suggestion as I need all the opportunities for learning Access I can get. Thank you again!

[esilva002]

Hey I have Done exactly what you guys have done using the With statement in Post #2. When I do that I get an error saying: This Property is not available in design view. So I changed the view to Preview and I get the error saying: You can't reference a property or method for a control unless the control has the focus.

help please.