How To Allow A Button To Open The Value List

[DigitalAdrenaline]

Hi Guys, I've created a form with a Combo Box. Instead of Right-Clicking the Combo box to open the Edit Value list, is the there a VBA code or Macro that will allow me to open this on clicking a form button for a user? Thanks.

[rpeare]

If you are going to allow edits to your list, why keep them as a value list? Why not have a table with the options and let your users add items to that list instead. Value lists are fraught with problems and should be used sparingly and on lists that won't change over time (opinion).

[DigitalAdrenaline]

I was thinking about setting up tables. But each Combo Box list only contains several entries each. I was wondering if tables might be overkill. But I see your point.

[rpeare]

You do not need a separate table for each 'list'. What I do is have a table of options (tblOption) that looks something like this

Opt_ID (PK autonumber)
Opt_Group
Opt_Desc
Opt_Sort
Date_Added

and the data looks like

Opt_ID	Opt_Group	Opt_Desc	Opt_Sort	Date_Added
6		GeneralYN		9/9/2014 2:25:01 PM
10	6	Yes	1	9/9/2014 2:28:14 PM
11	6	No	2	9/9/2014 2:28:16 PM

or

Opt_ID	Opt_Group	Opt_Desc	Opt_Sort	Date_Added
27		Race		11/15/2014 2:01:52 PM
31	27	Asian	1	11/15/2014 2:10:47 PM
30	27	Black	2	11/15/2014 2:10:43 PM
29	27	Caucasian	3	11/15/2014 2:10:40 PM
33	27	Multiracial	4	11/15/2014 2:42:37 PM
236	27	Pacific Islander	5	9/15/2015 1:10:16 PM

So you set up your group first, then you can populate it with your 'options' under that group

This allows you to store all your simple options in one table

[DigitalAdrenaline]

Thanks. I follow what you're saying. The Combo Box I've set up contains stock exchanges: Australia, US, UK. But another user may wish to amend the list to include Hong Kong or delete UK. Rather than me create a table of 50 exchanges, of which, most people won't use. That's where I was going with it, simply a short editable list. I did stumble onto finding the Macro for it: EditListItems, I think.

[DigitalAdrenaline]

Speaking of Macros. I have an 'Add' button on my main form to add a new data entry. When the Add form opens, my 'Add entry' button has an OnClick Macro sequence: Add Record, GoToRecord new, but I'm unsure how to refresh the combo box list on my first form so the dropdown list contains my new entry. Any suggestions how I might achieve this? Cheers.

[rpeare]

That's the thing with a value list, unless you save the form, the value list will not change when you close it, you have to force a save with your code along with the value list change. Additionally, if you modify the value list on a single form it does NOT propagate across other forms/reports, it is only useful for the single object (form/report).

[DigitalAdrenaline]

Ahh, ok. That answers a lot rpeare. Then I might just create tables for these combo boxes.