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 form.

This 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.

Step One: Create table to store data for the combo box

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.

PersonalDetailsID	Autonumber
LastName	Text
FirstName	Text

Add some data to the table, about 5 records should be plenty.

Some 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 with.

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

Create a new form by following the Wizard.

Follow 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

This 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.

Create a new form and open it in Design View.

Add 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.

Follow 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.

Add a command button to the form, following the Wizard, following the windows/prompts:

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

Open 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.

Microsoft Access creates a macro to handle this. We are going to convert this macro to VBA code.

Step Six: Convert the Macro to VBA

To do this,

Open the form in Design View
On the Ribbon Select ‘Design’
In the tools section click on ‘Convert Form’s Macros to Visual Basic’
Tick both the boxes as suggested and ‘Convert’

Then….

Open 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 button.

It will look similar to below:

'------------------------------------------------------------

' Command11_Click

'------------------------------------------------------------

Private Sub Command11_Click()

On Error GoTo Command11_Click_Err

Docmd.OpenForm "frmDetails", acNormal, "", "[PersonalDetailsID]=" [&Combo6], , acNormal

Command11_Click_Exit:

Exit Sub

Command11_Click_Err:

MsgBox Error$

Resume Command11_Click_Exit

End Sub

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.

For some reason the Macro conversion, puts the & inside the bracket [&Combo6], it should be

& [Combo6]

Change 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.

DoCmd.OpenForm "frmDetails", acNormal, "", "[PersonalDetailsID]=" & [Combo6], , acNormal