Related record required for ... in forms

[ByteWarrior]

Hi,

I'm having a little problem with making forms.

The situation:
I'm making a database for someone who is giving courses (design, etc).
There are 4 important tables: tblCourse, tblTrainer, tblLocation,
tblParticipants. All those tables have primary keys (autonumbered)

The "course" table is the most important one.
Every course is associated with 1 trainer and 1 location.
A participant is associated with one course.
(all one-to-many relationships).

When I now make an autoform where I can input the courses, one by one,
it contains:
1) dropdown box to chose a trainer
2) dropdown box to chose a location
3) subform based on tblParticipants containing all the participants for
that particular training.

However, I can't add data because Access is complaining ("... a related
record is required for the table tblTrainer" and "... for the table
tblLocation").

I'm guessing it has something to do with referential integrity. If I
don't enforce referential integrity, it works, but the locations aren't
right (if I enter something in the frmCourses and then check tblCourse,
I see another location).

Any ideas on how to solve this one?
BTW. I've already searched w/ Google, but found no solution that
actually helped me with the problem.

 

[Guest]

Suggestions within text below:

Hi,

I'm having a little problem with making forms.

The situation:
I'm making a database for someone who is giving courses (design, etc).
There are 4 important tables: tblCourse, tblTrainer, tblLocation,
tblParticipants. All those tables have primary keys (autonumbered)

OK, so you have four tables:

tbl_Courses
CourseID (PK)
TrainerID (FK)
LocationID (FK)
CourseName

tbl_Trainers
TrainerID (PK)
TrainerName

tbl_Locations
LocationID (PK)
LocationName

tbl_Participants
ParticipantID (PK)
CourseID (FK)
ParticipantName

The "course" table is the most important one.
Every course is associated with 1 trainer and 1 location.
A participant is associated with one course.
(all one-to-many relationships).

When I now make an autoform where I can input the courses, one by one,
it contains:
1) dropdown box to chose a trainer
2) dropdown box to chose a location
3) subform based on tblParticipants containing all the participants for
that particular training.

You then have a form using the fields from the Locations table. This in
turn has two subforms. The first one can be constructed using this:

SELECT Courses.TrainerID, Courses.LocationID, Courses.CourseName,
Trainers.TrainerName, Courses.CourseID
FROM Trainers INNER JOIN Courses ON Trainers.TrainerID = Courses.TrainerID;

and the second one uses the fields from the Participants table.

When you select a location, the courses and trainers that correspond to that
location show up, or you can input to, the first subform. You can then
select a combination of course and trainer in that subform, and add
participants in the second subform.