Auto-populate dependent table/form based on selection in main table/form.

[B Kava]

I’m building a database to track required training for employees. I have 3 main tables - Employees, Departments, and Courses and 3 junction tables – jnc_Employees_Departments, jnc_Employees_Courses, and jnc_Departments_Courses. The Employees table has a Hire_Date field. The Courses table has a Time_Frame field, which is numeric and represents the number of days within the Hire_Date that an employee should complete the course. The Courses are assigned to employees according to the Department they are a member of. If an employee is a supervisor, then he will be a member of two departments. Once an employee is hired, the clock starts for his/her required training. I will have a form to enter new employee data and a subform that lists the required trainings for each employee (one-to-many relationship of employee to courses). What I would like to happen is – once the Department value is selected in the main form (Employees table) – the required courses automatically get assigned to the employee in the subform (jnc_Employees_Courses). This is where I’m stuck. Once I’ve assigned the Department to an employee, I’d like the jnc_Employees_Courses table to auto-populate from the jnc_Departments_Courses table. I don’t want to have to manually select each course that the employee needs to take. Since my vba coding skill are limited, I’ve been searching the Internet for a solution close to what I need but haven’t found it. I’ve created a few records in the jnc_Employees_Courses table to illustrate what I’m looking for.

Any assistance you can provide is greatly appreciated!

 

[Pat Hartman]

Once I’ve assigned the Department to an employee, I’d like the jnc_Employees_Courses table to auto-populate from the jnc_Departments_Courses table. I

You need to clarify your business rules because you might need a slightly different procedure. The suggested solution should help you to talk to the users to clarify the rules.

You need to create a TempVar or global variable to flag a change to the departmentID. In the Current event, you set the variable to false. Then in the AfterUpdate event of the DepartmentID, you set the variable to true if Me.DepartmentID <> Me.DepartmentID.OldValue & "". The & "" takes care of handling the situation for a new record where the DepartmentID is null. Your business rules may or may not define department as a field that is required to create a record so you may need some variation here. Then in the form's AfterUpdate event, check the variable. If the flag is set, run an append query that selects the courses for a department and appends them to the employee_courses junction table. You will need to include the EmployeeID as a variable to populate the FK for that table. More potential rules. If the course is only ever taken ONCE, then you need to create a compound unique index on employeeID + DepartmentID in the junction table (ask if you don't know how to do this). As long as there is a unique index to prevent duplicates, you can just append the records and any duplicates will simply be discarded. If the business rules allow the course to be taken multiple times, then you need to probably check dates or whatever to determine if you need to append a new course record for the employee or not.

Anyway, the answer is you use an append query but there are "rules" that you need to define/consider.