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.
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.
Add some data to the table, about 5 records should be plenty.
Some points to note:
Create a new form by following the Wizard.
Follow the windows/prompts, choosing the following:
This will create a form with one record per page. Currently it just goes to the first record.
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’.
Add a command button to the form, following the Wizard, following the windows/prompts:
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.
To do this,
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:
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
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.
The final step is to test the code to open a form to display specific record using a combo box and VBA code:
If 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.