Pass args from form to report and use value in heading

[lorirobn]

Hi,

I pass an argument from a form to a report using OpenArgs.
In my report, I would like to display the OpenArgs (a room number) on the report heading line. I set up a text box on the report's heading line for this.

Can I use OpenArgs directly in the text box's record source property? If not, do I need to create a string to move the OpenArgs into, and then assign that value to the text box? I tried various iterations, but none work. I also tried setting the text box to the openargs in VBA, with "txtRoom = OpenArgs", but I get an error that I can't assign a value to that property (it's in my report's open event).

Any suggestions?
Thanks.

 

[stix4t2]

You can use the forms!yourfrmname!control in the report recordsource and a control source on the report. You may want to use the expression builder to assist in your efforts, the button with "..." on it.

 

[lorirobn]

Hi Stix,

My report recordsource is a query I set up. Not sure which control you meant to use in report record source?

As far as the control source on the report - did you mean to use forms!yourfrmname!control in a control source on the report? Not sure what that means. Sorry...perhaps my brain is a bit too foggy today. Too much programming and details details details...

Thanks for the response,
Lori

 

[lorirobn]

ps - my current code, in my Report_Open event, is:

Dim strRoom As String
strRoom = Me.OpenArgs
txtRoomID = strRoom

(txtRoomID is the text box I set up on my report to display the open arg value).
I get error "You cannot assign a value to this object".

 

[stix4t2]

Hey Lori,

I was saying that the form that is launching the report has fields. You can pull off the form instead of passing parameters to the report. I gave a sample to reference the form's controls to get the values.

Example,
On the form, frmReport, you have to fields startDate and endDate. In the reports query you could add criteria
between forms!frmReport.startDate and forms!frmReport.endDate. On the report you could add a textbox
= "Between " & forms!frmReport.startDate & " and " & forms!frmReport.endDate
You would not need to add any code to the report.

 

[RoyVidar]

If you are really in the on open event of the report, I'm wondering if your text control is bound. If it has a controlsource, then you cannot assign like that - try removing whatever is in the text controls controlsource, and try again.

Roy-Vidar

 

[lorirobn]

Stix: that is great. I didn't know I could pull off the form instead of passing parameters. I changed it, and it works great!

I am still curious as to why my assigning openargs to a control didn't work. PHV, it was definitely the Report_open event, and the control was unbound (it even says 'unbound' in the text box in design mode).

Anyway - glad it's working, and would still be happy to hear suggestions on assigning a value to the text box.

Thanks for all the help - don't know how I'd get by without all of you.

Lori

 

[RoyVidar]

Ah - it is me who is stupid, I don't think you can assign any value in the on open event - but need the on format event where the control resides.

Roy-Vidar

 

[stix4t2]

Lori,
I can only think of two things. With forms, the open event has not loaded up the controls, so setting a value is done in a form's load event. I would think that reports would have to do it in the form header because it doesn't have a load event. Or you had to specify the value property for the text box.

Also, sometimes if you are using an unbound form to enter values for the report, you could create a table to hold those values and tie it to the form. Like the date example above, creating a table with startdate and enddate as fields, the value are persistent from one session to another. Then you could add buttons to change the dates by week or month to do the same report for the next month.