Combox box filtered based on other combo box

[Ben Pelech]

Hello,

I have 2 combo boxes. One is a category, and the other is a subcategory.
category combobox is cmbotype
subcategory combobox is cmboreason

My goal is to have cmboreason be limited only to the reasons that pertain to
the cmbotype category.

I have 2 tables:

the category table is
ContactID
ContactType

the subcategory table is
ReasonID
ContactID
Reasons

The Contact ID in the subcategory matches the ContactID in the contact type.

This code was entered in the Afterupdate of the category combo box

Private Sub cmbotype_AfterUpdate()
Dim sReason_for_Contact As String

sNewReasonsforCallSource = "SELECT [tblNewReasonsforCall].[ReasonID],
[tblNewReasonsforCall].[ContactID], [tblNewReasonsforCall].[Reasons] " & _
"FROM tblNewReasonsforCall " & _
"WHERE [ContactID] = " & Me.cmbotype.Value
Me.cmboreason.RowSource = sReason_for_ContactSource
Me.cmboreason.Requery

End Sub

I got the above code from another article that I was trying to follow, but
when I select the main category, the subcategory is blank. So I am sure I am
just entering the code wrong.

Any help would be greatly appreciated.

Thanks
Ben

 

[Arvin Meyer [MVP]]

Here's an example database displaying what you require:

http://www.accessmvp.com/Arvin/Combo.zip