creating a query in code and calling a report

[MandoThrasher]

Can I create a SQL string on the fly in code and use the SQL to drive a report?

I have a form with some fields and checkboxes. Based on the data, I build the SQL in a function. Now I need to figure out how to take my variable(SQL) and assign it to a query that is the datasource for a report.

help!

 

[ajdesalvo]

Try this:

Docmd.OpenReport "YourReport"
Reports![YourReport].RecordSource = SQLVariable

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[MandoThrasher]

When I do this:
Docmd.OpenReport "YourReport"
Reports![YourReport].RecordSource = SQLVariable

I'm getting an error that says you can't set the record source propoerty after the report has started printing or in preview mode.

So I tried this:
Reports![YourReport].RecordSource = SQLVariable
Docmd.OpenReport "YourReport"

And I get an error that the report doesn't exist. I guess because it's not open.

Can I go into design mode on the report and set the record source equal to a variable name on my form?

 

[ajdesalvo]

Sorry, that is my mistake, clicking before thinking again!

Make the SQLVariable a Public variable.

Public SQLVariable as String

Save the report with no recordsource. Then on the OnLoad event of the report:

Me.RecordSource = SQLVariable

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[MandoThrasher]

That did it!

Thanks a lot!