How to Populate Form Fields Based on a Combo Box Selection in Microsoft Access

This post from our guest blogger Hollie Miller:

There are many potential uses for an Access database which automatically populates certain fields based on the selection in a particular field. The following guidelines for populating fields based on a combo box selection apply to Access 2007 and later. It is possible to do this in earlier versions of Access, however the instructions are different.

Create a table for your requirements. For this example we'll assume that you have would like to choose an Employee ID from a list and have Access automatically populate their first and last names.

Once the table is created it will be shown on the left side of the screen under “Tables”, select the one you want and then click on the “Create” tab. You will now need to create a form and usually the easiest way to do this is just to use the “Form Wizard”. If you have already selected the relevant table it will be shown by default in the forms tab, but if not, you can choose it from the drop-down menu under the heading “Tables/Queries”. The wizard will then show all the various fields available to you in this table and for this example, we're going to choose “EmpID”, “FirstName” and “LastName”, click on the right arrow between the two columns to move them across and then click “Next”. You can then change the layout although many people like the default columnar layout, then click “Next”. Then give your form a title, let's say Employee Details. Then hit “Finish”.

You will now be looking at a blank form. At this point in time you can enter data, but everything will need to be entered manually. Go to the “File” tab and under the “View” group, go to “Design View”. Now you will still see the same form but instead of seeing it as it appears to users, you will see its inner workings and be able to change them. Since we want to be able to choose the Employee ID from a list and have it automatically populate the “First Name” and “Last Name” field, we need to change the “Employee ID” field to a combo box. The combo box has to be able to identify the information we want to use to populate the other text boxes, i.e. it needs to include “FirstName” and “LastName”.

Start by deleting the Employee ID field and then enter a combo box from the “Design Tools” group. Click, drag and drop this into place. By default, the combo box has “I want the combo box to get the values from another table or query.” selected, leave this as it is and click “Next”. This will bring up a list of tables, so you can click on the one you want and click “Next”. This will bring up all of the possible values stored in this table, for this example we'll pick “EmpID”, “FirstName” and “LastName”. Move them into the right-hand column as before and click “Next”. You will then be presented with sort options which determine the order in which the data is shown. You can use these or not as you prefer. Then click “Next”. On the next screen, you will see the FirstName and LastName columns, above them, there is a box with a tick in it and beside it is written “Hide key column (recommended). Take the tick out of the box and you'll see the Employee ID. Then click on “Next” and from the next screen choose “EmpID” as it is the name of the combo box. On the next screen choose “Store that value in this field:” and leave it set to “EmpID” and click “Next”. Give your combo box a name, well use “EmpID” for consistency and click “Finish”.

You will now be back in the design view of your form. Your last task is to tell the combo box where to find the information it needs to populate the relevant fields. This means writing some code, so to begin with you will need to give your fields exact names. To do this highlight the field, look over to the right side of your screen where there is a “Property Sheet”, choose the far-right tab “Alt” and at the very top, there is the option “Name” change this to something very specific. In this example, we'll use “cboEmpID”, “txtFirstName” and “txtLastName” respectively.

Then move to the event tab and look for the line “On Change” this means that when there is a change made to the “EmpID” field, Access will take action. When you click on this field, you will see (…) to the far right of it. Click on this and then choose Code Builder.

In the blank space between the two pre-populated lines, enter.

me.txtFirstName.Value=Me.cboEmpID.col(1)

me.txtLastName.Value=Me.cboEmpID.col(2)

In brief, me refers to the form with which you are currently working, FirstName and LastName are the fields you want to populate into columns 1 and 2, based on the Employee ID entered into the combo box. Please note that Microsoft numbering starts at 0 so although the Employee ID is the first item on the form, it is counted as item 0.

Close out of the code builder and you will see the code in the event section of the property sheet. If you now go to the standard form view, you will be able to choose the Employee ID you require and the First Name and Last Name will automatically populate.

Hollie Miller

Hollie provides Microsoft Access training for Acuity Training. In her spare time she loves to go to the gym and dress making.