Multi Select Listbox

[stefanheller]

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

 

[dcx693]

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

 

[stefanheller]

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!

 

[dcx693]

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
need the value of my list box

 

[stefanheller]

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

 

[Pat Hartman]

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.

 

[stefanheller]

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

 

[Pat Hartman]

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.

 

[stefanheller]

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

 

[Pat Hartman]

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.

 

[stormin_norm]

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??

 

[Pat Hartman]

I believe the multi-select listbox db sample that I posted has that functionality in the print report section.

multi-select listbox

 

[stormin_norm]

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"

I was trying to stay away from DAO in my app, especially when all the Access2000 books suggest ADO.
But-I seem always to use DAO because of ease of use & plenty of sample code. -- Especially with querydef's

Thanks again!

 

[BrotherBook]

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

 

[Pat Hartman]

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.

 

[Teri Bridges]

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.

I have taken this approach as advised and the process seems to be working very well. It took me a minute to wrap my head around all the forms and sub-forms needed, but when faced with having to write VBA, well let's say I am not very good at that. The only issue I have faced, is getting the selected items from the sub-form into a report. I am still researching for a solution.

 

[Gasman]

Seems another member was trying to do the same thing?

Solved - Forms and Reports to Show Parent/Child records

Hi All, As I understand I cannot put a continuous form within another continuous form.....is there anyway around it to display it as per the attached so it shows multiple parent and associated child records in a continuous form or report? In a perfect world I'd like it like this in both a form...

www.access-programmers.co.uk

 

[Teri Bridges]

Seems another member was trying to do the same thing?

Solved - Forms and Reports to Show Parent/Child records

Hi All, As I understand I cannot put a continuous form within another continuous form.....is there anyway around it to display it as per the attached so it shows multiple parent and associated child records in a continuous form or report? In a perfect world I'd like it like this in both a form...

www.access-programmers.co.uk

Yes, I can get the sub-form into the report, but the look and fill does not match. I was trying to get the sub-form data into a list box that would match the look and feel of the other fields in the report. I think, If I want the List box to be filtered by course catalogID, like the sub-form, I will have to write some code. I am just not sure where to go from what I have. If I could limit the list box by catalog ID I think I would have what I want.