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 36

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?
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 36

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 36

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.

733 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