Using combo box values to select query fields

[Nick Douglas]

I have a table with 50 fields, and a combo box which is set to 'field
list' to read the field names. I want to select report groups via
another combo box based on the field names the user selects. For
instance, if they select 'State' in the first box, I want the second to
populate with 'New York' and 'New Jersey' and so on. I tried this as
the source of the second box :

SELECT Me.cbx FROM table GROUP BY Me.cbx ORDER BY Me.cbx;

but it did not work. Any ideas?

[Douglas J. Steele]

Re: Using combo box values to select query fields

You need to dynamically build the SQL string in the AfterUpdate event of the
first combo.

Assuming your second combobox is named cbx2, you'd have something like:

Private Sub cbx_AfterUpdate ()
Dim strSQL As String

strSQL = "SELECT DISTINCT [" & Me.cbx & "] " & _
"FROM table ORDER BY [" & Me.cbx & "]"
Me!cbx2.RowSour ceType = "Table/Query"
Me!cbx2.RowSour ce = strSQL
End Sub

(There's no reason to have the GROUP BY in there)

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(no e-mails, please!)



"Nick Douglas" <nickdouglas@gm ail.com> wrote in message
news:1132093174 .826699.33190@z 14g2000cwz.goog legroups.com...[color=blue]
>I have a table with 50 fields, and a combo box which is set to 'field
> list' to read the field names. I want to select report groups via
> another combo box based on the field names the user selects. For
> instance, if they select 'State' in the first box, I want the second to
> populate with 'New York' and 'New Jersey' and so on. I tried this as
> the source of the second box :
>
> SELECT Me.cbx FROM table GROUP BY Me.cbx ORDER BY Me.cbx;
>
> but it did not work. Any ideas?
>[/color]

[Nick Douglas]

Re: Using combo box values to select query fields

Thanks Doug

Is there any reason I can't add a WHERE statement in there as well,
like:

strSQL2 = "SELECT DISTINCT [" & Me.cbxUserL2 & "] " & _
"FROM Table WHERE [" & Me.cbxUserL1 & "] like " & _
"[" & Me.cbxGroup1 & "] ORDER BY [" & Me.cbxUserL2 & "]"

It's asking for a parameter value when I run it this way.

[Nick Douglas]

Re: Using combo box values to select query fields

Got it, had to set the where to the full combo box reference:

strSQL2 = "SELECT DISTINCT [" & Me.cbxUserL2 & "] " & _
"FROM Table WHERE [" & Me.cbxUserL1 & "] = " & _
"[forms]![frmName]![cbxGroup1] ORDER BY [" & Me.cbxUserL2 & "];"