input record info from another table

[nerdychick23]

Is there a way to get Access to input information based on another table?


This is what I want
I have an Employees table. In that Employees table, each Employee is assigned a Foreman and Project Manager.
We are tracking when employees complete training and I want to capture who their Foreman and PM were when they took the training (it changes periodically).
So if you create a record in the training table for employee 12345, it should automatically add that their Foreman is John Smith and Their PM Jake XYZ

[Micron]

You want to keep historical data, yes? Then perhaps...

tblEmployee (which also contains the supervisor and PM because they are employees) needs a supporting tblEmplSuper table with fields for employee id (not their name) and the employee id of the supervisor and likely a date field for when this association began. The form you use for training could be pulling in the super associated with the training at any given point in time. This could be a combo where the row source gets the super ID based on the Max of Date for that employee (if you're using a date field). That supervisor ID gets stored in your training table(s).

[davegri]

Somewhat different approach from Micron's MTM concept. I assume a new record will be created for each employee's training effort, so that's a one to many relationship.

Here's the file nerdy-davegri-v01.zip

And the form