Linking Option Group and Combo Box

[Dionysius]

I have a table tblSubcategories with four fields:

ID
Group
Category
Subcategory

I have a form Form8 with an option group grpCategory with 3 options (Values 1-3)

There is also a combo box cboSubcategory

I wish the drop down list from the combo to change based on the option group selected in grpCategory.

The code I am using is as follows:

Private Sub Form_Current()
On Error Resume Next
Dim strCategory As String
If IsNull(cboSubCategory.Value) Then
grpCategory.Value = Null
End If
' Synchronise Category combo with existing SubCategory
strCategory = DLookup("[Category]", "tblSubcategories", "[SubCategory]='" & cboSubCategory.Value & "'")
Select Case strCategory
Case "Access"
grpCategory.Value = 1
Case "Clinical"
grpCategory.Value = 2
Case "Consent"
grpCategory.Value = 3
End Select
' Synchronise SubCategory combo with existing SubCategory
cboSubCategory.RowSource = "Select tblSubcategories.SubCategory " & _
"FROM tblSubcategories " & _
"WHERE tblSubcategories.Category = '" & strCategory & "' " & _
"ORDER BY tblSubcategories.SubCategory;"
End Sub

Private Sub grpCategory_AfterUpdate()
On Error Resume Next
Dim strCategory As String
Select Case grpCategory.Value
Case 1
strCategory = "Access"
Case 2
strCategory = "Clinical"
Case 3
strCategory = "Consent"
End Select
cboSubCategory.RowSource = "Select tblSubcategories.SubCategory " & _
"FROM tblSubcategories " & _
"WHERE tblSubcategories.Category = '" & strCategory & "' " & _
"ORDER BY tblSubcategories.SubCategory;"
End Sub

My combo box drops down and empty list when an option is selected. Can anyone advise me where I am going wrong?

Thanks

Craig

 

[giacomo]

no idea if this will fix the problem or not but I'm not seeing that you're requerying after you change the rowsource on the combobox.

If you add me.cboSubCategory.requery to the end of each sub do you get records?

hth,
Giacomo

 

[Dionysius]

Thanks for the suggestion but still no joy.

Craig

 

[giacomo]

Ok, here's the wierd thing. I saw that I was no help to you so I built a form and copied in your code and get ready to debug it when I discovered that it works fine for me

So if it works for me, then it should work for you too. Check the spelling of everything, when I have code that I know should work and it doesn't it's usually because I fat fingered something. Also make sure that your query does actually return rows.

Good Luck!
Giacomo