Changing a report's RecordSource externally using VBA

[johns88]

I have developed a generic report that I wish to run using different queries as the RecordSource.
I need to be able to pass parameters from an OnClick event from a form to a report to print or generate a preview based on the parameters passed. I plan to hard code the query name in the form button's OnClick event. One OnClick event may generate multiple reports.
One of these parameters is the query name. Another would be the contents of a TextBox (ie to change the title of the report to match the query results.
I've tried a few things but they don't work.
I would appreciate any suggestions on the best ways to make this work.
Thanks in advance.
John

 

[KenReay]

Hi

If the form is open at the time teh reprt is called, you can address the textbox (or whatever on the form, using syntax like Forms!MyFormName!MyTextBoxName.

You can resest the recordsouce of the report in the on open event of the report so:

Me.Recordsource = "SELECT ...."

Does that help? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[johns88]

Unfortunately that doesn't work for this aplication.

The form is a simple switchboard form with buttons and no text boxes.

The queryname(s) are hardcode in the OnClick event of the button so there are no opportunities to use Forms!MyFormName!MyTextBoxName.

I need a method to pass the queryname from the form VBA to the report VBA.

 

[KenReay]

Hi

The fact that the form is a simple switchboard does not preclude the use of the textbox method described, just hide the textbox (.visible property = False) so the user does not see it.

If I have missed the point, or for some reason you do not want to do that, you are left with:

Global Variables

OR

Use a table, either local to the front end database, or with a user Id so taht multiple users do not corrupt each others passed parameters

Hope this helps
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulBricker]

I don't profess to completely understand what you are trying to do but you can add data to the query that might help. For example you can add a field to the query that says

QueryName:"PutQueryNameHere"

so that the name of the query actually appears as a field in the query. Then you should be able to pass that value along to the Report.

Paul