Field selected by Combo Box

[H0MELY]

Greetings, thank you for looking. I am attempting to display the grouped
contents of a field ...a field that the user will select from a combo box.

There is a table that contains atm data...lots of atm data. The users have
no idea what kinds of data is contained within the fields. What I would like
to do is have a form where all of the fields are listed in a combo box
(allready did that) and when the user selects a field and click on a command
button a query would run that would show the grouped field entries for their
selected field.

Ex...lets say you want to know all of the entries in the manufacturer field,
if you select manufacturer from the combo box it would run a simple query
where the field in the query is manufacturer.

What I have tried is listed below...unfortunately what happens is that when
it returns the result...it returns 1 result...the field name from the combo
box. Everything else I am trying causes syntax errors. Any assistance would
be much appreciated. Thanks again for looking.

SELECT Forms!frm_data_fields!Combo_all_atm AS [Field Results]
FROM tbl_v_aim_all_atms
GROUP BY Forms!frm_data_fields!Combo_all_atm;

 

[Ken Sheridan]

First add the following procedure to a standard module in the database:

Public Sub OpenTempQuery(strSQL As String)

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer

Set dbs = CurrentDb

n = n + 1
strQdf = "qdfTemp" & n

' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)

' evaluate QueryDef object's parameters
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

' open temporary query
DoCmd.OpenQuery strQdf

' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name

End Sub

Then in the button's Click event procedure build the SQL statement and pass
it into the procedure as the strSQL argument. As you are not aggregating any
data you can use the DISINCT option rather than a GROUP BY clause:

Dim strSQL as String
Dim strColumn As String

strColumn = Me.Combo_all_atm

strSQL = "SELECT DISTINCT " & strColumn & _
" FROM tbl_v_aim_all_atms & _
" ORDER BY " & strColumn

OpenTempQuery strSQL

Ken Sheridan
Stafford, England