VBA Combo filtering

[aknorth]

I am having trouble filtering one combo box on another. I found this very useful tutorial


http://www.databasedev.co.uk/filter_combo_boxes.html
I even downloaded the sample database, however I am having a very hard time implementing it on something else

Here is their code
Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[lngManagerID], [tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
End Sub

cboStore Row source SELECT [tblStore].[lngStoreID], [tblStore].[strStoreName] FROM tblStore;
cboManager Row source SELECT [tblManager].[lngManagerID], [tblManager].[lngStoreID], [tblManager].[strManagerName] FROM tblManager;

Here is my code

Private Sub cboFirstName_AfterUpdate()
Dim sLastSource As String


sLastSource = "SELECT [contactsT].[ID], [contactsT].[first_name], [contactsT].[last_name] " & _
"FROM contactsT " & _
"WHERE [first_name] = " & Me.cboFirstName.Value
Me.cboLastName.RowSource = sLastSource
Me.cboLastName.Requery
End Sub

Basically I have a contacts table with ID, first_name, and last_name and combo boxes cboFirstName and cboLastName

cboFirstName Row source SELECT [contactsT].[ID], [contactsT].[first_name] FROM contactsT;
cboLastName Row source SELECT contactsT.ID, contactsT.first_name, contactsT.last_name FROM contactsT;

I am completely flummoxed as to why this isn't working. Does anyone see something I am missing?

[pbaldy]

You need delimiters around text values, like:

"WHERE [first_name] = '" & Me.cboFirstName.Value & "'"

[aknorth]

Thank you for the quick reply. Unfortunately that didn't solve the problem whenever I select the last name combo box it just shows blank. I have attached a super simple sample database just one table and the form with the two combo boxes I am trying to get this to implement on.

[pbaldy]

Use this and you should see the problem (teaching how to fish):

http://www.baldyweb.com/ImmediateWindow.htm

[aknorth]

I completely understand that doing something is the best way to learn and giving the solution doesn't always help. I have been plugging away at this for 2 hours now so I am not trying to be handed the solution. So under the VBA editor I added the immediate window and I modified my code so it now reads

Private Sub cboFirstName_AfterUpdate()
Dim sLastSource As String

sLastSource = "SELECT [contactsT].[ID], [contactsT].[first_name], [contactsT].[last_name] " & _
"FROM contactsT " & _
"WHERE [first_name] = '" & Me.cboFirstName.Value & "'"
Debug.Print sLastSource
Me.cboLastName.RowSource = sLastSource
Me.cboLastName.Requery
End Sub

I do not know what to do next. I can't seem to run the code to get it to output that to the Immediate window I hit the green run button and the Macros window pops up.

I created a macro to run it in it stated Compile error: Invalid use of Me keyword and the Me in the Me.cboFirstName.Value is highlighted

SQL in the bottom states

SELECT [contactsT].[ID], [contactsT].[first_name], [contactsT].[last_name] FROM contactsT WHERE [first_name] = '1'

So the only thing I see is an issue is maybe contactsT need brackets and also whatever is to the right of the = sign is not right somehow

[pbaldy]

You would just run the code by changing the option in the first combo. That said, you've suffered enough. The bound column of the first combo is the ID field, so you're comparing apples to oranges. I'd change the first combo to just return first names, since the ID field is irrelevant there. I'd also have it return a single instance of each name, instead of however many exist in the data. Something like:

SELECT DISTINCT contactsT.first_name FROM contactsT;

[pbaldy]

Oh, and FYI the debug would give you:

SELECT [contactsT].[ID], [contactsT].[first_name], [contactsT].[last_name] FROM contactsT WHERE [first_name] = '1'

[aknorth]

Man you have been super patient with me, I really don't want to ask any more ?'s but I would like to clarify some things to make sure I know how to fish.

1) What do you mean by "run the code by changing the option in the first combo"?

2) I see what you mean the first combo box has two fields and it is selecting the ID and trying to compare to first_name and they don't match up that makes sense

I took the following actions

1) Changed Row Source for cboFirstName to SELECT DISTINCT contactsT.first_name FROM contactsT;

This created a problem however now nothing showings up in the first name combo box.

Getting rid of the distinct and setting Row Source SELECT contactsT.first_name FROM contactsT;

This also only shows blank entries, the only way I get the combo box to show anything on the combo box is to add back in the ID field

[pbaldy]

Your code is in the after update event of the first combo, so with the Debug.Print in the code, you can simply open the form and make a selection in the first combo which will run the code and the output should go to the Immediate window.

You have to change the column widths property of the first combo. It's currently hiding the first column. I'd also change the column count to 1.

[aknorth]

Finally got it! Thanks a ton.

I am not sure what was creating the issue but if you create a combo box using the "I want the combo box to get the values from another table or query" it will insist on adding the ID field if you delete the ID field it will show a blank drop down for the remaining field. Creating one from scratch allows you to bypass this. There must be an attribute you can change to correct this though I do no know what it is.

Thank you thank you

[pbaldy]

Happy to help! Yeah, it seems like the wizard used to work pretty well, and would create it properly if you didn't want the ID field. Now...