Form filtering and adding records to subform

[Albathien]

Hi, I am currently trying to create a DB for registering and tracking Trainees signing up for set courses.
This is the base Structure:


I am stuck on the actual registration form:



The idea is that I would type in a date, then the Course Combo would list the courses available on that date. Choosing an item on the list would select the record for that particular event and populate the rest of the fields in the main form. The subform would then show sign-ups for that event (filtered on Event_ID).

I have 2 problems.

The first is that the main form is not filtering the records for the event after I choose one from the drop-down. This was working, but I somehow broke it.

Secondly, I would like to be able to add new records to the sub-table ie. add new trainees to a particular event. However when I try to do this, I get an error "... cannot find a record in the Table 'GRADES' with matching key field(s) 'GradeID'". I suspect that Access is creating the wrong hierarchy with the SQL code, but I cannot work out what the syntax is to create this structure in the form Record Source SQL code, this is what Access creates:

SELECT MAIN.Main_ID,
MAIN.EventID,
MAIN.PersonnelID,
PERSONNEL.Pers_ID,
PERSONNEL.Forename,
PERSONNEL.Surname,
PERSONNEL.Email,
PERSONNEL.GradeID,
GRADES.Grade_ID,
GRADES.GradeName,
PERSONNEL.BaseID,
BASES.Base_ID,
BASES.BaseName,
MAIN.UNA,
MAIN.UNA_Date,
MAIN.DNA

FROM (GRADES
   INNER JOIN (BASES
      INNER JOIN PERSONNEL
      ON BASES.[Base_ID] = PERSONNEL.[BaseID])
   ON GRADES.[Grade_ID] = PERSONNEL.[GradeID])
   INNER JOIN MAIN
   ON PERSONNEL.[Pers_ID] = MAIN.[PersonnelID];

This is the hierarchy for the subform I'm using, which I think it should reflect.




I would appreciate any help I can get on explaining why the main form isn't filtering, and what the correct syntax should in Access SQL for Multiple Links and Nested links.

Thanks in advance

EDIT: Link to the file in my Dropbox https://dl.dropboxusercontent.com/u/...urse_Reg.accdb

[orange]

Do you have a 5-6 line description, in plain English, of WHAT you are trying to do? I do not understand the purpose of MAIN.

Seems you have
-People and Courses.
-Courses can be given in different Sessions and
-Courses can be given at different Locations.

Other facts that may be involved:
- A Person can be "assigned" to take a Course
- The Person may attend or not
- The Person receives a Grade

I always recommend a statement related to Business facts/rules; then
some test data based on those facts; and develop an evolving data model of the facts.
Test the model with the test data; reconcile every anomaly--is it data? is it the model? is there another business rule?
- change as necessary and repeat the tests until you have reconciled every issue.

Now start development of Forms, Queries, Reports etc.

[Albathien]

I appreciate your quick response,

Yes sorry, The purpose of MAIN is to create the link between People and Events (particular courses).
I have done this so that I can keep a historic record of who attended which events.

So:
- An Event is a Course that has been assigned a date, time, venue, trainer, etc. This has a unique ID
- Personnel are people that can be assigned to a particular Event. (Trainees). These have a unique ID
- A person can cancel their sign-up (UNA), the Date when they cancel must be registered.
- A person can not show up to the Event (DNA)
- An Event can be cancelled. A reason and date must be registered.

Here grade is actually Personnel Pay-Grades.

Thanks