Cascading combobox query problem

[Lol999]

Hi, I'm having some trouble with updating the rowsource for a combobox which cascades from the one above.

It DID work but since altering relationships in the tables along the lines of what Pat recommended the combobox now no longer works.
There are 2 comboboxes on frm Product, cboCategory and cboProduct.
On from load the cboProduct shows all the records available in the product table.
cboCategory shows the categories available.
It is when I try to update the rowsource for cboProduct in the afterupdate event for cboCategory that I'm just getting a blank box.
I actually only want to display 2 fields in cboProduct, ID_Product and Details.
Debug printing the returned value from cboCategory in the afterupdate event shows the correct category is being passed to the rowsource query but I'm still getting a blank combobox.

I've attached the database for perusal, ans still very much a work in progress.

Thanks, Lol

 

[MarkK]

Try...

Private Sub cboCategory_AfterUpdate()
    Const SQL As String = _
        "SELECT ID_Product, Details " & _
        "FROM tbl_Product " & _
        "WHERE ID_Tool_Category = "
        
    Me.cboProduct = Null
    Me.cboProduct.RowSource = SQL & Me.cboCategory
End Sub

See what that does?
Mark

 

[Lol999]

Mark that's perfect thank you, but could you tell me where I was going wrong with the following code?

Private Sub cboCategory_AfterUpdate()
Dim SQL As String
Dim Combo As String
Combo = Combo & Me.cboCategory
Me.cboProduct = Null
SQL = "SELECT  Tbl_Product.ID_Product, Tbl_Product.ID_Tool_Category, Tbl_Product.Part_No, Tbl_Product.Details, Tbl_Product.Date_deCommissioned " _
& "FROM Tbl_Product " _
& "WHERE ISNull(Tbl_Product.Date_deCommissioned) AND Tbl_Product.ID_Tool_Category= '" & Combo & "';"
Me.cboProduct.RowSource = SQL
Me.cboProduct.Requery

    
End Sub

 

[MarkK]

I don't know. I didn't look at your code, I just downloaded your db and made it work. Note that my SQL is not the same as yours. Mine does not filter for decommissioned date, so if the is crucial, that should be added.

And looking at your code now..., ...maybe here at the end of your SQL you are treating the CategoryID as a string...

...Tbl_Product.ID_Tool_Category= '" & Combo & "';"

...but to explore that more, Debug.Print SQL to the immediate pane, then copy that result into the query designer in SQL mode, and test it that way. Always test your SQL like that. It identifies errors much more clearly and saves you time as a result.
hth
Mark

 

[Lol999]

Mark I think you just hit the nail on the head, I need to declare Combo as an Integer not String

many thanks, Lol

 

[MarkK]

No, you need to print the SQL to the immediate pane then test it, and you'd find out that it's the single quotes in the SQL that are killing the WHERE clause expression, causing it to never evaluate to true.

If you look at the code I wrote, which works, there is no declaration for "combo." You don't need to do that. For instance, this...

Dim Combo As String
Combo = Combo & Me.cboCategory

...adds nothing to Me.cboCategory, right? And then this...

AND Tbl_Product.ID_Tool_Category= " & Me.cboCategory & ";"

...would work fine, so there is no need for the variable Combo that you've declared.
hth
Mark

 

[Lol999]

Cheers Mark, much appreciated. I struggle with the quotation marks thing sometimes.

I'll give it a try.

Thanks, Lol