Can I link a list box from one from to another?

[Debi]

I have a form with a list box that is based off a table.... then I have
another form based off the same table that I want to open when you click on
one of the records in the list box. Can this be done without having to use
as a subform.... like can this work with the two different forms?

 

[LloydBrown]

Sure. Have the query behind the second form refer to the choice made in the
first rorm's list box. use Forms!MyFirstForm!MyListBox1 in the criteria row
of the second for's basis query. It will only show records matching the
user's choice when opening. Slightly tougher to work out, but easy after the
first time is opening the second form to matching records if the user has
hade a choice, or to all records if the list is left blank. This can apply to
many fields and make a wonderfully flexible form. Ask if you want detail.

 

[Debi]

HUMMM.... I am not sure I understand.... can I code in the event procedure?
Or do I have to create the second form off a query?

 

[LloydBrown]

Sorry for the delay. I got busy at work.
You can put a simple open from statement in the after update event of the
list box.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "frmXXX"
(The first line saves the current record)

However the thing that makes this flexible is that the reference to the item
chosen by the user is in the query that forms the record source for the
second form.
SELECT tblBins.BSL FROM tblBins WHERE
(((tblBins.BSL)=[Forms]![frmMainMenu]![SelectABSL]));
This line selects only the bins (drawers) that are in the BSL (Bench Stock
Location - like a particular asile at Lowe's) chosen by the user on the main
menu before the bins form is opened.
The down side is you can only open the second form from the first. But if
this is a problem, there are ways around that as well.
Hope this helps. I will try to check more often from now on.