Cascading Combo/List Boxes help

[RZ15]

Hi,

I've been through the tutorial for this and I've tried applying it to my database and it almost works but not quite.

i have 2 combo boxes, cboPrgrp and cboSupplier. cboPrgrp is supposed to populate once a selection is made for cboSupplier. When a selection is made in cboSupplier and I click the dropdown box for cboPrgrp, it will prompt me saying whatever the selection is in cboSupplier. If I type in what was selected in cboSupplier, then cboPrgrp will populate properly. My question is why is it prompting me?

Here is the code to populate cboPrgrp:

Private Sub cboSupplier_AfterUpdate()
With Me![cboPrgrp]
.RowSource = " "
.RowSource = "SELECT DISTINCT [COMCOD] " & _
                     "FROM dbo_ICI1 " & _
                     "WHERE [SUPPLR] =" & Me!cboSupplier
.Requery
End With
End Sub
 

Note that [COMCOD] is what goes in cboPrgrp.

Anybody know why it's prompting me?

[JKing]

Is the prompt specifying anything or are you just seeing "Enter Parameter Value"? This error is usually caused by typos as Access cannot determine which variable /control/field you are referring to.

[RZ15]

Is the prompt specifying anything or are you just seeing "Enter Parameter Value"? This error is usually caused by typos as Access cannot determine which variable /control/field you are referring to.

It says "Enter Paramater Value" in the caption of the box (the top bar of the box). Then it says whatever the selection is in cboSupplier and then it has a box for me to type something in. So if my selection in cboSupplier is "EPPUSA" It looks like this:

Enter Parameter Value|X|

EPPUSA_______________ _____
||_____________ ____________|

This is what I'm seeing.

[JKing]

Hehe I liked the visual, thank you. I think I know the problem.
Give this a try:

Private Sub cboSupplier_AfterUpdate()
With Me![cboPrgrp]
.RowSource = " "
.RowSource = "SELECT DISTINCT [COMCOD] " & _
                     "FROM dbo_ICI1 " & _
                     "WHERE [SUPPLR] = '" & Me!cboSupplier &"'"
.Requery
End With
End Sub
 

Supplier is of the text data type. So you need to enclose it with the single quotes. I believe without them access assumes the value of your combo box to be an unknown variable.

[RZ15]

Hehe I liked the visual, thank you. I think I know the problem.
Give this a try:

Private Sub cboSupplier_AfterUpdate()
With Me![cboPrgrp]
.RowSource = " "
.RowSource = "SELECT DISTINCT [COMCOD] " & _
                     "FROM dbo_ICI1 " & _
                     "WHERE [SUPPLR] = '" & Me!cboSupplier &"'"
.Requery
End With
End Sub
 

Supplier is of the text data type. So you need to enclose it with the single quotes. I believe without them access assumes the value of your combo box to be an unknown variable.

It works beautifully now, thanks bud :) .

[JKing]

No problem glad I could help.