Dynamic Column selection

Question

How can I create a dynamic query to able the user to select the columns he wants to appear.  ie. I have a select query with 40 columns but a user will want to create a query with just 5 or 10 or 20 as they require.  Then we will export to Excel.  I would like some sort of list box where the user can pick the columns they want to appear and export to an excel spreadsheet from the results.

Answer 1

You might want to consider the Query By Form applet at

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH%20Query%20By%20Form'

***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated into any existing Access application. Typically, the functionality provided by DH QBF can replace many "canned" reports. The developer imports several forms, tables, a query, and a report from the DH_QBF.mdb, creates some master queries, and deploys.

The developer creates one or more master queries that join tables, alias field names, create calculated columns, etc. The users can then select a master query (data source) from a drop-down and then select up to 30 fields from the master query. Users can define sorting and criteria as well as grouping and totaling. All of this "design" information is stored in two tables for re-use.

The results of the queries are displayed in a datasheet subform contained in a main form. The main form has options to send/export the records to print, Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats allow the user to automatically open the target application. The Word merge process will open a new Word document and link to the merge fields.

--

Duane Hookom

MS Access MVP

Answer 2

You might want to consider the Query By Form applet at

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH%20Query%20By%20Form'

***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated into any existing Access application. Typically, the functionality provided by DH QBF can replace many "canned" reports. The developer imports several forms, tables, a query, and a report from the DH_QBF.mdb, creates some master queries, and deploys.

The developer creates one or more master queries that join tables, alias field names, create calculated columns, etc. The users can then select a master query (data source) from a drop-down and then select up to 30 fields from the master query. Users can define sorting and criteria as well as grouping and totaling. All of this "design" information is stored in two tables for re-use.

The results of the queries are displayed in a datasheet subform contained in a main form. The main form has options to send/export the records to print, Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats allow the user to automatically open the target application. The Word merge process will open a new Word document and link to the merge fields.

--

Duane Hookom

MS Access MVP

This worked perfectly.  Exactly what I needed.

Answer 3

You could use a listbox with the Row Source Type set to FIELD LIST and then you supply a table or query name in the ROW SOURCE and it will list the fields.  Change the MULTI-SELECT property of the list box from NONE to SIMPLE or EXTENDED and then you can use code to iterate through and take the selections.  Also, create a base query with the name of qryBase to make it easier.

Dim qdf As DAO.QueryDef

Dim strSQL As String

Dim strFields As String

Dim varItm As String

For Each varItm In Me.ListBoxNameHere.ItemsSelected

strFields = strFields & "[" & Me.ListBoxNameHere.ItemData(varItm) & "], "

Next

If Right(strFields, 2) = ", " Then

strFields = Left(strFields, Len(strFields) - 2)

End If

strSQL = "SELECT " & strFields & " FROM TABLENAMEHERE"

Set qdf = CurrentDb.QueryDefs("qryBase")

qdf.SQL = strSQL

qdf.Close

DoCmd.OpenQuery "qryBase"

Set qdf = Nothing