By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

Using combo box values to select query fields

P: n/a
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?

Nov 15 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.RowSourceType = "Table/Query"
Me!cbx2.RowSource = 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" <ni*********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
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?

Nov 15 '05 #2

P: n/a
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.

Nov 16 '05 #3

P: n/a
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 & "];"

Nov 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.