How to Create a Combo Box in Access

Access / November 23, 2019

Do you need to create a Combo Box in Access to gather user’s input?

If so, I’ll review an example with the steps to create a Combo Box in Access.

The Example

Let’s review a simple example where we will import two CSV files into Access:

  • People_20190101
  • People_20190102

As you can see, the date of the file names keeps changing (highlighted in blue).

You can then use a combo box, where you can type the date of the specific file you want to import.

The Data

This is how the data would look like in the People_20190101 file:

CSV File

And this is how the data would look like in the People_20190102 file:

CSV File

Steps to Create a Combo Box in Access

Step 1: Create a Combo Box

To start, you’ll need to create an Access Form where you can place your combo box.

To do so, go to the Create tab, and then press on the From Design icon:

From Design

A new Access Form will now get created.

To add your combo box, click on the icon that represents a Combo Box, and then place it anywhere on the form:

How to Create a Combo Box in Access

Press Cancel, as the Combo Box Wizard will not be needed for our purposes:

Combo Box Wizard

Check the name of the Combo Box you just created. You can do that by selecting the Combo Box, and then checking the name at the top of the Property Sheet (press F4 if you cannot see the Property Sheet).

Here, the Combo Box name is Combo0. This combo box name will then be used in the VBA (as you’ll later see):

How to Create a Combo Box in Access

Step 2: Import the file into Access

Next, import the CSV file into Access.

For our example, you’ll need to import the ‘People_20190101’ file, and then store it in a new table called ‘People‘:

Import Text Wizard

Note that since you are dealing with a CSV file, you’ll also need to create a specification name while importing the file into Access. You may refer to the following guide that explains the full steps to import a CSV file while creating a specification name.

Step 3: Add a button to the Access Form

Now add a button to the Access Form. The button will be used to import the CSV file based on the info typed in the combo box (in our case, based on the date typed in the combo box).

To add the button, click on the button shape, and then place the button on the Form itself:

Create a Combo Box in Access

Close the Command Button Wizard, as it will not be needed.

Step 4: Create the VBA

In order to create the VBA, right-click on the button, and then select Build Event…

Build Event

Then, select the Code Builder option and press OK:

Code Builder

You should now see the following screen where you can write your VBA for the button:

VBA example

Insert the code below in between the lines (you’ll need to modify the PATH variable to the location where the CSV files are stored on your computer):

Dim DATE1 As String
Dim PATH As String

DATE1 = Me.Combo0
PATH = "C:\Users\Ron\Desktop\Test\" & "People_" & DATE1 & ".csv"

On Error GoTo Bad
DoCmd.TransferText acImportDelim, "People_Specification", "People", PATH, True

Exit Sub

Bad:
MsgBox "The File for the date " & DATE1 & " cannot be found in " & PATH & " OR the file is currently opened by another user"

This is how the full code would look like:

How to Create a Combo Box in Access
Let’s review the main components of the code:

  • DATE1 = Me.Combo0 represents the Combo Box that is used to gather the date (in our case, the name of the Combo Box, which was captured in Step-1, is Combo0)
  • PATH is the variable that represents the location where your CSV files (to be imported) are stored. It consists 4 parts:
    • The folder where the CSV files are stored. In my case, the folder is:
      “C:\Users\Ron\Desktop\Test\”
    • The prefix file name “People_” which never changes
    • The DATE1 variable that contains the info that you typed in Combo0
    • The file extension of “.csv”
  • DoCmd.TransferText acImportDelim is used when importing delimited CSV files
    • “People_Specification” represents the specification name you created
    • “People” is the name of the Access table where the imported data will be stored
    • PATH is the variable that contains the location of the CSV files
    • True should be chosen when the data to be imported contains headings

You may also include a message box at the bottom of the code to inform you about issues when importing the files.

Don’t forget to save your Access Form once you’re done.

Step 5: Test the Combo Box

To test the combo box, you’ll need to switch to the Form View.

To do so, click on the View icon, and then select Form View:

Form View

Now input the date of 20190102 in the combo box, and then click on the button:

How to Create a Combo Box in Access

The People_20190102 CSV file will now get imported into the ‘People’ table. So now the People table will contain the data from both 20190101 and 20190102:

Combo Box

Optionally, you may also wish to include the following delete statement in the VBA code in order to clean the ‘People‘ table each time before you import your data:

DoCmd.RunSQL "Delete * from People"

So your full VBA with the delete statement would look like this:

VBA with delete statement