Generate query based on fields checked in a form

[jdashm]

Sorry is this is redundant. I posted it under queries but did not get a response so I am trying to post this under programming...

I want to create a query where the fields displayed are selected (check boxes?) on a form.
For example, a users chooses the First Name, Last Name, and email fields on a form and clicking a button generates a query with those fields only from their underlying tables.




Any help would be appreciated.
Thanks.

[pbaldy]

I saw it yesterday but was on a mobile device. It would have to be done with VBA code. If the end result is a query, you'd need a DAO QueryDef to change the SQL of a query (or create a new one). Your code would examine each check box and add its field to the SQL if checked. A multiselect listbox would be an alternative to checkboxes, and be more dynamic.

[jdashm]

Thanks for the response. I like the idea of the multi-select list box. However, i have no idea what the code would look like to accomplish tying the fields to an underlying query. Can you help?

[pbaldy]

You'd be building the SQL for the query in code, not "tying fields to an underlying query". Start a string like

strSQL = "SELECT "

add the chosen fields to that, then

strSQL = strSQL & " FROM TableName"

here's how you'd step through the selected items of the listbox, though it will require adapting to your usage:

http://www.baldyweb.com/multiselect.htm

[jdashm]

Thanks for this. I will try this out.

[pbaldy]

No problem, post back if you get stuck.

[alansidman]

Attached is a DB that I built quite a few years ago (originally an .mdb file). I have deleted all data from the file. If you go to the reports form (Search Form) and look at the logic behind the Multi-Selector Query/report you will see one way of accomplishing this task. It is VBA driven and uses the technique described by Paul above. Kudos to June7 who helped me figure this out --probably 10-12 years ago.

Alan