I'm still learning how to express myself properly in Access terms, so forgive me. I should have said that I created a lookup field, for one of the fields in my table, and it contains multiple items.
I hope that's the correct way of stating it.
And now I just want the user to be able to select more than one of these items in the lookup field.
To clarify what others have said, don't create a lookup in your table design. If you want a list to appear in your form then just do it in the combo or list box. With either of these you can provide a list either from another table or just as a list in the properties of the control.
If you want to select multiple items then you can do this with the list box. However, the reasons for using a list box like this are usually quite specific. Like all controls, the list box will only naturally save one selection to your table (assuming it’s bound and A2007 aside). To do multiple selections and have them all be saved using a list box requires a little effort in vba code. That’s not to say it’s difficult and you may still feel that a list box provides you with the user interface you require.
For info on combos & list boxes, take a look here
http://office.microsoft.com/en-us/access/HP051884851033.aspx
For info about grabbing the values from a multi-select listbox, see here for the kind of code to use. In this code the selections are output as a comma-delimited string but you could use elements of it to create records in a table:
http://support.microsoft.com/kb/827423
However, I wonder if what you really want is a one-to-many relationship e.g. Person A has attended courses 1,3 & 4. This is pretty easy to implement and typically you would do this with two tables and a form/subform. One table stores the person information. The second table stores the courses attended by each person. Then you would use a form and subform to show a person and the courses attended by that person. This can be made using drop-downs (combo or listbox) to ensure the user only selects from the list. But essentially for each course, the user would be adding a new record to the courses attended table. Apologies if I’m stating the obvious here.
For info on form/subform, take a look here:
http://fisher.osu.edu/~muhanna_1/837/MSAccess/tutorials/subforms.pdf
and here
http://www.fgcu.edu/SUPPORT/OFFICE2000/ACCESS/
Whatever method you choose, you still need to have a well designed (normalised) database. From what you describe, you need at least two tables (can’t speak for Access2007).
hth
Chris
