Auto filling or auto populating MS Access fields in a form

Download sample database

Required: MS Access 2010

Desktop database:

1. Create the required fields in your main table (tblTable2) similar to the data type of other table (tblTable1), where from you want to extract data for auto filling (text fields, numbers fields, etc). You may also keep the field names similar.

2. In tblTable2 design mood, click the tab ‘Lookup’ and select ‘Combo Box’. Go to ‘Row Source’ and click the three eclipses to switch to query mood. Select tblTable1 and double click the matching field; save and close. Repeat the same procedure for all the other fields.

3. Prepare a form based on tblTable2. Among the auto filling fields, select the main field (e.g., MediaOrganisation). Go to the tab ‘Design’ > ‘Properties’ > Data > open ‘Row Source’ and select all the fields in the query that you want to auto fill. Close and go to ‘Properties’ > ‘Format’ and fill in ‘Column Count’ and ‘Column Widths’ (e.g. Column Count: 3; Column Width: 1”,1”,1”).

4. Go to the tab ‘Event’, ‘After Update’, open ‘code builder’ and type the following code:

Option Compare Database
Private Sub [combo box name with the prefix cbo]_AfterUpdate()
End Sub

Private Sub [main filed name]_AfterUpdate() [this is column 0]
Me.[field 1 of tblTable2] = Me.[ main filed name].Column(1)
Me. [field 2 of tblTable2] = Me. [main filed name].Column(2)
End Sub

Or copy the following code:

Option Compare Database
Private Sub cboMediaOrganisation_AfterUpdate()
End Sub

Private Sub MediaOrganisation_AfterUpdate()
Me.Medium = Me.MediaOrganisation.Column(1)
Me.Language = Me.MediaOrganisation.Column(2)
End Sub

5. Now try the form by selecting a name in the field MediaOrganisation. You will notice that ‘Medium’ and ‘Language’ fields are auto filled.

Web database:

Drag joined query fields in your main table (tblTable2) query and create a form to display them.