Microsoft Access 2016: Drop Down Menu vs. Combo Box

Question

I am trying to create a drop down list in a table in Access 2016 which will allow the user to choose one of the selected options from the drop down list, or, if the user does not see an option they like, add their own option to the list. I thought this is what combo boxes did, but you cannot add them to a table/it is not working for me. Any suggestions? Do combo boxes not function the same way on Access?

Answer 1

Sorry but there is no way to do this in a table. You cannot have a Not In List event in a table.

I'm not sure you understand my answer. I don't see why anyone would want to enter data directly in a table. You have fewer controls of how the data is entered and you can't make data entry easier for the user with a table. That's what forms are for. When someone enters data into a bound form, the table is directly updated. So, in terms of data entry, it would be same as entering data into the table. It is really a bad practice to let users have direct access to tables. It affects the integrity of the data. And it would be a nightmare if audited.

I'm sorry you don't think my answer resolved your issue, but the only reason it didn't was because you are trying to do something that can't and, more importantly, shouldn't be done.

Answer 2

.................... it is for company use, and many individuals of the company would want to update the table as they see fit.

In a multi-user environment the database should firstly be split into front and back ends, the latter containing the tables only, the former containing the forms, reports, queries etc which make up the user interface, along with links to each back end table.  The built in database splitter wizard can d this for you, though it is very simple to do so manually.

Once, split the back end should be placed in a shared location on the server, to which all users have full permissions.  The links in the front end can be refreshed using the built in Linked Table Manager.  A separate copy of the front end should be installed on each user's local machine, or in a 'personal' location on the system, to which only the user and the system administrator has access.

Users should only interreact with the data by means of bound forms in the front end, never directly with a table in raw datasheet view.  Direct access to tables gives no advantage over the use of forms, it merely puts the integrity of the data at risk.  In a bound form you can use a combo box to select from a set of values, and the control's NotInList event procedure can be used to insert a new item not currently represented in the database.  You'll find examples of the use of the NotInList event procedure in NotInList.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates the use of the procedure to insert a new row into the refence4d table transparently, where only the value types into the control needs to be inserted into a single column, or by opening a from in dialogue mode, where, in addition to inserted the NewData value into a column, values need to be inserted into other column(s) in the referenced table.

You might also like to take a look at DatabaseBasics.zip in the same OneDrive folder.  This incudes a contacts form in which new values can be inserted, not just into a single referenced table, but into a set of related tables in a tree-structured hierarchy, in this case that of Countries----<Regions----<Cities.

Answer 3

You need to set yourself up for success.  To do so, Scott, is entirely correct.  You need to create a form for the users to use to work with the data.  They should never have any access directly to the tables.  The database needs to be split and every user provided with their own local copy of the frontend.

Once you start developing form, you'll start to understand why this is done.  You gain the ability to use various controls (buttons, combo boxes, ...).  You also can enforce security, add data validation, ...

I'd urge you to review

http://www.devhut.net/2017/04/09/setting-up-an-ms-access-database/

http://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/

Answer 4

Thank you! However, I wanted to be able to do this in a table because it is for company use, and many individuals of the company would want to update the table as they see fit. Is there any way to do this through a table?

Answer 5

Users should NEVER have direct access to tables. All interaction with data should be done through forms. Comboboxes can be created on forms with the properties you are looking for. You want to use the NotInList property to prompt the user to add what they typed. By searching for NotInList property you can find sample code to use for this.