Open a form to
display specific record using a combo box and VBA code
How to open a form to display specific record using
a combo box and VBA code, will be the topic of this lesson. We will do
this by adding a combo box and a command button to a form. This command button will open a new form displaying
specific data. The data displayed will depend on the value selected from the
Combo Box. In the first instance, this
will be automatically done by Microsoft Access as a Macro. We will then convert this Macro to VBA code
and adding some additional code to just display a specific record on the second
lesson assumes some knowledge of Microsoft Access. For example, it is expected
that you know how to create a table, a form etc., how to open these in design
view add a button combo box etc. If you are not familiar with these elements,
then I suggest you read my introduction lesson to Microsoft Access.
The first step to open a form to display specific
record using a combo box and VBA code is to create
a new table and add the following fields.
some data to the table, about 5 records should be plenty.
points to note:
- I do not use spaces in my field names.
This makes life a lot easier for me when writing code. You can have spaces in your field names, but
if you do you will have to include your field names in square brackets [ ] every time you include it in your code.
- I also use Capital letters to identify the beginning of new words. Other coders
may use _ i.e. PersonalDetailsID would be personal_details_id. There are also
other naming conventions, use whatever naming convention you feel comfortable
- The ID on the end of my field name i.e. PersonalDetailsID identifies this field
as the Primary Key for my table. Once
again this is just my personal preference.
Step Two: Create a form to display a single record
a new form by following the Wizard.
the windows/prompts, choosing the following:
- The table should already be selected, choose to have all the fields by moving
them across to the right-hand side > Next
- Choose the columnar format > Next
- Change the form name to frmDetails > Finish
will create a form with one record per page.
Currently it just goes to the first record.
Step Three: Add an unbound combo box to the form.
a new form and open it in Design View.
an unbound combo box to the form. In a few steps time we will use, the
selection of this combo box to open a form to display a specific record.
the Wizard, selecting ‘I want the combo box to get the values from a table or
query’, linking it to the table you have just created, adding all the fields
and hiding the ‘Key Column’.
Step Four: Add a Command Button to the form.
a command button to the form, following the Wizard, following the
- Choose Form Operations and to open a new form > Next
- Choose frmDetails > Next
- Choose ‘Open the form and find specific data to display’ > Next
- Select the Combo Box you previously created and select the PersonDetailsID
field, then click on ‘< - >’
- Continue with the windows/prompts updating the names as you wish.
Step Five: Test the Form
the form, and test the above, by selecting a name in the combo box and pressing
the command button, the new form should open displaying the data selected.
Access creates a macro to handle this.
We are going to convert this macro to VBA code.
Step Six: Convert the Macro to VBA
- Open the form in Design View
- On the Ribbon Select ‘Design’
- In the tools section click on ‘Convert Form’s Macros to Visual
- Tick both the boxes as suggested and ‘Convert’
the VBE window by right clicking on the Command button (in Design View) and
choosing Build Event. The VBE window
will open with the cursor at the beginning of the Subprocedure for the Command
will look similar to below:
Error GoTo Command11_Click_Err
"frmDetails", acNormal, "",
"[PersonalDetailsID]=" [&Combo6], , acNormal
Note: The line of code is in Red yours most likely
is also. This means there is an error in
the code. If you try to do what you did
before, i.e. selecting from the Combo Box and clicking on the Command Button, you
will get an error.
some reason the Macro conversion, puts the
& inside the bracket [&Combo6], it should be
the code to reflect this. Note: The name
of your Combo Box maybe different to mine, depending on what you called it, or
what the default Microsoft Access name it was given.
"frmDetails", acNormal, "",
"[PersonalDetailsID]=" & [Combo6], , acNormal
Open a form to display specific
record using a combo box and VBA code
The final step is to test the code to open a form to display specific record using a combo box and VBA code:
- Close the VBA code window by clicking on the outer most X.
- Open the form, choose a name from the combo box and click the command button.
- Check to see if it all works.
you just want a simple command button, stick with the Macro, but if you need to
do anything more complicated, then this is a handy step to know.