Multi Select on Listboxes and saving to a table

[crispy02]

Help, I have a list box where I have set the multiselect property to Simple. I have 2 tables set up one with the customer name, address and phone number. The other table is where I plan to store the information that is selected in the listbox which is related to the information in the first table. I need to save this information so reports can be run at a later date. What is the best way to go about doing this. Thank you for any help.

Chris

 

[David R]

Multi-select listboxes are not for storing data

Once you make a listbox multi-select, it can no longer be bound to a table. This would violate the relational database idea of "atomic" data - storing one piece of information in a field. Multi-select listboxes can be used to select criteria for a report or query, but not for storing data without a good chunk of VBA code.

What you might consider using instead is a continuous subform that has a combo box on it. Then you can select each combo box to be a record you wanted from your listbox, and make as many subrecords as needed to get all of them related to the main form. This is how a One-to-Many relationship is commonly done.

Your other alternative is to use VBA code to loop through the checked entries in a multi-select listbox and create an associated record for each one, but that is not a trivial task.

 

[crispy02]

Do you have a example of a continuous subform suggestion that you could send me?

Thanks

 

[David R]

It depends on which way you want to associate the data...

Are you storing multiple pieces of data with one Person (first table), or are you wanting to store the same piece of data with many Person entries?

In either case, your main form contains the 'single' piece of data; If you're storing many thing with a person's record, then the main form is the Name fields, Address fields, Phone number, etc...

The subform is based on a table that is related to the main table. It should include its own Primary Key (probably Autonumber), but also a field of the same type as the PK from your main table; Text for Text, Long Integer for Autonumber. Then the fields you want associated with it.

I usually create my subform separately and build it to fit the space in my main form, then insert it using the toolbox Insert Subform/Subdatasheet option. The wizard for this is pretty good about making sure the subform is related to the main form (It will suggest "Show every related record in table2 where PK <=> PK in table1", or something similar.

Then you can select many things in the subform to associate with your main entry.

Does that help?