Share this page:

MS Access: Populate subform data automatically (one to many relationship) in Access 2003/XP/2000/97

Question: I have an Access 2003/XP/2000/97 database that is used to record the materials transferred from our workshop to the head office. The material has a particular number assigned (serial number). For each serial number, you can enter 24 items.

I've created a form and a subform. The form contains data like serial number, date, and driver name. While the subform contains details like item number, quantity, description of item, and remarks.

What I want is when I assign a new serial number in the main form, the subform should automatically produce numbers 1 - 24 as item numbers. Any chance for this?

Answer: This is definitely possible to do. We've created a sample Access database that you can download that demonstrates how to populate a subform with data, in this case multiple records, when a master record is created in a form.

Download version in Access 2000

Let's look at the example. Below, we have a form that lists the serial number, date, and driver name.

Microsoft Access

The user will enter the new serial number (and associated data) and tab off of the Driver name field. Under the main form's After_Insert event, there is code that will run to create the 24 item number records that will be associated with the serial number (just entered).

Microsoft Access

As you can see, the 24 items now appear in the subform.

Microsoft Access