Multi Select Option Group alternative

[detrie]

I [think] I know option groups cannot be multi-select.
I need a way to select 1 to 10 values from another table
My plan is to use a multi-select listbox with the Record Source pointing to the other table and concatenating those record IDs into a bound field on my form.
So a record on my form whose listbox had Table and Stool selected would have 1,3 in the bound field. From there I would use a case statement to do the rest of what I need.

ID ITEM
1 Table
2 Chair
3 Stool

Is there a better way to handle this? If not, what would the code to update bound field with the listbox selections the look like?

 

[CJ_London]

I would use a separate table because you are describing a many to many relationship.

The table would consist of two fields a FK to your main record and FK to your other table.

You can then use code to parse this table (rather than a field in your main table) for selected items to repopulate the listbox as required. The benefit is that any queries will be much easier to write further down the line.

Alternatively the table could be populated via a subform on your main form.

The subform only needs a control for the FK from your sub table and you would use a combo control to select the appropriate item.

To this point, there is no coding required.

If selections from the other table are mutually exclusive you have two choices.

For no code, in the table design create a multifield index for the two FK's and make it 'no duplicates allowed'.

Otherwise (or as well if you don't want users to try and select the same item twice) you can have some code in the combo on enter event to exclude items already selected and then in the combo on exit event to restore the full selection. You use these two events because otherwise the items already selected will appear to disappear - they won't, it's just the rowsource will apply to all records so any already selected won't appear.