Solved

Auto populating a form in access

Posted on 2014-11-04
7
205 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 35

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 35

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 35

Accepted Solution

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

Author Comment

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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

825 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