How To Create A Drop Down List In Access 2013

How to Add <All> or <N/A> Options to Combo box

A combo box is well known in the MS Access form. The combo box can be a Lookup field from a table. You can see more details at: How to Create Lookup Field in Table for MS Access here. The combo box can also be a stand-alone combo box on the Access form. In this How To, I will show to add the <All> or <N/A> option on the list of combo box. The selection option: <All> or <N/A> is not listed on the table, but we can add on top of the list in the combo box.

I have created a from below and added the combo box for selecting the state. I will not show how to create the combo box on this How To. If you need to learn more on how to create the combo box, you can click on this link: Working with Combo Box.

The purpose of this form below is to select the state from the combo box dropdown list then display all customers who live in the selected state. All states are listed in the combo box. This combo box has a Row Source from the Customers table, so there are duplicated states on the dropdown list as shown below, but not "<All>" option. We want to add <All> option on this combo box.

Under the Data tab of the property sheet of the combo box, you will see the Row Source in the picture below:

                Row Source  = SELECT [Customers].[ID], [Customers].[State] FROM Customers ORDER BY [State];                  Row Source Type     = Table/Query
                                  Limit To List       = Yes
                                  Allow Value List Edits = No

Actually, there are two fields/columns in the combo box. However, it displays only the State column (2^nd column). The first column (ID) is hidden.

On the picture below, you will see the Column Count =2 and Column Widths =0";1". So the 1^st column (ID) has the width =0 and does not display on the combo box.

I will demonstrate the steps below on how to add the "<All>" option to the combo box.

There are 2 methods to add "<All>" option into the combo box.

Method 1: Using Union Query

Step #1 Open Query Builder

Open form in the form design view
Open the property sheet of the combo box
Open the Row Source of the combo box like the picture below

Step #2 Update Field/Column for combo box

Delete the 1^st field or column (ID)
Delete the 3^rd field or 3^rd column for sorting Ascending

Click on Crosstab icon then click on Select icon
The Select Query now has the Total and Crosstab rows
The Group By is automatically added on the Total row
Select Ascending under the Sort row

Step #3 Update to Union Query

Right hand click on the Query Builder bar "Add <All> to Combo Box"
Select SQL View

Add "Union Select "<All>" from Customers" on the 2^nd line after FROM Customers

Select View on the top left hand corner to view the result of Union Query
<All> option is now listed on the top of all states on the data sheet view
Save and Close this Query Builder

Step #4 Update the Combo Box Property

Click on the Format property of Combo box
Update Column Count = 1
Update Column Widths = 1" to display only State
Save and close the Form Design View and open the Form View

Now the <All> option displays on top of all states on the combo box. There is also no duplicate state on the list because we set the Group By on the Total row under the Query Builder. See picture below:

Method 2: Using VBA

The 2nd method is to use the VBA code to add the "<All>" option the combo box drop down list. You will need to follow the steps above from Step#1 to 4 except Step#3 for Update Union Query since we are not using the Union Query. We need to create a Sup Procedure and call it under the Form On Load Event.

Sub GetComboBoxList()
Dim strList, strSQL As String

strList = "<All>;"
With cboState
With CurrentDb.OpenRecordset(.RowSource)
Do Until .EOF
strList = strList & !State & ";"
.MoveNext
Loop
End With
.RowSourceType = "Value List"
.RowSource = strList
End With
End Sub

Then call the GetComboBoxList() Sup-Procedure on the Form On Load Event as shown below.

                Private Sub Form_Load()                
                      Call GetComboBoxList                
                End Sub

Now the <All> option displays on top of all states on the combo box. There is also no duplicate state on the list.

Filter Data in Sub-Form by Using VBA for Combo box

Under the design view, open the property of combo box
Select Event tab on the Property Sheet
Select [Event Procedure] on After Update event
Click on (…) to open the VBA window

Enter code in the picture below to filter the sub-form to display the customers who live in the state that is selected from the combo box.

Example of Selecting "CA" on the Combo box state.

Example of Selecting <All> option

All customers will display on the sub-form after selecting the <All> option as shown in the picture below.