Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Auto populating a form in access

Posted on 2014-11-04
7
Medium Priority
?
218 Views
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.
0
Comment
Question by:sharris_glascol
  • 4
  • 3
7 Comments
 
LVL 40

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:sharris_glascol
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?
0
 

Author Comment

by:sharris_glascol
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?
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 40

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:sharris_glascol
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?
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 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.
0
 

Author Comment

by:sharris_glascol
ID: 40424037
Thanks got it to work great!!!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

Implementing simple internal controls in the Microsoft Access application.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

578 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