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.
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.
Rey Obrero (Capricorn1)
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(ISele
Next Iselected
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(ISele
Next Iselected
ASKER
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).
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.
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.
ASKER
I see. Thank you peter.
What code do I need for Access to automatically store multiple selections?
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.
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.
ASKER
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This works great! Thank you, peter.