Need quick help on dependent cbxs

M

mirax_00

Can someone who's worked in VBA tell me what's wrong with this code:

Private Sub cboCatTree_AfterUpdate()

Dim strSQL As String
Dim index As Integer

strSQL = "SELECT
dbo_tblProductCategoryLevel.iProductCategoryLevelId, "
strSQL = strSQL & "dbo_tblProductCategoryLevel.sDisplayName "
strSQL = strSQL & "FROM dbo_tblProductCategoryLevel "
strSQL = strSQL & "WHERE
dbo_tblProductCategoryLevel.iProductCategoryLevel = "
strSQL = strSQL & Me.cboCatTree.Column(0)

Me.cboCatLevel.RowSourceType = "Table/Query"
Me.cboCatLevel.RowSource = strSQL
Me.cboCatLevel.Requery

End Sub

When I run the form it asks me for the parameter value for
iProductCategoryLevelId each time I change the value in the cbx it
depends on...

I've looked all over in these newsgroups and support.microsoft.com and
from what I can tell this is what I'm supposed to do.

Thanks ahead of time for any help anyone can give me :)

-Jayyde
 
K

kabaka

I'm not sure of your exact problem, but I'd open up the debug window and put
in a breakpoint after you create your sql string. In the debug window type
"Print strSQL" and take a look at your sql to see if you spot a problem. You
could also then just copy the sql and create a new query out of it to try and
find the error from there.

On a side note, instead of using
strSQL = "Text"
strSQL = strSQL & "..."
strSQL = strSQL & "..."

you can just do
strSQL = "Text" _
& "..." _
& "..."

just saves a little typing
 
K

kabaka

Just thought of it:

you need to indicate that the value in Me.cboCatTree.Column(0) is text. To
do this you must put single quotes around it otherwise it is just thought to
be a parameter - something like:

strSQL = "...where... =' " & Me.cboCatTree.Column(0) & " ' "

note the single quotes.
 
J

Jayyde

But it's not text, it's an integer (the hidden PK) and I need to pass
it as that for the FK relation. I msgboxed the strSQL once it was all
made and it reads fine in that screen =\. That's why it's
flabbergasting me. If for some freakish VBA reason I still need to put
the number in ''s that's cool, so I'll give that a shot =). Otherwise
I'm still at a loss...
 
K

kabaka

I'd still test it out in the debug window and copy it into a new query. Run
that query from the sql page and it MIGHT indicate where the error is.
 
K

kabaka

It could be the syntax you're using in your sql. Put the table name in
square brackets as in [tbl].field
 
J

Jayyde

Tried the quotes thing before I left yesterday and still no go. It
continues to ask me to type in a parameter when I change the 1st combo
box, even though the strSQL clearly has the value in it (again I
msgBoxed it and it's there every time). I'm pretty much exhausting all
the sites I can google and it looks like everything is right from what
they say.

Plz help!

-Jayyde
 
K

kabaka

Post the sql - not the vba code to produce it, but the sql string that is
generated (grab it from the debug window).
 
Top