Unanswered: Form/Subform for multiple tables and relationships
It seems like I have almost figured out the hard part but now I can not prepare the forms and subforms properly.
Here is the brief info about my databse and things I need to do.
This is Employee Training Records Database.
Employees (EmpID as PK)
Courses (CouID as PK)
CourseYear (YearID as PK)
CourseDocs (DocID as PK)
Junction (JunctionID as PK and EmpID, CouID, YearID and DocumentID as FK)
One record or row which is a pdf file (DocID) in CourseDocs table can have one and only one matching record to a combined key from any combination of EmpID, CouID and YearID. All three are required input but DocID is optional because in most cases, the employee might have finished particular course for particualr year but might not have received card/Document (Pdf file in database)
So, the combined table would look like this.
EmpID ----- CouID ------- YearID ------- DocID(will not be duplicated)
1 -------- 1 ---------- 1 ------- 1
John Smith -- Fall Protection -- 2007 ---- Docs/123_C00_D01.pdf
John Smith may have two pdf files means two cards for Fall Protection course and for same year 2007.
What I need to do: (I have created some forms already in the attached file but may be that is not working out)
I need to create a simple Form where I can assign each Employee its relevant Courses, Course Years for each courses and Course Documents for each Course for each Course Year.
I understand that Access does not allow you to create a new record for any table and at the same time assign/link/attach the same record to another table as a foreign key. I may be wrong.
In my Case, I have managed to link EmpID, CouID and YearID in the "Junction" table and created a form as well to create new record in "Junction" table for a new combination of EmpID, CouID and YearID using comoboxes. This form can also edit/modify existing combinations of these three parameters. Provided that you already have entered all info in relevant tables (i.e Employees, Courses, CourseYear).
I am not sure which method would be easy as far as data entry and data editing is concerned.
But the nature of requirement is such that Documents will be added last. First Employee, Courses completed and in which year the course was completed. This info is readily available and then it is just the matter of attaching pdf with all other relevant data like issue date and etc.
Note the funny #suffix and duplicated path of pdf in the "Junction" table under the DocumentID field/column. I have no clue how did that happen.
Please anybody spend your precious time to give me a clue to resolve this issue. Please let me know if any further clarifications which I might have missed to mention.