Cascading combos refuse to cascade.

[Guest]

There is a table:
tblItems
ItemID ‘PK Long integer
Desc ‘description of item
Category ‘each category has more than one item

There are 2 combos
cboCategory
and
cboItemID

cboCategory has a value list “barrierâ€;â€bridgeâ€;â€culvertâ€;

I have spent hours trying to set the row source for cboItemID to a query

Private Sub cboCategory_AfterUpdate()
Dim sSQL As String
sSQL = "SELECT tblItems.Desc " _
& "FROM tblItems WHERE Category = " & Me.cboCategory _
& " ORDER BY tblItems.Desc"
Me!cboItemID.RowSource = sSQL
End Sub

Another query
sSQL = "SELECT ItemID, Desc " _
& "FROM tblItems WHERE Category = " & Me.cboCategory _
& " ORDER BY Desc"

A third query
sSQL = "SELECT tblItems.ItemID, tblItems.Desc " _
& "FROM tblItems WHERE tblItems.Category = ‘" & Me.cboCategory.Value
& “’†& _
& " ORDER BY tblItems.Desc"

A 4th query
sSQL = "Select tblItems.ItemID, tblItems.Desc " _
& "From tblItems Where tblItems.Category = ‘" & Me.cboCategory.Value
& “’†& _
& " Order by tblItems.Desc"


On the groups pages there are a few different way of coding the query. I
feel as though I have tried them all and none will work for me.
I have tried building the query and viewing it in SQL view and using that
for the row source as well, but copying the SQL from the query gives
incorrect syntax for the VB editor.

Below is the SQL from the query design in SQL
SELECT tblItems.ItemID, tblItems.Desc, tblItems.Category
FROM tblItems
WHERE (((tblItems.Category)="culvert"))
ORDER BY tblItems.Desc;
As you would probably know this last gives errors for “culvert†and the ;
after tblItems.Desc;

Please help.

When I get this sorted out, I need another 3 cascading combos which are
dependent on the value selected in the second combo.

 

[Jeff Boyce]

You mention "errors", but don't mention what kind, or what the error message
says. Using cascading combos is common, so perhaps there's something else
interfering...

You don't mention it directly, but I'll assume you are working in a form
with these two combo boxes.

You don't mention it, and it could be critical -- have you used a "lookup"
data type in your underlying table?

Another approach, rather than creating a procedure to "set" the RowSource,
would be to make the RowSource of the second combo box equal to the SQL.
Then you could add something like the following to the AfterUpdate event of
the first (?category) combo box...

Me!cboItem.Requery

 

[Andi Mayer]

There is a table:
tblItems
ItemID ‘PK Long integer
Desc ‘description of item
Category ‘each category has more than one item

There are 2 combos
cboCategory
and
cboItemID

cboCategory has a value list “barrier”;”bridge”;”culvert”;

I have spent hours trying to set the row source for cboItemID to a query

Private Sub cboCategory_AfterUpdate()
Dim sSQL As String
sSQL = "SELECT tblItems.Desc " _
& "FROM tblItems WHERE Category = " & Me.cboCategory _
& " ORDER BY tblItems.Desc"
Me!cboItemID.RowSource = sSQL
End Sub

is Me.cboCategory a String???
I am sure, therfore:

& "FROM tblItems WHERE Category = '" & Me.cboCategory &"'"_

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[javier barcenas]

iloveyou

 

[Guest]

Thanks for your suggestions. I took note of the idea that something might be
interfering and created a new database with only the form with the cascading
combos and necessary tables. I now have the cascading combos working
perfectly in this mini database. I still have no idea why it wouldn't work in
the original database, but I feel sure I can get it to work now.