Populate a field in a table from another field (selection by dropdown in form)

[veejay]

I'm trying to create Relationship between employees and managers in a form.

Here's what I have so far:

Four main tables:
1- Employees
2- Managers
3- Status
4- Form-table

On my employee table I have a few columns : ID, First Name, Last Name, Full Name, Manager and Status.

I create a form that is linked to the employee table where I can add the information. In there I have two dropdown box where I can select a manager and set the status of the employee (active, project, away, leave).

All of this works great so far.

Now I'm creating a form where i'm reviewing the employees work. The form is link to the "Form-table" table where I collect a bunch of information (date, work number, comments, etc.) as well as the employee name and manager.



I currently have a dropdown on that page to select which employee is being reviewed.

Here's what I'm trying to do, but have no idea how to accomplish it.

I would like to be able to register in employee the name of the employee and in manager the name of the manager, however I would like to only select the name of the employee and have the form auto populate the name of the manager in the correct field.

I tried playing with the combo box and adding the two fields (employee name and manager) but only the ID of the employee is being recorded.

The reason why I want this to auto populates and to record on this new table is that employees/manager change all the time, so I want to be able to change the Relationship in my employee form, however I want the Relationship on my "Form-table" to stay unaltered after the work as being reviewed, even if the employee is working under a new manager.

So in plain language: I want to select the employee name and have the manager name getting recorded at the same time.

I hope this makes sense...

[June7]

Not really seeing need for Managers table.

Managers are employees. They should have records in Employees table. Should also be a field in Employees table to identify the employee's manager.

[veejay]

Our managers won't need to be evaluated in this database so that's why I didn't account for them being employees.

So you would recommend adding a field in my employee table such as "Is Manager" with 0 being no and 1 being yes? How can I then create a dropdown in my form to only display the employees names that are manager (1) ?

[Minty]

No I think what June is suggesting is that you have a field for ManagerID

That stores who the employees managers employee id is - it's self referencing.

EmployeeID	FirstName	LastName	ManagerID	StartDate	FinishDate
1	John	Doe	0	01/01/1980
2	Capt	Kirk	1	20/03/1986
3	Bruce	Wayne	1	20/03/1986
4	Albert	Butler	3	04/05/1995
5	Robin	Sidekick	3	05/05/1995
6	Officer	Spock	2	06/05/1995
7	Lieutenant	Uhura	6	07/05/1995

John Doe is the boss. Everyone else reports to someone above them.

[ArviLaanemets]

My advice is, have something like department also. You need:
Employees table
tEmployees: EmployeeID, FirstName, LastName, ...;
Departments table
tDepartments; DepartmentID, DepartmentName, ...;
You also may use Appointments table
tAppointments: AppointmentID, [AppointmentCode], AppointmentName, ... (where one appointment may be "department head")
or you have in tEmployees a field like IsBoss with values 1 or 0 for every employee.

And you need a couple of tables where you bring all this info together:
tblEmployeeDepartment: EDID, EmployeeID, DepartmentID, ValidFromDate, [IsActive];
Optionally tblEmployeeAppointment: EAID, EmployeeID, AppointmentID, ValidFromDate, [IsActive];
tblDepartmentBoss: DBID, DepartmentID, EmployeeID, ValidFromDate, [IsActive] (where EmployeeID is selected from employees having IsBoss=1 or having AppointmentCode meant for boss).

With this design, when department gets new boss, you add a new entry into tblDepartmentBoss, and it's all - all employees in this department have new boss from date the new boss is valid!

[veejay]

No I think what June is suggesting is that you have a field for ManagerID

That stores who the employees managers employee id is - it's self referencing.

EmployeeID	FirstName	LastName	ManagerID	StartDate	FinishDate
1	John	Doe		01/01/1980
2	Capt	Kirk	1	20/03/1986
3	Bruce	Wayne	1	20/03/1986
4	Albert	Butler	3	04/05/1995
5	Robin	Sidekick	3	05/05/1995
6	Officer	Spock	2	06/05/1995
7	Lieutenant	Uhura	6	07/05/1995

John Doe is the boss. Everyone else reports to someone above them.

Wow i was making my life much harder. This is a very simple way of organizing the information.

Thanks for this i'll change my tables.

[veejay]

So I've updated my table to make it more like your recommandation Minty but now i'd like to be able to grab the ManagerID from a combobox selection where i'm grabing the EmployeeName.

Here's my setup :

In my EmployeeTable I have : EmployeeId, EmployeeName, ManagerID, StartDate and EndDate

This works great.
On my form which is link to ReviewTable I want to use a combo box to select the EmployeeName from the EmployeeTable and then register it in the ReviewTable Under Employee. At the same time this selection happens, I'd like to have a "passive" text box, that will get filled by the ManagerID from the EmployeeTable and this would get recorded in the ReviewTable Under Manager.

Any recommandation? I've been looking for this but alas..

[Minty]

In you combo simply bring the Manager ID in as a hidden column.

On your form you can then reference that in this format in the control source of the unbound text box;

=cmbYourComboName.Column(2)

Column number starts at 0 and I've assumed column 0 would be your EmpID, 1 the Emp Name, and 2 the ManagerID.

To get slightly clever I would probably bring in the managers name by joining the employee table to itself in the combo rowsource, then displaying that instead of the manager ID number.
But I'll let you play with as a learning exercise.

[veejay]

Great!!

I got it working by joining the table to itself and then creating a combobox with a hidden column for the manager.
For displaying/registering the information in the new table using the control source set to =cmbYourcOmbName.Column(2) wouldn't work so I ended up using the On Change - Event Procedure on the EmployeeCombo and this code:

Private Sub ExaminerCombo_Change()
    Me.Manager.Value = Me.EmployeeCombo.Column(1)
End Sub

Thanks for helping!

[Minty]

You are welcome - good luck with your project.