cbo/cbo filter small problem 1

[simon551]

I tried this code to sort a combobox by another combobox, but when I click the second combobox, I get a popup asking me to enter the parameter which is defined by the first combo box. For example: I choose ManagerID "1" in the first combobox. The pop asks: enter parameter: 1. Then if I enter "1" the query works and my 2nd box is filtered correctly. Has anyone experienced this?

Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[lngManagerID], [tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
End Sub

 

[Hasu]

Hi Simon,

Your code should work fine, there is no coding error. Could you explore more how did you code first combobox?

Hope this helps...
Hasu

 

[Zion7]

Since ManagerID is not your parameter, it must be an erroneous referenced field? is it spelled correctly?

 

[simon551]

Oh.I got confused. This is my actual code for the 2 boxes:

1.RowSource 1st box(combo18):
SELECT tblManagers.MgrID, tblManagers.Manager FROM tblManagers;

2.RowSource 2nd box (combo4):
SELECT tblSecurities.SecID, tblSecurities.MgrID, tblSecurities.Tckr, tblSecurities.Description FROM tblSecurities ORDER BY [Description];

OnUpdate, 1st box:
Dim sSecIDSource As String

sSecIDSource = "SELECT [tblSecurities].[SecID], [tblSecurities].[MgrID], [tblSecurities]. [tckr], [tblSecurities].[Description] " & _
"FROM tblSecurities " & _
"WHERE [MgrID] = "&Me.combo18.Value
Me.combo4.RowSource = sSecIDSource
Me.cboManager.Requery


The message is: Enter Parameter Value (and it prompts what it should be ie "1")

Sorry to be misleading. I appreciate your help.

 

[Hasu]

Try this,

OnUpdate, 1st box:


Dim sSecIDSource As String

sSecIDSource = "SELECT SecID, MgrID, tckr, Description " & _
"FROM tblSecurities " & _
"WHERE MgrID = " & Me.combo18.Value

Me.combo4.RowSource = sSecIDSource
Me.combo4.Requery


Hope this helps...
Hasu

 

[simon551]

Doesn't change anything.
I also have more code in combo4:


Private Sub Combo4_AfterUpdate()
Me.SecIDTxt.Value = Me.Combo4.Column(0)
Me.MgrID.Value = Me.Combo4.Column(1)
Me.Tckr.Value = Me.Combo4.Column(2)
Me.Desc.Value = Me.Combo4.Column(3)

Me.frmQueryInventory.LinkChildFields = "SecID"
Me.frmQueryInventory.LinkMasterFields = "SecIDtxt"
Me.frmQueryInventory.Requery
End Sub

 

[Zion7]

Check your Bound Column on Combo18, it should be 1

 

[simon551]

It is.

 

[Zion7]

Use a MsgBox to check the sSecIDSource output


sSecIDSource = "SELECT [tblSecurities].[SecID], [tblSecurities].[MgrID], [tblSecurities]. [tckr], [tblSecurities].[Description] " & _
"FROM tblSecurities " & _
"WHERE [MgrID] = "&Me.combo18.Value

MsgBox sSecIDSource

'Me.combo4.RowSource = sSecIDSource

 

[simon551]

not sure exactly what you are asking me to do. But I tried typing "MsgBox sSecIDSource" into the code where you put it, and it returned this after selecting the MgrID (1):

SELECT SecID, MgrID, Tckr, Description FROM tblSecurities WHERE MgrID = 1

Is that right?

 

[Zion7]

Yes, I just wanted to see if the SQL was complete. If for some reason, the combo value wasn't getting entered.

REMOVE REQUERY FROM AFTERUPDATE EVENT OF 1st COMBO

If this fails, try the next 2 suggestions...



2 things,
1st, try entering the combobox value as a variable.

Dim intID As Integer

intID = Me.Combo18

"SELECT SecID, MgrID, Tckr, Description FROM tblSecurities WHERE MgrID = " & intID

OR, hardcode this, in 2nd combo.

SELECT SecID, MgrID, Tckr, Description FROM tblSecurities WHERE ((MgrID) = (Combo18))

And simply requery 2nd combo, after 1st combo update.

 

[simon551]

Zion,
The last option worked! I put the requery in the gotfocus for 2nd combobox.
Thanks a bundle, dude!

-Simon