Feed a combo box with the results of a query

[Alejandro]

Hello everyone,

I have a form that among other things has two combo boxes. The first one is
fed with a simple table with two entries. I would like the second one to
display the result of a query run in the background. The idea is that based
on what the user selects in the first combo box she would get a specific set
of options in the second one.

I don't know if this is the right way to do it, but if anything else at
least the following code will give you an idea of what I'm trying to do:

Private Sub cmb_FirstComboBox_AfterUpdate()

Dim SQL As String

If Me.cmb_FirstComboBox = "Front Desk" Then

SQL = "SELECT DISTINCT [FD REP] FROM tbl_MasterTable" & _
"ORDER BY tbl_MasterTable.[fd rep]"

ElseIf Me.cmb_FirstComboBox = "Back Office" Then
SQL = "SELECT DISTINCT [adm rep] FROM tbl_MasterTable" & _
"ORDER BY tbl_MasterTable.[adm rep]"

DoCmd.RunSQL SQL

End If

(and this point I would have something that links the cmb_SecondComboBox
with the results of the SQL query)

Could anyone give me a hand with this, please?

Thanks,

A.

 

[Jeanette Cunningham]

You're almost right.
After you form the sql, set the second combo's row source to the sql.


Private Sub cmb_FirstComboBox_AfterUpdate()

Dim SQL As String

If Me.cmb_FirstComboBox = "Front Desk" Then

SQL = "SELECT DISTINCT [FD REP] FROM tbl_MasterTable" & _
"ORDER BY tbl_MasterTable.[fd rep]"

ElseIf Me.cmb_FirstComboBox = "Back Office" Then
SQL = "SELECT DISTINCT [adm rep] FROM tbl_MasterTable" & _
"ORDER BY tbl_MasterTable.[adm rep]"

me.[NameOfCombo].RowSource = SQL

End If

Note: replace [NameOfCombo] with the name of the second combo


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia