Wednesday, May 16, 2012

Access: Auto-Populate Fields in a Form Based on ComboBox Selection

This post outlines how to automatically fill other fields in a form based on a selection made from a ComboBox.

1)  While in Design View, select the ComboBox from which you will use to fill in the rest of the fields.  All this data must exist in the same table for this method to work.

2)  On the Property Sheet, select the Data tab and click the "..." button next to Row Source.  The "Show Table" dialog should appear asking you which table to select from.  Choose the apporiate table.

3)  Drag and drop the column headers that will be used for filling in the data and save the query.

4)  Go to the Format tab in the Property Sheet.  Change the Column Count to the number of columns you selected in step 3.  Change the Column Widths to be 1 for the column you want to be selectable from the drop down and 0 for each column you want hidden.  For example, I want my first row to be the list used in the Combobox, but I listed two additional columns, so my Column Widths will look like this:  1";0";0".

The 0 values are just making the data available to use when we auto-fill later on.

5)  Go to the Event tab and click on the "..." button for "After Update" and select Code Builder.  This will bring up the Microsoft Visual Basic interface.

6)  You shold see a section beginning with "Private Sub COMBOBOX1_AfterUpdate()". Below that and before "End Sub", type the following:


Me.[TEXT BOX] = Me.[COMBOBOX1].Column(1)
 
"TEXT BOX" is the label for the box you wish to fill automatically fill.
"COMBOX1" is the name of the combobox where the selection was made to fill the rest of the boxes.
"Column(1)" indicates which column from the hidden areas of COMBOBOX to fill this box with.  Note that the column counts begins at 0, so above the 1 indicates the second column.
 
To continue, you could add another text box that automatically gets filled by typing the following:
 
Me.[TEXT BOX2] = Me.[COMBOBOX1].Column(2)
 
7)  Go back to Form View and try it out.  I may add some screen shots at a later time, but in the meantime, please post comment with questions for clarity.
 
An advantage of using this method is that it frees up the Control Source field in the Property Sheet so that any data entered and automatically filled can still be stored to a table for other reports.

5 comments:

  1. Thank you for this. I have been doing a lot of fiddling about trying to make this work and thanks to you it now does.

    ReplyDelete
  2. Thank you so much! I have been looking for an easy explanation that did not use a query, as I needed the student's current school as of the date the info was entered. Other solutions mostly used a query stated to set up a query that would have updated the school on records already entered. I appreciate your help.

    ReplyDelete
  3. I am not a coder but thanks to you I was able to make this work even tho I was confused by the "label" designation for the text box. Again, thanx

    ReplyDelete
  4. Just what I needed!!!!! Now I need to figure out how to choose which auto field to choose when there are more that one.

    ReplyDelete