How do I use one list box to restrict results in another?

[Guest]

How can I use a selection in one list box (lstLead) to restrict records in a
second list box (lstSub) in an unbound form?

lstLead lists assembly part numbers (RecordSource= qry Tracking).
lstSub lists sub assembly part numbers based on assembly PN (RecordSource=
qry SubAssy).

I want to restrict the sub assy part number in lstSub to the selected part
number in
lstLead. How can I do this?

I initially created this functioanlity using subforms, but opted to use list
boxes for programming reasons. The link to the help I received on this
original matter is
:http://www.microsoft.com/office/com...3eef41-766e-4e41-bd74-11719b8c247c&sloc=en-us.

 

[Guest]

in qry SubAssy (spaces in name = bad), in the criteria for your part number,
use
Forms!YourFormName!lstLead

Then, in the lost focus event of lstLead, requery lstSub.
My first thought was to use the After Update event, but it has been a while
since I have used cascading list boxes, and I seem to recall there was an
issue putting it there. It may be the list box after update event fires each
time you add a selection.
In reality, if you are only selecting one part number, combo boxes would be
easier to deal with. If you are using multi select for lstLead, then the
above will not work. You will have to write a function that loops through
the list box's ItemsSelected collection and build a where condition using
IN() or something like that.

 

[fredg]

How can I use a selection in one list box (lstLead) to restrict records in a
second list box (lstSub) in an unbound form?

lstLead lists assembly part numbers (RecordSource= qry Tracking).
lstSub lists sub assembly part numbers based on assembly PN (RecordSource=
qry SubAssy).

I want to restrict the sub assy part number in lstSub to the selected part
number in
lstLead. How can I do this?

I initially created this functioanlity using subforms, but opted to use list
boxes for programming reasons. The link to the help I received on this
original matter is
:http://www.microsoft.com/office/com...3eef41-766e-4e41-bd74-11719b8c247c&sloc=en-us.

Leave the RowSource property of lstSub blank.

Code the AfterUpdate event of lstLead something like this:

lstSub.RowSource = "Select TableName.SubAssemblyNumber from TableName
Where TableName.PartNumber = " & Me.lstPart

The above assumes the PartNumber is a Number datatype.
If PartNumber is Text datatype, then use:

Where TableName.PartNumber = '" & Me.lstPart & "'"

Change TableName to whatever the actual table name is.
Change SubAssemblyNumber and PartNumber to whatever the actual field
names are.
Make sure the bound column of lstPart is the correct datatype.

 

[Guest]

Thanks Guys...

I used the code posted by Klatuu and it worked great.
I will look into your comments on Monday Fredg

Thanks again!!