?
Solved

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

Posted on 2011-04-27
10
Medium Priority
?
313 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.
0
Comment
Question by:terraks
  • 5
  • 4
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35480407
upload a copy of the db...
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35481796
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
0
 

Author Comment

by:terraks
ID: 35485409
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).
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 77

Expert Comment

by:peter57r
ID: 35485737
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.



0
 

Author Comment

by:terraks
ID: 35485764
I see. Thank you peter.

What code do I need for Access to automatically store multiple selections?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35485861
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.  
0
 

Author Comment

by:terraks
ID: 35494835
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.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35496664
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.





 

0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 35497171
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.


db3.mdb
0
 

Author Comment

by:terraks
ID: 35516201
This works great! Thank you, peter.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question