Need quick help on dependent cbxs

  • Thread starter Thread starter mirax_00
  • Start date Start date
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
 
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
 
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.
 
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...
 
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.
 
It could be the syntax you're using in your sql. Put the table name in
square brackets as in [tbl].field
 
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
 
Post the sql - not the vba code to produce it, but the sql string that is
generated (grab it from the debug window).
 
Back
Top