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:
And this is how the data would look like in the People_20190102 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:
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:
Press Cancel, as the Combo Box Wizard will not be needed for our purposes:
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):
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‘:
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:
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…
Then, select the Code Builder option and press OK:
You should now see the following screen where you can write your VBA for the button:
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:
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”
- The folder where the CSV files are stored. In my case, the folder is:
- 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:
Now input the date of 20190102 in the combo box, and then click on the button:
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:
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: