Cascading combo boxes help (1 Viewer)

[Chumpalot]

Hi all,

I have a table for my products (tbl_winestock). In here there is a field (Grade) that looks up values from tbl_grade.

tbl_winestock
ProdID (PK)
ProductName
Grade (FK)

tbl_grade
GradeID (PK)
Grade (A1, A, B, C etc)

In tbl_winestock it is possible to have 2 identical products but with different grades, for example:

Sparkling Wine A1
Sparkling Wine C

When recording the consumption or purchase of products I need to be able to differentiate between these products. At present they are just listed in one big combobox. Taking the example above I'd like 'Sparkling Wine' to be listed just once in the first combobox and then a second, related combobox to list A1 and C as the only options available. Stock adjustments are then recorded against this line.

I hope that makes sense. I have tried the following code on a test form

Private Sub cboproduct_AfterUpdate()
' Set the grade combo box to be limited by the selected product
    Me.cbograde.RowSource = "SELECT GradeID, Grade " & _
                            "FROM tbl_grade " & _
                            "WHERE ID = " & Nz(Me.cboproduct) & _
                            "ORDER By Grade"

End Sub

Unfortunately I get the following error: Syntax error (missing operator) in query expression 'ID = 1046ORDER by Grade'

Any help would be greatly appreciated here.

Thanks
David

 

[jdraw]

Start each new line of the sql you are building with a space.

eg.

Me.cbograde.RowSource = "SELECT GradeID, Grade " & _
                            " FROM tbl_grade " & _
                            " WHERE ID = " & Nz(Me.cboproduct) & _
                            " ORDER By Grade"

Notice: How there is no space before ORDER in your post?
'ID = 1046ORDER by Grade'

PS: Review the links in my signature for more info on debugging and errors.

For cascading combos: see these free videos from Datapig.
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

 

[Chumpalot]

So I have it working to a degree.

When I select a product in the first combobox only the related grade is shown.

What if I now wanted to cut out the duplicates (as in the example above) in the first combobox?

Private Sub cboproduct_AfterUpdate()
' Set the grade combo box to be limited by the selected product
    Me.cbograde.RowSource = "SELECT Grade " & _
                            " FROM tbl_winestock " & _
                            " WHERE ID = " & Nz(Me.cboproduct) & _
                            " ORDER BY Grade"

End Sub

Thanks again

 

[jdraw]

What is ID?
Should it be GradeID??

Can you tell us in plain English or send us a jpg of your tables and relationships?

Did you watch the videos?

 

[Chumpalot]

What is ID?
Should it be GradeID??

Can you tell us in plain English or send us a jpg of your tables and relationships?

Did you watch the videos?

ID is the PK in tbl_winestock

The videos helped a great deal. Thank you for posting those.