Method for maintaining listbox possible values

[andydtaylor]

Hi,

I'm usually an Excel person and not so much an Access person so please forgive my stupid question:

I plan to build a database which in various forms rolling up into various tables I want to control selection choices a user can make. Some of these list might be 3 options long, some might be 5, and i know of at least one that is 10. But what is the best way to makes these selection choices maintainable by a user? The scenario would be a user has just encountered a new type of thing not preciously reflected in a drop-down choice. Certainly until this database gets established.

Is the below approach the right way to manage this, or should I be thinking of something else?

Would I have a tblUserStatic
Field 1: Listbox_Name
Field 2: Selection group description
Field 3: Selection_Value

I could then query this and sort A-Z or groupby count when populating a listbox in a form?

Another form could link to this table for the purpose of adding/removing items

Thanks,

Andrew

 

[welshgasman]

That is what I would do, with perhaps an additonal field for sort order, then sort by that and then Selection_value.?

 

[JonXL]

It's a good approach. A couple of thoughts, though:

1. Since users can't change list boxes, the selections for this field should be hard-coded so they don't have to be separately typed in.
2. Have a separate table holding the possible group values (or hard-coding those, too, if they don't change).

 

[Legacy 134911]

If I understand what you are after, this ability for users to edit lists is baked in.

See:
https://docs.microsoft.com/en-us/office/vba/api/access.listbox.listitemseditform
and/or
https://docs.microsoft.com/en-us/office/vba/api/access.combobox.listitemseditform

 

[Micron]

there is also the Not In List event for a combo box, which doesn't require a form for editing list items. However, it can be problematic if not done correctly, and requires additional code if more than one value needs to be added (as in the case where a combo box list has multiple fields and each one is required at the time of inserting new data).