Method for maintaining listbox possible values

andydtaylor

Active Member
Joined
Feb 15, 2007
Messages
355
Office Version
  1. 2010
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
883
That is what I would do, with perhaps an additonal field for sort order, then sort by that and then Selection_value.?
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
450
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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).
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,059
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,855
Messages
5,627,269
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top