dependent combo has no value or doesn't refresh

[jking.mails]

Hello, I am newbie to Access and I use Access 2007. I have 2 tables, tblCategories and tblProducts.

tblCategories is a list of brands.
Each line of tblProducts is a pair of brand and a product of this brand.

Then I created a blank form (formSale) with 2 combo boxes. The first combo box (cboCategories) has Rowsource from tblCategories. I hope the second combo (cboProducts) list the corresponding products when cobCategories is set with a value.

I tried to make the second combo (cboProducts) as a dependent combo or cascading combo by following Microsoft Access 2007 demo, and I wrote cboCategories's AfterUpdate as

Private Sub cboCategories_AfterUpdate()
Me.cboProducts.RowSource = "SELECT tblProducts.ProductName FROM" & _
" tblProducts WHERE tblProducts.Category = '" & Me.cboCategories.Value & "' " & _
"ORDER BY ProductName;"
End Sub
But I just couldn't get the same results.

If I leave the Rowsource of cboProducts empty, as shown in Microsoft's demo, then in my file, no matter which value of cboCategories is chosen, cboProducts is always blank.

If I set the Rowsource of cboProducts as
SELECT ProductName, Category FROM tblProducts WHERE (Category=[forms]![formSale]![cboCategories]);
then I could get the results I want for the first record. However, for the following records, cboProducts always showed the product list of the 1st record and wouldn't refresh, even when the cboCategories box has different values.

What is wrong? I have read many documents but couldn't solve this problem. Please help. Many thanks.

 

[oxicottin]

Here is a test one I made for myself. I might help you I might not!

 

[jking.mails]

Hi, oxicottin, Thanks. But I got the same problem with your file. That is, the first time I select a department, I can see a list of the categories. But when I chose another department again, I still saw the same list and the categories didn't change accordingly. Is this the same on your machine?

The interesting thing is, when I ran Microsoft's syncronizing combo boxes example, I could get the correct results. That is, the dependent combo changed with the main combo. But I couldn't see any difference from their code.

ps. I attached my file. Please have a look. Thanks

 

[oxicottin]

Sorry jking.mails, I don't have access 07 on my personal PC so I cant see if your file is working for me. The file I uploaded worked for me though, Every time I changed a department it cleared the categories and updated the list.

Chad....

 

[JANR]

You are missing a Requery of the second combobox.

For your first atempt:

Private Sub cboCategories_AfterUpdate()
Me.cboProducts.RowSource = "SELECT tblProducts.ProductName FROM" & _
" tblProducts WHERE tblProducts.Category = '" & Me.cboCategories.Value & "' " & _
"ORDER BY ProductName;"
[COLOR=red]Me.cboProducts.Requery[/COLOR]
End Sub

For your second atempt:

Private Sub cboCategories_AfterUpdate()
[COLOR=red]Me.cboProducts.Requery[/COLOR]
End Sub

JR

 

[ImLost]

Thank you guys - this solved the problem I've been having and made it so much easier.