How to add an "Other" option to a List Box in Access Forms that allows typing a custom entry using VBA Body

Question

I’m creating a form in Microsoft Access with a List Box for users to select their usual food preferences. The list box options are:

1. Meat-based (beef, chicken, pork, fish, seafood)
2. Plant-based (vegetarian, vegan, fruitarian)
3. Flexitarian (combination of meat and plant-based foods)
4. Other When a user selects "Other"

I want a popup (e.g., InputBox) to appear where they can type their custom food preference, and that entry should then be added to the list and selected automatically.

Here’s the VBA code I’ve tried:

Private Sub Form_Load()
 With Me.ListBox0
 .RowSourceType = "Value List"
 .RowSource = "Meat-based (beef, chicken, pork, fish, seafood);Plant-based (vegetarian, vegan, fruitarian);Flexitarian (combination of meat and plant-based foods);Other"
 .MultiSelect = 0
 End With
End Sub
Private Sub ListBox0_AfterUpdate()
 If Me.ListBox0.Value = "Other" Then
 Dim userInput As String
 userInput = InputBox("Please specify your food preference:", "Other Option")
 If userInput <> "" Then
 Me.ListBox0.AddItem userInput
 Me.ListBox0.Value = userInput
 End If
 End If
End Sub

@Tim Williams, thanks for your comment. We tried the above code and it did not work. There was no "other" field created. There was no error code.

• Tim Williams: What happened when you tried that? You haven't told us what the problem is here.
• Marie: @TimWilliams Thanks for your comment. We tried the above code and it did not work. There was no "other" field created. There was no error code.
• Tim Williams: Working for me, though .MultiSelect = 0 triggers an error since that property is desig-time only learn.microsoft.com/en-us/office/vba/api/…

Answer 1

I’m going to suggest you move the "VBA hard coding" of the choices out to a table.

There are several reasons for this. First, think of buying a word processor, but you THEN need to hire some developers to change the choices inside of word? And same goes for your application (no VBA and hiring of a developer should be required to change some simple list of choices).

Over time, you really don’t want to use VBA code to fill out the choices for the list box, since then over time, you need to use an Access developer and code to change the list of choices.

If you move the choices to a table, then over time, more choices can be added to that list, and no developer + VBA coding would be required (users of the application can add and make changes to the list).

The next big issue? Well, say your code as posted did work, what happens next time the form is loaded and you go back to that record?

Your VBA code loads up the list of choices, but the user typed in a special new category.

Hence, that new "other" text typed in not going to display, since the listbox can ONLY display the lists of choices you have in your VBA.

So, when you move to the next record, or re-load the form to edit? User’s custom choice will not display since the listbox legal choices are from VBA code.

So, for ease of maintains, ease of changing the list, ease of adding to the list, and allowing the form to "know" and "remember" the "other" choice that the user typed in? And show it highlighted/selected when you go back to that record?

This suggests a design in which choices for the listbox comes from a table.

I suggest building a table to hold food preferences like this:

enter image description here

And then simply enter the 3 choices you have like this:

enter image description here

Then, on the form, beside the listbox, you can have some button say like this:

enter image description here

And now we don't need any VBA code in the forms load event to setup and load up choices to the list box (you can specify the table in the listbox).

And our code behind the button "new Pref" can simply pop up a dialog, user can enter a new choice, and then we add that new choice to the table, re-load the listbox choices and then highlight (choose) the text that user just added to our list.

So, our button code (to add new preference) can be:

Private Sub cmdAddPref_Click()
 Dim strNewPref As String
 Dim strSQL As String
 
 strNewPref = InputBox("", "Enter new Food Preference")
 
 If strNewPref = "" Then
 Exit Sub
 End If
 
 ' get here, we add new preference to table
 strSQL = "INSERT INTO tblFoodPreferences (FoodPreference) VALUES('" + strNewPref & "')"
 CurrentDb.Execute strSQL, dbFailOnError
 
 ' Now trigger re-load of listbox
 lstFoodPref.Requery
 
 lstFoodPref = strNewPref ' highlight/setup our new choice
 
End Sub

And the result is thus this:

enter image description here

• Marie: Thanks for your brillant suggestion to use a table instead of the "VBA coding" to create an options field. Is it possible to select multiple responses in a table (e.g flexitarian and plant-based)? Also, we should input the code in the 'VBA editor', correct? We really appreciate your help!
• Marie: I sent this comment last week but it did not post
• Albert D. Kallal: the code a shown would go behind the button we added to the given form. As for allowing "mulltiple" choices? Yes, you can allow multiple choices, but the new problem/trick then becomes where and how one is going to save/store the multiple choices. This can be somewhat of a challenge, since normally the listbox is bound to one column in the database, so how can we stuff multiple choices into into one field/column in that one record? Often then, this requires a sub form, or new child table. I suppose one could also consider a multi-value column.