Create report based on dynamic query from a select field form

[Lucrecious]

How do I create a dynamic report based on a query that updates from a select field form? I followed the information from the article here entitled: "Checkbox form to allow user to select query output fields". I wish to have the Switchboard run the report after I have used the select field form to generate the results. Is this possible, and if not, is there a way to create a report template which can be applied to the query results of the field select form?

[limweizhong]

Use a reference to the control in the form in the query itself, e.g. in the criteria row, put something like forms.form1.text0 where form1 and text0 are the form name and field name respectively.

[Lucrecious]

Thank you for your suggestion.
In terms of a dynamic report generated based on a custom layout using fields from the query. How is this accomplished with your suggestion? Please elaborate...

[limweizhong]

Assume you have the original query in Query1. You don't put that in the RecordSource of the Report. You first create another query, say Query2, and inside that query, you do the appropriate filters with the reference to the control in the form, say Forms.Form1.Text1. When you have the form open, and run the query, you can see if it works. Then you set the RecordSource of the Report to Query2. Or you could copy the SQL of Query2 into the RecordSource.

[Lucrecious]

I followed your instructions, however, the report remains blank.

The second query does match the first and I have set the recordsource of the report to the sql from the second query namely:

SELECT CRF_Fields1.*
FROM CRF_Fields1;

The report remains blank.

The second query does retain the selected fields from the first.

Do you have any suggestions?

[Lucrecious]

Okay, this is not what I need and now see what you were mentioning.

I took a current report with custom graphics, layout and fonts.

Removed all the selected fields and set the recordsource to the query expecting the report to populate with all the fields using the wildcard "*".

Excuse my ignorance, however, is there a way that a report can be created to dynamically update the fields from a variable query without having to edit the report each time?

In other words, while everything else works, I need the report to be generated from the varying results of the query which is updated from the field selector form which I gratefully found on this website recently?

I trust what I have described is clear enough...

Please advise me...

[limweizhong]

That might be do-able with a macro, but it all depends on how you want the report to be laid out. If you have a fixed *number* of columns, then you might just put the text-boxes all laid out nicely, then dynamically change the ControlSource to the right field name using a macro that runs when the report loads. Otherwise, you must have a macro somewhere else, that dynamically changes the report *design*, and then opens the report with the new design.

However, all these really requires knowledge of VBA programming.

[Lucrecious]

Alternately, would it be possible to create a template look and feel so each Report generated is based on the same graphics and layout?

[limweizhong]

If you are dynamically generating reports, there might be a way to use the in-built Access commands to make the form (form wizard but with parameters specified automatically), and apply themes to it (in Access 2007). But you will need to refer to the MSDN documentation. I don't know enough to help you there, sorry.