Multi-Select List Box

[Mechele]

I have a list box on a form where the user can make several choices. I allowed for extended multiselections. The list box is bound to a field. The field's data type is text.

How can I get the user's multiple selections to save in the bound field. Should I define the bound field as a memo instead of a text field or something else?

 

[dcx693]

There is a huge difference between a single-select listbox and a multi-select listbox, as you're just begininning to discover. Because a mult-select listbox returns multiples values and you can only store a single value within a field, you cannot store all the selections of a multi-select listbox in a single field.

What you really need is to use a separate table to store the results. Think of it this way, if you are taking orders for a customer, and you select multiple items for them, you'll need an order table and an order details table. Same idea. It's a one-to-many relationship.

As with all one-to-many relations, among the easiest ways to get the data saved is to use a form/subform structure. Another is to use VBA code to save the details in the 2nd table.

 

[theprez]

Read carefully what dcx693 is saying and if what you really need is to take multiple selections of a listbox than you will have to make the listbox unbound and if its a case where you want to combine all the selections the user chose in one field, you will have to write code to enumerate through the selections and put in a field. For example if you have a listbox of cars and you want to populate a field that is based on user selections

mercedes lexus jaguar ford

you will probably need a memo field that is populated with code.

You need to distinguish this from taking those selections and placing them in a table where each user selection becomes a separate record (row) in the table.

Which do you need?

 

[Mechele]

The user can make the following selections from the list box:

Inpatient
Outpatient Surgery
Outpatient Diagnostic
DME
Psych
Home Health

The user can make more than one selection. I need the user's selection(s) to be saved in one record/field in a table.

 

[pbaldy]

There's a method here to do what you want, along with cautions against it. It sounds like most users here don't think this is the right approach, including me. Storing multiple values in one field will cause you grief in the long run.

http://support.microsoft.com/default.aspx?scid=kb;en-us;210203

 

[Pat Hartman]

Mechele, Storing multiple values in a single field violates first normal form. There is no built in support to help you do this since it is poor practice. You are on your own with code. Before you commit to handling the data this way, make absolutely certain that you will never need to do any analysis of any kind on this multi-valued field. For if you need to do anything with these values, including simply displaying them, you will find them extremely difficult to work with especially considering storing them properly would not require any code at all nor would you need code to analyze the values. Simple queries would work.

 

[dcx693]

Mechele, I think you get the gist of what we're all saying. The whole point of a database is the storage and retrieval of information. If you can store the information, but you can't retrieve without jumping through hoops, then no one's going to use that data. In about a week, you'd be posting back to the forums asking us how to run a query to find data all saved into one field.