Change query field in form

[octsim]

Hello,



is it possible to change a query's field through a form?
I have 2 list controls with the names all the fields in my table - I want the user to select 2 fields and perform a query that shows all the data of only those 2 fields. I know how to use a listbox in the criteria but I want the fieldname to be selected, not the search criteria - or am thinking wrong?

Thanks in advance!

octsim

[ItsMe]

Why do you have two listboxes? Are they dependent upon each other? If you want a dynamic SELECT statement, this is possible. Is it a form that is dependent on the query?

[octsim]

I have 2 listboxes which display the same values - all field names in my table. I want the user to be able to select 2 fields from my table through a form and perform a select query, I just can't find how to use the fields as a parameter.

[June7]

AFAIK, a saved Access query object cannot be set up to change the fields that are selected (I have tried).

VBA can modify a saved Access query object using QueryDefs collection or delete query and resave it with different structure (I do this). Examples:

Private Sub btnRun_Click()
If IsNull(Me.tbxFilter) Then
    Me.tbxFilter = "SELECT * FROM ProjectRatesMainSub WHERE grading Like '*' " & GetMisc() & " ORDER BY projects.proj_num, grading;"
End If
Dim qdfUser As DAO.QueryDef
CurrentDb.QueryDefs.Delete ("UserQuery")
Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
DoCmd.OpenQuery "UserQuery"
End Sub

Private Sub btnExcel_Click()
Dim qdfUser As DAO.QueryDef
CurrentDb.QueryDefs.Delete ("UserQuery")
Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
DoCmd.OpenQuery "UserQuery", , acReadOnly
DoCmd.RunCommand acCmdExportExcel
End Sub

Or maybe you don't want to change the fields that are selected in the query, just change the filter criteria. I use VBA to construct WHERE clause and then apply it to the Filter property of form or pass it to a form/report with WHERE CONDITION argument of DoCmd.OpenForm (or OpenReport). Review http://allenbrowne.com/ser-62.html

[ItsMe]

In the end, you may find you only need one list box. It is a little more code to get selections from a multiselection list box verses a listbox that only allows one selection or a combobox.

If you want your form's recordsource to be dependant on the listbox controls' values, you will need to adjust the form's filter or its recordsource. I prefer to adjust the recordsource. Adjusting the form's filter can be risky. The user will most likely have the ability to modify, turn on, and turn off the filter via toolbars, etc.

The very first step is to translate the listbox values into a WHERE clause. In order to do this you need to identify a socalled Child Link and Main Link. YOu need to identify the field that will act as a PK(Main) in the listbox and the field that will act as the FK(Child) in the form. Don't take the terms used here too literaly. You need to identify which column the field resides in within the listbox.

Your where clause may begin to look something like this....

dim lngID as Long
dim strWhere as string

lngID = Me.lstControl1.Value
strWhere = " WHERE [ID] = " & lngID