Combo Box Help 2

[ScotteMoegling]

I was wondering about the possibility of 'combo boxes' working together. So if one choice is selected in Combo Box "X" it could automatically select a choice in Combo Box "Y" for the user.

If so, can I please get direction how to do this?

Thanks for the Help

 

[Lightning]

Yes, this is a relatively simple procedure, but is it really what you require?

Normally, you would use this type of setup to reduce the second combobox's recordset to a subset of a larger set, thus providing only relevant choices to the user.

If each choice in the first combobox can have one and only one choice in combobox 2 (as your question implies), it would be just as easy to set this value via code as it would be to get the combobox to display the value.

Example code:
strSQL = "SELECT [YourTable].[ID], [YourTable].[NextField] From [YourTable] WHERE ((([YourTable].[YourField]) = " & me.ComboBox1 & &quot);"
Me.Combobox2.RowSource = strSQL

OR you could paste the SQL statement directly into the RowSource property of ComboBox2.

HTH
Lightning

 

[Durdles]

Lightning,

I am having problems making your SQL work, can you maybe post an example? This is exactly what I want to do, but it doesn't seem to work despite close inspection of my syntax to make sure it's right.

Thank you,

Brian

 

[Lightning]

Can you post your existing code for us to look at.

Lightning

 

[Durdles]

This is essentially a copy of what you posted pasted into the Row Source of my combo box.

SELECT [Tbl_Employee].[Display_Name] FROM [Tbl_Employee] WHERE ((([Tbl_Employee].[Location])=" & me.Combo106 & &quot);

Combo106 is a combo box drawing values from a table of depot locations. I would like the user to select a depot location from Combo106 and then when they go to select an employee, they only see the employees for that particular depot instead of seeing all the employees from all the depots.

Any help you can give is greatly appreciated.

Thanks,
Brian

 

[Lightning]

Sorry, I didn't explain that code as well as I could have.

Because you are using a string expression, you need to enclose the me.Combo106 in the Where clause in quotes inside the existing quotes.

SELECT [Tbl_Employee].[Display_Name] FROM [Tbl_Employee] WHERE ((([Tbl_Employee].[Location])=" & "me.Combo106" & &quot);

This can get messy and/or hard to read/debug, so I wrote a function that I use whenever I need to do this.

Function strQuotes(strSource As String) As String
'Sets Quote marks in a string expression or variable
strQuotes = Chr$(34) & strSource & Chr$(34)
End Function

Put this into a new module, and then use it instead of the quotes.

SELECT [Tbl_Employee].[Display_Name] FROM [Tbl_Employee] WHERE ((([Tbl_Employee].[Location])=" & strQuotes(me.Combo106) & &quot);"

HTH
Lightning

 

[Durdles]

Lightning,

First of all, thank you for all your help. However, I am still having problems making this work. Before when the syntax was wrong, it was simply not working in that the second combo box wasn't displaying any employees. Now with the change that you helped me with, I get the following error:

Syntax Error (missing operator) in query expression '((([Tbl_Employee].[Location])="&"me.Combo106"&&quot);'

I also tried it using the module you gave me with no success. I don't get the error when I use the module, but it doesn't work to display a list of employees from a particular depot location. Any other ideas? I am posting this little query in the Row source of the Properties. I am assuming this is right since other combo boxes that I use query from this location, but I wanted to make sure you are aware of this. Any help you can give is greatly appreciated.

Brian

 

[Lightning]

Brian
I'm sorry, I've misled you a bit. I haven't used this procedure for a while and I should have gone back and looked it up.

When I looked back at an old application that includes this, I realised that I was setting the Row Source for the second combobox in code in the AfterUpdate event of the first combobox, NOT in the combobox's RowSource property.

In the AfterUpdate event of Combo106 combobox add the following code (changing YourSecondComboBox to the actual combobox name):

Private Sub Combo106_AfterUpdate()
Me.[YourSecondComboBox].RowSource = "SELECT TBL_Employee.Display_Name FROM TBL_Employee" _
& " WHERE ((([TBL_Employee].[Location])=" & MyQuotes(Me.Combo106) & &quot);"
End Sub

I have tested this in a sample app and it does work.

HTH

 

[Durdles]

Thank you so so so much!! It works!