Limiting list box selection

[RNicole]

I am building a table with several list boxes. When entering a record, I
want the user's selection in one list box to effect the choices in another
list box.

How do a create a list box that displays particular values based on another
list box

 

[Frank H]

It's really helpful if you specify which version you are using. If Access
2007, then you might be using a multivalue field, in which case you can
ignore my answer cause I don't care for multivalue fields. If Access 2003,
then you don't have multivalued fields, and you might as well be using a
combo box.
I use combo boxes instead of list boxes, so a later answer may give you
different advice...(this is not the most sophisticated way of doing this, but
has worked great for me)
You can't do what you ask in a table. What you need to do is have the second
"box" be requeried after you make the selection in the first "box". That can
be done on a form.
Assuming that your list box is using a query as its recordsource, you can go
to the "second" list box properties, click into the RowSource property, and
hit the builder button to bring up the query design for the "second" box. Add
the same field to the query as what you will be picking in the "first" box.
Then as the criteria for this field, put in a reference to the value that you
picked in the "first" box. (Use the expression builder to do this, and it is
a snap.)
So far, you have made the list in the "second" box dependent upon a choice
in the "first" box. Now, on the "first" box property sheet, you need to go to
the Events tab, go to the AfterUpdate event, and double click in it to cause
it to display "Event Procedure", the hit the builder button. Your flashing
cursor will be sitting in the correct place to begin typing: "Me.[control
name of "second" box].requery"
If all this makes sense it will work like a charm.

Good Luck.

 

[John W. Vinson]

I am building a table with several list boxes. When entering a record, I
want the user's selection in one list box to effect the choices in another
list box.

How do a create a list box that displays particular values based on another
list box

You can't... *IN A TABLE*.

Table datasheets have *very* limited functionality. Microsoft is trying to
push you to use them as if they were spreadsheets, but they're not! In
practice, users should never even SEE a table datasheet; all interaction
should be done with Forms instead.

It's quite easy to do what you ask on a Form. Base the second listbox (or
combo box, which I prefer for screen space reasons) on a Query which
references the first list (combo) box as a criterion, e.g.

=[Forms]![NameOfForm]![NameOfListbox]

and Requery the second control in the afterupdate event of the first.