How to Create a Combo Box in Access

Do you need to create a Combo Box in Access to gather user’s input? If so, in this article I’ll show you how to create a Combo Box in Access. In addition, I’ll go over an example to demonstrate how you can use a Combo Box in a VBA.

So let’s look into a simple example where:

• You decide to import a daily file into Access; and
• The file name that you import keeps changing; and
• You want to add a Combo Box in Access that will allow you to gather user’s input. The user’s input will then be used to import your desired file into Access (based on the parameters that the user inserted in the Combo Box)

For instance, suppose that you work as part of a group. Each person within the group has a unique user name. Let’s say that a user can generate files that contain a client list. When the user generates the list, a file name is created, which consists of the following 3 components:

• UserName
• “ClientList”
• Date

For example, the user Jon Smith generated the following CSV file on 05-Dec-2017:

JonSmith_ClientList_20171205

So how can you use a Combo Box in Access, to import these types of files directly from Access, without changing the file name?

Let’s look at the steps.

Steps to create a Combo Box in Access:

In the following example, you’ll need to create two combo boxes; one for the User Name, and one for the Date.  These two combo boxes will be used to gather user’s input.

Here is how you can create a Combo Box in Access:

(1) First, go to the Create tab

(2) Then, press on the icon From Design on the Access Ribbon

(3) A new empty form will open up. Click on the icon that represents a Combo Box, and then place it anywhere on your form (when you place the Combo Box on the form itself, close the Combo Box Wizard as it will not be needed)

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

Here, the Combo Box name is “Combo6.” This Combo Box name will then be used in the VBA to represent the User Name:

(5) Once you created a Combo Box, repeat the above steps and create a second Combo Box to represent the Date. Don’t forget to note the name of the second Combo Box as well.

Here, the Combo Box name is “Combo8.” This Combo Box name will then be used in the VBA to represent the Date:

Create a Table in Access to Store your Imported Data

Now that you created your two Combo boxes, you’ll need to create a table in Access to store your imported data (i.e., the client data), as well as create a Specification Name for the CSV file to be imported.

The data that will be imported from the CSV file, will then be stored in the Client_List table in Access.

The following is an example of how the client list would look like in the CSV file to be imported into the Client_List table in Access:

VBA to Use a Combo Box in Access to Gather User’s Input

So now that you have your two Combo Boxes, a table and a CSV file to be imported into Access, you are ready to write a VBA, which will use the data inserted in the Combo Boxes to import your desired file into Access, without leaving Access!

Let’s look at the VBA code that you’ll need to use:

Dim UserName As String
Dim DATE1 As String
Dim PATH As String

UserName = Me.Combo6
DATE1 = Me.Combo8
PATH = "C:\Users\Doron E\Desktop\Combo Box\" & UserName & "_ClientList_" & DATE1 & ".csv"


DoCmd.RunSQL "Delete * from Client_list"

On Error GoTo Bad
DoCmd.TransferText acImportDelim, "client_list_specification", "Client_list", PATH, True

Exit Sub

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

Let’s explain the components of the code:

• UserName = Me.Combo6 represents the first Combo Box that is used to gather the User Name
• DATE1 = Me.Combo8 represents the second Combo Box that is used to gather the Date
• PATH is the variable that represents the location where your CSV file (to be imported) is placed.  It consists of 4 parts:
  • The static location which never changes (this location is tailored to my desktop. You’ll need to tailor this portion to your desired location):
    “C:\Users\Doron E\Desktop\Combo Box\”
  • The variable UserName which changes based on the user’s input (when inserted into Combo6)
  •  Client_list is also static and never changes
  • While the variable DATE1 (gathering user’s input when inserted into Combo8), and the extension of “.csv”, are concatenated at the end of the PATH string. They represent the suffix of the file name to be imported  
• DoCmd.TransferText acImportDelim is used when importing delimited CSV files
  • “client_list_specification” represents the specification name you created. If, for example, you created a specification name that is saved as client_list_specification, then you’ll need to put that name within quotations: “client_list_specification”
  • “Client_list” is the name of the Access table where the imported information, from the CSV file, will be stored. If, for instance, the name of your table in Access is Client_list, then you’ll need to put that name within quotations: “Client_list”
  • PATH is the variable that contains the CSV file location and name. Quotations should not be used for variables
  • True should be chosen when the data to be imported contains headings

The following statements are optional, but you may also use them in the VBA code, depending on your needs:

• If you want to delete all the records in the Access table, before you import the new data from the CSV file, you can insert the following portion into the VBA code:

DoCmd.RunSQL “Delete * from Client_list”

• You can also place an error message using a message box. The message box can be used to inform users about issues when importing the data into Access. Potential issues that can occur when importing data into Access are:
  • The file to be imported is not present in the location defined; and/or
  • The file name is not following the structure specified in the VBA; and/or
  • The file is currently opened by another user

This is a screenshot of the VBA code in Access (it is using a button to trigger the VBA within the form itself):

And this is how your form would look like in Access. The parameters inserted in this example, will allow you to import the file name JonSmith_ClientList_20171205 using a click of a button!

You have seen how to create a Combo Box to gather user’s input. While we used an example to import a CSV file, you can modify the VBA to import an Excel file. You may also decide to change the format of your combo box, for example to a Short Date. To see how you can accomplish these tasks, check our article about how to create an Access VBA to import a file with a variable name.