Creat a form for entering training information for many people

[thekruser]

What I have:


tblTraining
-TrainingID (PK)
-Employee# (FK)
-CourseID
-StartDate
-EndDate
-Status
etc, etc

What I am trying to do:
I would like to set up a form (or series of forms if that works better) that will make entering a training roster easier. Ideally, I would like one form to open (form view) in which the user will select the data that applies to all participants (e.g., CourseID, StartDate, EndDate, Status, etc). The user enters the information and hits "Ok" then another form opens (datasheet view) in which the user only has to select the Employee#s that are associated with that training session.

The problem:
I can't get it to work. I am able to create a form for entering one person at a time, but then I have to re-enter all the course data. I have tried making an append query and basing the form off of that, but I cannot figure out how to tie the form to the query. Access won't let me. I know how to make an append query and add [Select Course Name] in the Criteria box, but I am not able to convert the text box that pops up into a combo box. Plus, I am not even sure an append query is what I need.

I really need two forms, one to append the table and one to update it. Any help would be greatly appreciated. Thank you in advance for your time and help. Have a good day!

[ConneXionLost]

If you're only using two tables, "tbl Training" and "tblEmployee", then you will be stuck.

Try removing [Employee#] from "tblTraining", and instead use a third table:

tblEmployeeTraining
- EmployeeTrainingID (PK)
- TrainingID (FK)
- Employee# (FK)
- DateComplete

to record what you're trying to do. With this arrangement, you'll be able to use a subform to add multiple employees to a course.

Cheers,

[thekruser]

Yes, that is the way I have it, I just left out the third table for ease of understanding the concept. I actually have 3 tables that feed tblTraining, which is the same as you are describing.

[HiTechCoach]

... I just left out the third table for ease of understanding the concept. ...

I do believe that made it worse not better.

Database programming is based ion specifics. If we do not know exactly how your database is built we can't help you without lots of extra effort.

If you will post a sample copy of your actual database we will be able to help you a lot quicker.

[thekruser]

While I appreciate the offer to help, due to the proprietary nature of the content, I cannot upload a sample without a major rework…and even then it would not make any sense. Here is a more accurate synopsis:

tblPersonal
-Employee# (PK)
-LName
-FName
-MI
etc

tblCourses
-CourseID (PK)
-Name
-Code
-Location
-Description
etc

tblStatus
-StatusID (PK)
-Status (e.g., Active, Pending, Attending, Cancelled, Completed, Failed, etc)
-Description

tblTraining
-TrainingID (PK)
-Employee# (FK)
-CourseID (FK)
-StatusID (FK)
-StartDate
-EndDate

Table to be updated/appended: tblTraining

Looking at making frmCourseData which will feed CourseID, StatusID, StartDate, and EndDate and frmCourseNomination which will feed Employee#.

Hope this helps. Thank you for your time and effort.

[thekruser]

I tried creating a form that had CourseID, StatusID, StartDate, and EndDate and then adding a subform with Employee#. Didn't work. It allowed me to add all the names, but I would up with one entry in tblTraining that included the course data then several that only had Employee#. I understand why this happened, just need to know how to get the course info to replicate to all the records. Thanks again!

[HiTechCoach]

Yes, that is the way I have it, I just left out the third table for ease of understanding the concept. I actually have 3 tables that feed tblTraining, which is the same as you are describing.

Actually you do NOT have it the way ConneXionLost is suggesting. I also agree with that recommendation.

I think that there is a normalization issue that is causing you so much grief.

What you we are recommending is something like this:

tblPersonal
-PersonalID (PK) - automuberr 
-Employee# - indexed and no dups
-LName
-FName
-MI
etc

[code]
tblCourses
-CourseID (PK)
-CourseName << Be careful of reserved keyword!
-Code
-Location
-CourseDescription << Be careful of reserved keyword!
etc
['code]

[code]
tblTrainingStatus
-TrainingStatusID (PK)
-Status (e.g., Active, Pending, Attending, Cancelled, Completed, Failed, etc)
-TrainingStatusDescription

tblTrainingClass
-TrainingClassID (PK)
-CourseID (FK)
-StartDate
-EndDate

tblTrainingClassAttendees
- EmployeeTrainingID (PK)
- TrainingClassID (FK)
- PersonalID (FK)
- TrainingStatusID (FK)
- DateCompleted

[thekruser]

@HiTechCoach

Thank you for your reply. A few things from my perspective:

-Empl# can't be changed to an autonumber now. Currently, I have 64 tables and twice as many forms. Would be easier to just start over rather than changing that many relationships and code.
-I know about the reserved words. Again, just trying to make it easier to conceptualize. I didn’t figure DrgCrsXLN, DrgCrsXDs would be very easy to understand.
-There is a lot more at play in my db than I can post do due the complexity/confidentiality issues I face. None of the tables/fields I have listed above are the actual names. I am bound to this and can’t deviate.

My problem is that I am an intermediate access guy, which is to say I can build a functional database with little problem, but when it comes to (what I consider to be) advanced functionality I am somewhat at a loss. I do not know VBA and am trying to learn -- which leads us to the here and now.

In my mind, adding the 5th table would only serve me better if I ever had a need to track specific courses and who attended them. For example, assume:

English 101 is taught in fall, spring, and summer semester
My employer requires English 101
I have Employees A, B, C, and X.

From my perspective, all I care about is that Employee X completed English 101 on date Y. I will never need to know if Employees A, B, and C were in the same fall semester with Employee X. That is my understanding of what the 5th table would provide.

Please help me to understand if I am not seeing this correctly. If it is necessary, I will incorporate it. If what I am explaining is not clear enough, please let me know that as well. I might just have to figure it out on my own, as I cannot be any more specific.

My apologies for the ambiguity.