Although versions of Access after 2007 support multivalue fields and provide some mechanisms for maintaining them, they cause a number of problems both in displaying them and in querying them. They break the principle of relational database design that a single field (column) holds only one datum, and so are inherently difficult to work with in a relational database application like Access. The best way to store multiple items in a single field is not to do it at all. Instead, use multiple records in a related table to represent these items. There are several ways to present these records on a form in a way that users find easy to work with. The obvious way to present multiple related items is to use a subform. An Access subform is designed to display and edit multiple records from a related table (these records being related to the record currently displayed on the main form), and does it with no code at all. However, the subform approach is a bit cumbersome to work with when all you want to do is let the user quickly choose multiple items from a list. For small "pick-lists" I prefer the multiselect list box. However, list boxes in multiselect mode can't be bound directly to the data, so you need to use code to read the related records for each new main record and select the appropriate items in the list box. Then whenever the list box is updated you need to use code to update the set of records in the related table. Here's code from a sample form that represents "family members", with a list box named "lstHobbies" that represents, for each family member, that person's hobbies from the list of all possible hobbies. As you can see, this will involve tables called "tblFamilyMembers" (all family members), "tblHobbies" (all the hobbies that anyone could have), and "tblFamilyMembersHobbies" (each record linking one family member to one hobby). The list box shows all hobbies, with the selected items in the list being the hobbies held by the current family member. code: click in the frame, Select All, then Paste into your code editorAs you see, there's a fair amount of code involved, because we're using the list box to do something it wasn't built to do, but it works quite nicely. Relevance: Microsoft Access Versions: Access 95 to 2010 Categories: VBA, How To, Forms Date: 24 April 2012
MMcCarthy 14,534 Expert Mod 8TB
This is an example of code that uses a multi-select ListBox control. This is an area that seems to cause confusion and difficulty for many of our members.
1 37672
TheSmileyCoder 2,322 Expert Mod 2GB
I just wanted to expand on this, and show how to get the value of a secondary column in a multiselect listbox:
Sign in to post your reply or Sign up for a free account.
sigh - yet again Multi Select List Box Hi - hope someone can help!
I am very new to database design and especially VB code. I am trying to get a mutliselect list box functioning, but after reading a lot of the posts here and trying to knock something together myself, I have FAILED! The database is for a travel firm (a friend of mine). The main form is made up from quite a few different tables (to try and keep it modular) and their recent request was for "a visible list box that you can select one or more of the items on within the main form" The list box was created as a table with a primary key and has a relationship with the main part of the form (which is the "company form") - many to one. This list is the "client product list". 1. I need to be able to select one or multiple items for each company record and this needs to be saved (in a table?) 2. Currently the data will not save or change from record to record if you select option 2,3 and 4 from the list box, ALL the records have these same options selcted (ie highlighted) - so each record must have a unique list of items highlighted (even if they are possibly the same choices as another record). 3. They will then want to search on one or more of the items from the list box and print a mailing list (or email list) that they can then mass email / mail info to. I think I need to create a unique table to store the data in that has been selected from the list box... but what VB code do I need...and would I not also need to run some sort of query to update this table after every record has been changed / edited??? Hope someone understands this/ can help me etc etc etc
Thanks
I think I understand what you need. Unlike with a single-select listbox, you need to have multiple values stored. You cannot store that (obviously) in one field. You need to create a whole other table just to store the selections made in the multiselect listbox. This is a one-to-many type of relationship. You select one company record, then you can make many selections from the listbox. Now, how do you actually record the selections in the table? There are a few ways, the easiest depend on the application being single-user (where you do not have multiple users logged onto the database at once). When you save the record, you can use an update query and save the selected entries to the new table along with the company name or number. All the records that aren't saved were not selected. When you move from record-to-record, you can reload the selections from the new table. Highlighting the items in the listbox that ones that match the ones that were previously saved.
I'm sure you'll have questions...
Yes - that is EXACTLY what I need. I have tried to dissect all sorts of code and ended up in a complete muddle. So - someone said to me I need to use "itemselect" - but how?
where? And the query - I figured I would have to have one - but it seems most people have that written in code - can I do it without?
I suppose the big question is where exactly do I start to untangle the mess I am in!
The first step would be to create that new table holding the selections from the listbox. That should be relatively easy. Next, you've got to get familiar with how to record selections from a multiselect listbox. Yes - you do use the ItemsSelected property. These posts might help you out:
Using MultiSelect Listbox
OK - I am going to review the links you have given me...but it is late here - 1:21 am - so time for me to go to bed before I totally mess up the database.
I will post a reply as to how I get on during MY daytime tomorrow! Many thanks so far -
Stefan
If you are willing to give up the multi-select listbox and replace it with a subform, you can do everything without ANY code. You can make the subform small and visually similar to the listbox.
Thanks for that Pat - but I wasn't too sure how far I could go with subforms...I already have 1 subform in the main form...and then a subform in that subform... Would this be a better solution that multi select list boxes?
Stef
I prefer non-code solutions whenever possible. The less code you write, the less code you need to debug, and the less code you need to maintain. Besides, I've written my million lines of code and don't need any more practice. Using a Multi-Select Listbox to substitute for a subform can be done but requires code in the Form's Before Update event to identify which selected items are adds and also to identify if any previously selected items are no longer selected meaning that they should be deleted. You also need code in the current event to highlight the present selections for the current record. Most people totally forget about the delete problem or ignore it. I have created forms that use multi-select listboxes to create things like attendance records. But these listboxes never need to show existing data. They are only used to add data. In your case since you need to add, delete, and display, I would go with the subform unless the user totally objects to the interface change.
Form's with lots of "expensive" controls such as subforms can get sluggish but it takes a while. I had to convert a database created by a non-professional developer. The db contained one form that had 48 tabs, each with 1 or more subforms. The tabs and forms were made visible or not in the current event of the main form. A coding nightmare and totally unnecessary if the db had been properly designed from the beginning. So, if that form functioned, I wouldn't worry about 3 or 4 subforms.
OK - after looking at all the options, I thik that the sub-form route sounds an easier maintenance. However, I am getting confused with how I would set it up. The User would need to still highlight (in some way) various options from a choice of about 12. Obviously they can choose any of them, all of them or selected items. So, within this "list" which comes from a table called "clientproductlist" I have created the subform. But you cannot select multiple itmes from this list....I must be missing something so obvious! Can you point it out to me? Cheers
Stefan
Use a combo box to present the selections. The user will choose 1 option from a combo, then go to the next row to choose the next option.
Pat- I have a similar issue. I have to pass the choices made to a COMPLEX query (can't do this dynamic) which will then feed a mailmerge. I have read numerous threads and it does not appear that there is an easy way to pass the selection list to a query.
Perhaps a temp table as DCX mentioned in another thread. Although I'm not sure what would happen if two users run this query with a join to temp table. After all the temp table is defined in the query (again complex query, dynamic query not possible)
Any suggestions??
GREAT example Pat! I guess I have to join the CT Access users group now. Very useful example. I will do something similar where I run a delete query and then run the append query with the multi-list box selections. This table will then be part of the query which will be used for the mailmerge.
Nice Mauve color Pat! You will need to add that one to the "colors thread"
But-I seem always to use DAO because of ease of use & plenty of sample code. -- Especially with querydef's
Thanks again!
Pat - You mentioned that there is a way to do this using a subform without any code. Do you have any example code or DB I could look at. I've been able to write the code for the multiple selection listbox to write down to a new table, but I've started to realize how difficult the code is to have it show their selections if they re-enter the form, limit them from adding someone twice, and giving them the ability to remove someone. Thanks,
Mike
Create a subform and bind it to a query of the junction table. Use a combo to let the user select what he wants to add. I am not in my office at the momement so I don't have a sample I can post but if you search here for a many to many sample database that I posted, you will see how it works. As I recall, the sample shows the relationships from each direction. So one main form is based on table A with the subform based on the junction table AB and the combo on the subform selects the table B primary key and the other main form is based on table B with a different subform based on the junction table AB and this time the combo on the subform selects the table A primary key. |
