Problem with query structure

[j_horbach]

Hello...

Attached is the access db I am working on.

My problem is in the following code:
(Sorry for this. I don't know how to place code into a thread.)

Private Sub cmbxMainCatName_AfterUpdate()

Dim updateString As String

lblSUBCATEGORYTitle.Caption = cmbxMainCatName.Value

updateString = "SELECT [Main Category].[MainCatName], [MainCategory].[MainCatID], [Sub Category].[MainCatID], " & _
"[Sub Category].[SubCatName] FROM [Main Category] " & _
"INNER JOIN [Sub Category] " & _
"ON [Main Category].[MainCatID]=[Sub Category].[MainCatID] " & _
"WHERE ((([Main Category].[MainCatName])=cmbxMainCatName.Value));"

cmbxSubCatName.RowSource = updateString

End Sub

cmbxSubCatName should contain the SubCatName's under the Main Category selected in cmbxMainCatName, not the SubCatID's.


Please try the db to see what I mean.
Please help.

 

[Pat Hartman]

Two things come to mind :
1. You need to fix the syntax
"WHERE ((([Main Category].[MainCatName])= '" & Me.cmbxMainCatName &"'));"

2. You need to requery the combo after changing its RowSource
Me.cmbxSubCatName.Requery

BTW, it is more efficient to use stored querydefs rather than SQL strings in code. Your SQL is not dynamic so there is no reason to build it on the fly. If you use a stored querydef, change the where clause to:
"WHERE ((([Main Category].[MainCatName])= '" & Forms!YourForm!cmbxMainCatName &"'));"