Storing values from a listbox

[Larry Salvucci]

Can someone explain to me the concept of storing values from a listbox if I'm
allowing users to select multiple items? I understand the single select
process and how it stores it but do I need to set up a different table to
store the multiple values? And how do you store multiple values into one
field? Or do you?

 

[Allen Browne]

Don't use a multi-select list box to try to jam multiple values into a
field.

Instead, create a related table, so one of your main records can have many
related records in the new table. Then use a subform, so the user can enter
multiple records (one per row.)

 

[Larry Salvucci]

That makes sense, thanks. But how to I pass the multiple values to the
subform fields? I assume I have to create a number of fields in this related
table to store the values. For example if the user wants to select 10 values
then I would need 10 fields?

 

[Duane Hookom]

No, you would not want 10 fields for 10 values. You would create one related
record per value. Consider the Northwind sample database with Orders and
Order Details. The Orders table doesn't have a single field with all products
in it. It also doesn't have 10 sets of field for up to 10 different products
in the order.

There is an Order Details table that has one record per product per order.

 

[Larry Salvucci]

I understand now. I would relate the tables based on my primary key from my
main table. Correct? But how do I pass tha values over to this table if
multiple values are selected from the list box? And will the users be able to
view all these records on the subform so they can see which values were
selected?

 

[Duane Hookom]

The subform has Link Master/Child properties that maintain the foreign key
value in the child table. When you add a new, related record to the subform,
the "master" field value from the main form is updated to the "child" field
value of the subform.

 

[Larry Salvucci]

So where do I put my list box? Does it go on the main form or the subform?
And how do I pass the values to the subform?

 

[Duane Hookom]

Ditch the list box. Don't use it. Use a subform in place of the list box.
This is what Allen suggested in his first reply in this thread.

 

[Larry Salvucci]

I'm on the same page now. I misunderstood what Allen said. Thanks for the
clarification!