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.

and if you replace : DoCmd.RunSQL SQL
by :
Me.cmb_SecondComboBox .Rowsource = SQL



"Alejandro" wrote:

> 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.