Help with Cascading Updates/ Relationship problems

[equipe9]

I have several tables as follows.

Department
DepartmentID(Primary Key)
DepartmentName(Text)
MgrEmployeeNumber(Text)
Foreign Key from Manager Table.

Manager
MgrEmployeeNumber(PrimaryKey)
LastName(Text)
FirstName(Text)

Employee
UserEmployeeNumber(PrimaryKey)
MgrEmployeeNumber(ForeignKey)
DepartmentID(ForeignKey)

Relation 1

A Manager can manage 1 to many Departments

Relation 2

A Manager can manage 1 to many Employees.

Relation 3

A Department can have 1 to Many Employees

Please see attached files for ER Diagram as well as Department Form.


It should also be noted that a Department can be Managed by 1 and only 1 Manager. Currently I have 106 Departments Managed by 99 Managers.

The problem I am having is that whenever I change which manager manages a department via the combo box on my Department form I can't get the corresponding MgrEmployeeNumber(FK) to update in my Employee table.

The new Manager will show up in the Department table but won't make the change in the employee table.

Does this have to do with my database structure or am I missing something else?

Thanks in advance for any help you might be able to offer.

Please see

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=36418

also

 

[equipe9]

Here is the Form

 

[equipe9]

and the ER diagram

 

[equipe9]

Pat thank you for your reply and sorry for not responding sooner,

I went ahead and moved my managers into my employee table but the next problem that comes to mind is now I have a many to many relationship.

A department can have 1 to many employees and
An employee can manage 1 to many departments.

I think I may need an associative entity inbetween employees and departments but I'm not sure if I remember how to do this.

Also, I also tried your suggestion with the self-referencing relationship but when I tried to draw the join line I got a message saying....

"Can't create this relationship and enforce referential integrity."

Do you have any other suggestions?

Thanks,

Mike