Adding multiple names to a form

[Dina01]

I was wondering if anyone has ever created an db that is familiar to what I am trying to do. I have form that is unbound, and that has a few combo box, that are alllinked to different table, one of the combox give the choice to add 4 employee names to the form, because there can be up to 4 employee that have the same training.

But my combo is linked to another table called Employe_tbe which looks like this

Employee_tb:
EmployeeName
EmployeeNo --- this is the primary key
EmployeCode

But all the info form the form will be saved into another table named Traning_tb

I save the data by pressing a SAVE button on this form and then I use the additem, but since I have 5 combo box for the employees named: Empl1, Empl2,Empl3, Empl4, Empl5

My table Training_tb
Also has the following fields:

CourseName
CourseID
Date
EmployeeNo
Empl1
Empl2
Empl3
Empl4
Empl5
Code

Both table are linked with the EmployeeNo, but I am confused cause it's not saving into my table named traning_tb

And I need the table named Emplyee_tb to get updated because if I set the code to 1 that means that the employee is already in a traing course, and code 0 means he is avaialble

Thanks

 

[David R]

To take a cue from Pat Hartman, Access doesn't recognize 4 of something. It recognizes 0, 1, or many. Your data is not organized properly which is part of the reason you're running into difficulty.

Let me see if I understand what you want to do correctly: You have an Employees table, and a Courses table, and you want to be able to put up to four employees into any one course, but no employee can be scheduled for more than one course at once. Correct?

There are two ways to do this, based on if you envision your employees EVER being allowed to take more than one course at once. If this may be the case, go ahead and structure your data correctly for that possibility now...

You need one more table that just lists the CourseID and the EmployeeNo of the Employees taking the Course. For example:

tableEmployeeCourses:
Course  Employee
  31      567
  31      578
  31      462
  31      084
  32      553
  32      735
     etc

For this table the Primary Key can be two-part, based on those two fields. This may sound strange because they're the only fields in the table, but this means they will be uniquely indexed: no combination of course and employee can ever be listed twice. (We'll deal with the employees being in one training at a time later)

Now build a subform from your Courses form, and make a continuous subform with a combo box for employee names on it. However you want to avoid putting an employee in two trainings at once, so the row source will be a bit different.
Have you ever designed a Find Unmatched Query with the wizard? The syntax will be rather similar.

SELECT tableEmployees.EmployeeID, tableEmployees.EmployeeName
FROM tableEmployees LEFT JOIN tableEmployeeCourses ON tableEmployees.EmployeeID = tableEmployeeCourses.EmployeeID
WHERE tableEmployeeCourses.EmployeeID Is Null;

is an example. This will show all Employees who are not already registered for Courses. Make sure you put a Me.ComboName.Requery statement in the AfterUpdate event so your list is always up to date.

Now we just have to limit your subform to 4 entrants at a time. I'll defer to greater expertise on this subject; check here for a good answer: http://www.access-programmers.co.uk/ubb/Forum4/HTML/003678.html

This obviates your need for the EmployeeCode field, though you can put a display checkbox on the Employee record that looks for them in the EmployeeCourses table and shows a checkbox, 1 or 0, or whatever.

You CAN do what you're asking with a multi-select listbox where you just go down the list, click on the names you want in the course, and click a 'Enroll' button, but it's a little more difficult. Post back if you're that curious and I'll try to explain how that's done.

Good luck,
David R