How to create a data entry form that puts data in two tables

Question

I have Student and Course Tables

Student
--------
ID - Primary Key - AutoNumber
Name - Text
Age - Number

Courses
-------
CID - Foreign Key
CourseTitle - Text

ID in Student table is linked to CID in Courses

Question

I want to create a simple Data Entry form that collects a Students Name, Age and Course Title. The Name and Age should go in Student table however, the Course Title should go in Course table.

How do I do this? I know how to create a data entry form that puts data in one table but don't know how to put it in two tables while maintaining the relationship. I would really appreciate a screenshot.

Solution

Assuming that each student can have 0 to many courses, you should create a subform on your data entry form with the courses for each student listed.

It sounds like you are doing this using the Access Forms designer (as opposed to VBA code):

1. Create a main form named frmStudents.

2. Set the datasource for this form to your students table, and then add the fields you want displayed (You can drag these from the "available fields) menu.

3. Create another form named sfmCourses and set the datasource to your courses table. Set the default view for this form to either continuaous forms or Datasheet. While in design view you will still see a standard forms designer view. Drag and drop the fields you want available onto the form and arrange to suit.

4. Add a Subform container to frmStudents and set the SourceObject property to sfmCourses. Then use the LinkMasterFields property to establish the relationship between the two.

Hope that helps.