Store Subform Data in a Microsoft Access Main Form Relationship
Question details
The user wants to associate subform records with a student without repeatedly storing the student's name.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A main form displays a pupil, while a related form or subform stores records associated with that pupil.
- Observed behavior
- Related records should store the pupil's identifier and display the student's name when needed.
Confirm that the Pupils table has a unique PupID primary key and that the related Data table has a compatible PupID foreign-key field. If Pupils.PupID is an AutoNumber, use a Number field with the Long Integer field size for Data.PupID.
Link the main form and subform using PupID
Use this method when the main form displays a pupil and the subform holds that pupil's related records.
Open Database Tools > Relationships, add the Pupils and Data tables, and drag Pupils.PupID to Data.PupID. Select Enforce Referential Integrity and click Create.
Open the main form in Design View and use its Property Sheet to set its Record Source to Pupils. Open the subform in Design View and set its Record Source to Data or a query based on Data.
In the main form's Design View, select the subform control and open its Property Sheet. Set Link Master Fields to PupID and Link Child Fields to PupID.
Open the main form in Form View, select a pupil, and add a record in the subform. Save the record and check the Data table: its PupID should match the pupil shown on the main form.
Use a combo box to select a student by name
Use a combo box when entering a related record on a form where the pupil is not already determined by a linked main form.
Work with Office documents alongside your Access database
WPS Office is a free, lightweight alternative for everyday documents, spreadsheets, and presentations. Its familiar interface and Microsoft Office format compatibility make it easy to move those files between suites. It does not replace or repair an Access database.
Frequently Asked Questions
How does Access fill PupID in a new subform record?
When the subform control's Link Master Fields and Link Child Fields are both set to PupID, Access uses the current main-form value for new related records.
Why does the combo box show a name but store a number?
The combo box's Bound Column property determines which column is saved. Set Bound Column to 1 for PupID and hide that column with Column Widths while displaying the name in the second column.
Do I need to store the student's name in the Data table?
No. Store PupID as the foreign key and use a query that joins Data.PupID to Pupils.PupID when you need to display the student's name.
Can I display the selected name in a separate text box?
Yes. Add an unbound text box and set its Control Source to =cboPupID.Column(1), assuming the combo box is named cboPupID and the name is in its second column.
