Auto populating a form in access

Posted on 2014-11-04
Last Modified: 2014-11-05
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.
Question by:sharris_glascol
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 37

Expert Comment

ID: 40421656
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.

Author Comment

ID: 40421666
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?

Author Comment

ID: 40421703
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?
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 37

Expert Comment

ID: 40421803
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.

Author Comment

ID: 40421832
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?
LVL 37

Accepted Solution

PatHartman earned 500 total points
ID: 40422095
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.

Author Comment

ID: 40424037
Thanks got it to work great!!!

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question