Cascade List Boxes?

[Jay]

I have a form with 3 cascading combo boxes; combo box 3's list is
dependant on the selection from combo box 2's list which is dependent on
the selection from combo box 1.

However because of the number of records in each box I would prefer to
use list boxes. I've tried to just do exactly the same as my combo
boxed form but using list boxes, but can't get it to work.

For example, my row source for the second list box is:

SELECT DISTINCTROW tblDerivatives.Model FROM tblDerivatives WHERE
(((tblDerivatives.Manufacturer) Like
forms!frmMovementRationaleLists!lstManufacturer));

where lstManufacturer is the first list box.

List box lstManufacturer works OK but I get a 'Enter Parameter Value'
prompt detailing 'forms!frmMovementRationaleLists!lstManufacturer'

AM I trying to do something that you can't do this with list boxes like
you can with combo boxes.

If so, can anyone advise me what I'm doing wrong.

Many thanks,

Jason

 

[Guest]

Jason:

It should work equally well with list boxes. Make sure you have the
reference to the control correct. BTW you don't actually need to use a
fully qualified reference as the controls are all on the same form. You can
reference the form object via the Form property:

Form!lstManufacturer

Also don't use the LIKE operator unless you are using patterns with
wildcards. Use the = operator instead. So putting these two points together
the ControlSource property would be:

SELECT Model FROM tblDerivatives WHERE Manufacturer = Form!lstManufacturer;

Ken Sheridan
Stafford, England

 

[Arvin Meyer [MVP]]

List boxes work the same way. Have a look at:

http://www.accessmvp.com/Arvin/Combo.zip

for a sample of dependent listboxes.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Jay]

Many thanks for the help Ken. The reference to the control is definitely
correct but I couldn't get it to work & I can't figure out why. Anyway I
downloaded the example db as mentioned in Arvin's reply & have managed to
reproduce the method used (using queries as control sources for the list
boxes).

Anyway, all works fine now - apart from one thing. I have the form set for
Data Entry. I have 3 list boxes and when the form is first opened the first
list is populated (but nothing is selected) and the next 2 list boxes are
empty until a choice is selected in list boxes 1 & 2.

However, when the first record is complete and I move to the next record,
the list boxes retain the lists from the previous record (until a *new*
choice is made).

How can I make it so that on each new record list box 1 is populated (but
nothing selected) and list boxes 2 & 3 are blank/empty until choices are
made in list boxes 1 & 2 respectively.
The lists are very long so I want list one to be at the top of the list & 2
and 3 to be clear until choices are made in 1 and 2.

Many thanks for your help.
Kind Regards

Jason