Multiple selection in a form (1 Viewer)

[ilanray]

Hello
I would like to create a form with multiple selection for example I have a list of sale person and I would like to choose 3-5 salepersons. what is the best way to do it and how do I update the table (any table) . is anyone have an example for that?

Thanks

 

[Pat Hartman]

You didn't say what you are using this selection for and it makes a difference. So:

1. you want to assign more than one sales person to an order or customer
Use a subform. The subform would have a combo that lists the sales people. Use a separate row for each salesperson.

2. you want to produce a report that includes the sales data for the selected salespeople or perhaps send emails to the selected group
Use a multi-select list box. Build an In() expression to use in the where clause of a query.

The difference is in 1 you are permanently saving the selection but in 2 it is a temporary usage.

Here's a sample that shows you how to do #2

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

 

[MajP]

There are lots of ways to build a "checklist". In most cases you need this when you are doing a Many to Many. In most simple one to many you would just create a child subform so that you can add child records. But in a many to many you are assigning a group of records to another group of records.
So most likely you need a "junction table". If for example you are assigning sales persons to an account you would have a junction table

tblAccounts_Salespersons
-- AcountID_FK
-- SalesPersonID_FK

Assume we have account IDs A, B, C, D, E, F and SalespersonIDs of 1,2,3
if 1 and 2 work on A you would have records
A 1
A 2
If 2, 3, 4 work on D then you would have more records
D 2
D 3
D 4

So most examples of a checklist provide some type of interface (subform, multi select list box) to allow you to select choices and write using an append query to the junction table. The tricks are to then be able to go back and edit the selections or delete the selections.

Access unfortunately provides something called a multi value field. It allows you to store multiple values in a single field without creating this junction table. It does this creating hidden system tables. Thus it gets really confusing. If interested here is a checklist interface that is pretty resuseable and has some nice features.

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

 

[MajP]

Here is another method, and probably the easiest to implement. Although all methods will take code. If you add a yes/no field to your sales person table, then you can build a checklist like this.

Making a custom checklist that saves to a table

Good evening all! I have a need to build a checklist form. We have 19 steps that our leads have to do every engagement. Items 1-4 are pre-engagement, 5- 8 are mid-engagement, and 9- 19 are post-engagememt. I need a checklist form that saves checked items for the next time the DB is opened and I...

www.access-programmers.co.uk

 

[LarryE]

What I do is:

1. Create a Yes/No field in the applicable table.
2. Add the field to the forms record source
3. Create a Checkbox control on the form and set its Control Source to the new field

Now you can create queries for reports or filter the form where the checkbox control criteria = True

 

[kentgorrell]

Are you in a multi user environment? ie are you sharing your database with other users?
If not then LarryE's suggestion will suffice.
The only problem with this solution is, if you have multiple users working on the same table. Users' selections can be affected by each other's selections.

A safer solution for multi user applications can be found here
http://www.osel.com.au/forms/record-selecting-in-multi-user-applications/
a fair bit more work but safe and effective.
it uses temp tables in the FE that aren't shared
there is a sample db to download.