How to change the record source of a report from a form

[MattCollins]

I'm looking to change the record source of a report and then print the report depending on what button the user clicks on.

Is there some way to do this?

 

[RuralGuy]

You can pass almost any amount of information in the OpenArgs which you can test for in the Open event of the report and change almost anything about the report, including the record source.

 

[Pat Hartman]

The best place to actually change the recordsource of a report is in the report's Open event. Only newer versions of Access support the OpenArgs property for reports. If yours does not, you will need to reference the calling form directly.

Me.RecordSource = Forms!someform!sqlstring

 

[MattCollins]

Thanks for the replies!! I'm using Access 2003 but using a 2000 database.

Basically any code has to go into the open event of the actual report?

If i already have a criteria in the query that the report is based on will this effect this? Or is it best to start with a blank query and then use the open event to decide what goes into the criteria?

 

[RuralGuy]

You could use a generic query for the report and narrow it down with the WhereCondidion of the OpenReport which is just the WHERE clause without the key word WHERE.

If you could accomplish everything you need with different WHERE clauses then no code would be needed in the report.

 

[MattCollins]

Thanks RuralGuy! I've decided to use a generic query and then use the WHERE method. Works like a dream!! Thanks again

 

[RuralGuy]

Glad I could help.

 

[DruTheFu]

How would this work if I were to build the Query in the form (using Access 2000)? I have some routines that build the query off of the data from multiple multi-select listboxes, concatenates them into a single query statement.

How would I pass this query to be used as the Reports RecordSource?

Thanks.

 

[RuralGuy]

DruTheFu,
I could think of a couple of ways. One would be to put the SQL string in a public variable in the form and reach back into the form from the report for the RecordSource:

Me.RecordSource = Forms!YourFormName.TheSQLstringVariable