checked list box

Question

Hi

I am new to Access and i am in the process of creating a database for some of our users. I have designed a form where the user name, first and last name etc are inputted.
Some of these users work for more than one department which means I need some type of listbox control that allows them to select more than one department when they enter their contact info.

In VB Studios 2005, this control is called a CHECKED LISTBOX. I have looked everywhere in access but cant seem to find it or anything closely similar.

The option of a listbox or combobox is not feasible here as these only allow the user to select one option only when inputting their details. Any help with this is much appreciated.

Answer 1

Use an ordinary list box with the "Multi Select" property set to "Simple".

The user can then click multiple list items, all of which stay selected until they un-select them again.

Answer 2

You may wish to consider a subform, as this will save you the trouble of updating a table with the data from a list box. A subform can use a department-person junction table with person id as the linked child field and master field. A combobox bound to department id will allow the user to select departments.

Answer 3

If you are really set on having checkboxes, Stephen Lebans has a sample database that uses the built-in Access listbox with checkboxes (from one of the Access wizards).

Answer 4

There is another choice:

If you are using Access 2007, you can declare your field as a lookup field accepting multiple values. When you bind the list box to that field, you will get checkboxes.

It's easy to use them.
You'll also find more information is available from the MS Office website.

However, note:

• This is only possible in the new Access 2007 database format and you will not be able to save your database to the older MDB format.

• Multiple value fields are not compatible with most other databases, meaning that if you decide one day to upsize to SQL Server, you'll have to redesign your tables to use a junction table as Remou mentioned.

• These multi-value fields are difficult to use from VBA: the value they return is in fact another recordset that you must iterate through to get all the values.

Having said that, if you just want something simple and you're not planning on manipulating the database from code, then it can be a practical option, albeit not a very compatible or future-proof one.

I'm not using them, but I think others should be able make their own decision knowing what is available to them.