Cascading List Box

[Guest]

Hello All,

I am tring to create cascading list boxes where the criteria in the second
list box depends on the selection from the first list box. Here is some
general info and my code. Any help would be appreciated.

1st list box = lboPrimaryOcc
2nd list box = lboSecondOcc

Table 1:
Table Name - tblPrimOccurrence
Pimary Key - numPrimOccPK
Field Name - strPrimOccLabel

Table 2:
Table Name - tblSecOccurrence
Pimary Key - numSecOccPK
Field Name - strSecOccLabel

The first list box (lboPrimaryOcc) has a row source of "tblPrimOccurrence".
The second list box (lboSecondOcc) does not have a record source.

I have coded the after update event of the first list box (lboPrimaryOcc) as
follows:

Dim strSQL As String
strSQL = "SELECT lboPrimaryOcc "
strSQL = strSQL & " FROM tblPrimOccurrence "
strSQL = strSQL & "WHERE numPrimOccPK = " & Me!lboPrimaryOcc
Me.lboSecondOcc.RowSourceType = "Table/Query"
Me.lboSecondOcc.RowSource = strSQL

I have no otrher code anywhere else. Can someone let me know where I'm
going wrong.

Regards,
George

 

[Douglas J. Steele]

Does tblSecOccurrence have a foreign key field numPrimOccPK in it to relate
it back to tblPrimOccurrence?

lboSecondOcc needs to get its data from tblSecOccurrence, not
tblPrimOccurrence:

Dim strSQL As String
strSQL = "SELECT lboPrimaryOcc "
strSQL = strSQL & " FROM tblSecOccurrence "
strSQL = strSQL & "WHERE numPrimOccPK = " & Me!lboPrimaryOcc
Me.lboSecondOcc.RowSourceType = "Table/Query"
Me.lboSecondOcc.RowSource = strSQL

 

[Guest]

Thanks Doug. I tried that and it still is not working properly.

If I select the first choice in the 1st listbox I get "1" listed in the
second listbox. If I select the second choice in thje 1st listbox, I get "2"
listed in the second listbox. Same occurres for all the other selections in
the first listbox...Any idea what is wrong?

 

[Douglas J. Steele]

That's what I get for not looking that closely at your SQL! <g>

You've got SELECT lboPrimaryOcc: that should be SELECT numSecOccPK (or
whatever field it is from tblSecOccurrence that you want to have in your
listbox)

 

[Guest]

Thank you very much Doug, it worked...Only took me 2 hours lmao