By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,439 Members | 1,657 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,439 IT Pros & Developers. It's quick & easy.

Passing a value to a report via VBA

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. strCriteria = "PricingDate = Date()"
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. strReportDate = InputBox("Please enter the desired report date", "Report Date", Date-1)
  2. strCriteria = "PricingDate = #" & strReportDate & "#"
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. Dim strHeader as String
  2. strHeader = "2008 Pricings for " & datReportDate
  3. 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.
Aug 21 '08 #1
Share this Question
Share on Google+
4 Replies

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

Expand|Select|Wrap|Line Numbers
  1. Dim strHeader as String
  2. strHeader = "2008 Pricings for " & datReportDate
  3. DoCmd.OpenReport "DailyPricings", acViewDesign
  4. With Reports!DailyPricings
  5. .lblHeader.Caption = strHeader
  6. End With
  7. DoCmd.Save acReport, "DailyPricings"
  8. DoCmd.OpenReport "DailyPricings", acViewPreview, , strCriteria
Aug 21 '08 #2

Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "Reportname", acPreview,,,,me.yourcontrolvalue
in the report's On Activate property
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Activate()
  2. If Not IsNull(me.openargs) then
  3.   Me.YourHeaderCtrl = Me.OpenArgs
  4. end if
  5. 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.

Aug 21 '08 #3

P: 15
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!
Aug 21 '08 #4

P: 1
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.
Sep 21 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.