Auto populating a form in access

Ok I am writing a database that helps track training records by position.  I have a dbo_positions table, a dbo_trainingcourse table, and a dbo_jobgroup table.  The position table has all of the main positions, the training course tracks the different courses inside a position and the job group marries them together.  I have created a form, that you select an employee and then their primary position, what I want then is to auto populate , the employee table with their primary positions courses that they need to be trained on, BUT then I need to be able to put a date next to the course when they have finished.  I am able to produce the information with a query but I can't tie a date to each course.  Any help would be appreciated.
sharris_glascolAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Do you have a date field in dbo_jobgroup?  Bind the form to the query and update the date when you know the training happened.
sharris_glascolAuthor Commented:
All of the data will should populate to a dbo_emp_Course which will assign a course to that employee and the date completed.  How do I bind the table?
sharris_glascolAuthor Commented:
I want to be able to go to that employee select their position and the dbo_emp_course updates with the courses that belong to that position.  But to do that it will need to run a query correct?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PatHartmanCommented:
You've just added two more tables to the schema.  The first three tables describe the courses, the positions, and the courses required for a position.  Now you have the employees and the courses required for the employee.  Since position changes over time, position needs to be included in the courses required for the employee table.  Otherwise, you will need to figure out what to do when an employee changes position and therefore the course requirements change.  How will you sort out which requirements apply to which position?

When you change position, the AfterUpdate event can run an append query that copies all the rows from the dbo_jobgroup for a particular position and appends them to the courses required for the employee table.
sharris_glascolAuthor Commented:
So in the form once I select the position, I run a append query on the dbo_jobGroup that will update the dbo_emp_train table correct?  I have not ran an append query, so what do I select?
PatHartmanCommented:
Open the query builder.
Select the dbo_JobGroup table
Select the columns you need
Change the query type to Append
Choose dbo_emp_train
The matching column names will fill in the Append To: cell.  You will have to manually type column names if they are different in the two tables.
Add your EmpID as an Append To column.  In the Field cell, add a reference to the form field that holds the employee ID -- Forms!yourform!txtEmpID and that will set the foreign key you need to append the rows to the correct employee.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sharris_glascolAuthor Commented:
Thanks got it to work great!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.