We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

terraks
terraks asked
on
Medium Priority
332 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
upload a copy of the db...
CERTIFIED EXPERT

Commented:
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

Author

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).
CERTIFIED EXPERT

Commented:
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.



Author

Commented:
I see. Thank you peter.

What code do I need for Access to automatically store multiple selections?
CERTIFIED EXPERT

Commented:
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.  

Author

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.
CERTIFIED EXPERT

Commented:
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.





 

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This works great! Thank you, peter.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.