How to prevent multiple selections in a list box from changing other records' original selection?

I have a combo search box (ComboSearch) that is controlling the contents of a form (frmIntake) below it.

In the frmIntake there is a field where one can choose from multiple options (MultipleChoicesField) and one field where one can only choose one option (SingleChoiceField).

When I go through the records wtih ComboSearch, the MultipleChoicesField keeps the same results for all the records whenever it is changed on ANY record and the SingleChoice Field changes according to what each records was individually assigned to.

How can I fix the multiple selection box so that it remembers the selection for the different records just like the single selection box is remembering the individual selections?

Thank you.
Who is Participating?
peter57rConnect With a Mentor Commented:
Here is a sample.

In the end I used a subform to handle the list.  I think this involves less work all round that any other approach and provides a completely flexible list.

Rey Obrero (Capricorn1)Commented:
upload a copy of the db...
A multi-select listbox must be unbound by its nature, so if you are storing the selected values you must be doing so in code.
At the end of your code, or separately in say, the form current event, you need to deselect all items in the listbox.

Something like..
dim Iselected
 For Each Iselected In listboxname.ItemsSelected
          listboxname.Selected(ISelected) = False
 Next  Iselected
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

terraksAuthor Commented:
I haven't edited the code at all.

Whenever I switch the list from Multi Select "None" to "Simple", this is when it goes from records the different values for each record (Multi Select: None) to making all the values the same for all records (Multi Select: Simple).
While it is not multi-select then Access will automatically store the single selected value in the field specified in the controlsource.
But Access will not automatically store multiple selections.  It can only be done in code.  So if all you do is change the multi-select property, and don't write the necessary code, then you effectively disable any meaningful use of the listbox.

terraksAuthor Commented:
I see. Thank you peter.

What code do I need for Access to automatically store multiple selections?
I would strongly advise you not to do so.
If you need mutiple entries then you should be creating another table containing the relevant main table primary key and a single selected value in each record.  

I think you should explain what you are trying to do with this function. It may be more appropriate to use a subform rather than a list box.  
terraksAuthor Commented:
I'm trying to assign multiple languages to one person.

So Person 1 speaks A, B, and C; Person 2 speaks B; Person 3 speaks A and C.

I could go the archaic way and assign each language a field, but I'm also trying to learn how to use tables and or value lists to do this.
In my view, there is no really satisfactory solution to this type of requirement where you just want a small but unquantifiable list of items.  Access 2007/2010 introduces a new feature which provides this functionality, although it adds some complexity to processes which use the list.

Any approach you adopt will have benefits and disadvantages.
The approach you have been looking at is good for selecting the values in a list, but hard work in terms of storing, retrieving and searching or sorting.
What would normally be regarded as the 'correct' approach  is to have a separate table which holds the personid and one  language (or languageid) in each record - so multiple records per person.  This approach is the opposite of the first one in terms of pros and cons.  It is much simpler in searching, sorting, storing and retrieving but harder work in doing the initial selection and maintaining the list, and also requires more work in form design whenever you want to use the list.  In concept, this is the basis for the new feature in A2007/2010, but because MS can also change how Access itself works,  a lot of the setup work is hidden.

Whichever route you take , though, and this applies to the newer version of Access as well, you should start by creating a table of languages.  This will make it easier to add new entries when they arise.
The table should not have an id field as this will just encourage Access to use the id rather than the names and in this situation you don't want that to happen. If you need to list the languages in a particular non-alphabetic sequence, then include a sort sequence field in the record.

I'll try and put together a sample over the weekend, but I'm just going out now for most of today.


terraksAuthor Commented:
This works great! Thank you, peter.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.