Multi-select List Box and Disappering Values

[Guest]

I have a form that uses a list box to select materials. There are four
columns with: Material Name, Young's Modulus, Poisson's Ratio, and Density.
The Material Name column is the primary key in the associated table.

What I would like to do is to select multiple materials from this list box.
The ControlSource for the list box is the associated material table, and the
list box is bound to the table.

When I change the multi-select property to simple, I can select multiple
materials, but they remain constant/fixed when I go to another form (acts as
if the list box is unbound). If I change the property to extended, I can
select several materials, but they do not remain selected (if I selected
multiple materials for form 5, then go to form 6 to fill it out, if I return
to form 5, the materials are no longer selected).

Can anyone point out what I am doing incorrectly?
Thank you

 

[Dirk Goldgar]

I have a form that uses a list box to select materials. There are four
columns with: Material Name, Young's Modulus, Poisson's Ratio, and
Density. The Material Name column is the primary key in the
associated table.

What I would like to do is to select multiple materials from this
list box. The ControlSource for the list box is the associated
material table, and the list box is bound to the table.

When I change the multi-select property to simple, I can select
multiple materials, but they remain constant/fixed when I go to
another form (acts as if the list box is unbound). If I change the
property to extended, I can select several materials, but they do not
remain selected (if I selected multiple materials for form 5, then go
to form 6 to fill it out, if I return to form 5, the materials are no
longer selected).

Can anyone point out what I am doing incorrectly?
Thank you

A list box in either Multiselect mode cannot be successfully bound to a
field in the form's recordsource, because a field cannot hold multiple
values. Once you set the list box to Multiselect, you have essentially
made it an unbound control, and its value will always be Null. The
behavior as you switch between records is different between Simple and
Extended multiselect, but none of that has any effect on what is stored
in the table as you click in the list box -- nothing will be stored.

You'll need a second, related table to store the multiple materials that
are associated with the form's current record. Once you've set that up,
the easiest way to maintain that list of materials is to use a
subform -- if you do that, you don't need to write any code.

It *is* possible to use a multiselect list box to display and update the
contents of the related table, but you have to write all the code
yourself -- to select the appropriate items in the list as each record
becomes current, and to update the table in response to the user's
selections in the list box. The code isn't too complicated, but Access
won't do this for you.

 

[Guest]

Dirk,
Thanks for the info. I think I'll take the related table approach - it seems
more appropriate than to try stuffing multiple entries in a single field.

Dennis