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.

Advertisements

7 thoughts on “Auto filling or auto populating MS Access fields in a form

      • Sir i post my question in stack Overflow they block my Q.While you solved it very simply.And helped me to work on my project.Sir the Sample database is very helpful.If I have any problem in my project I post it here.
        thanks.

  1. Hi,

    Thanks so much for your clear guidance.

    Now, I have another problem though.. After successfully getting auto fill fields in the FORM, this auto fill data does not updated in the same field in the related TABLE. Could you please advise how to update new data backward to the table automatically?

    Thank you.

    Best regards.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s