Using a form to update tables connected by relationships 1

[Vyurr]

Hi,
I have some knowledge of Access and have read many articles on relational database design. I have created several tables and have established relationships between them to allow for cascading updates, etc. The difficulty I am having now though, is that my forms are requiring subforms and such to display the data I wish the user to see. This is working well for displaying the data, but is giving me great difficulty updating the data. Here is what I am working with on one particular problem:

Tables

1) Employee Table (Contains general info and unique empID)
2) Employee's TL Table (With the empID and a type of Team Leader as a dual primary key. The reason for this is that an employee can go on an "acting" assignment under another TeamLeader and then later returns to their own "substantive" team)
3) Team Leader Table (Info about the Team Leaders and their Sections, linked to Table 2 by TLID)
4) Manager Table (Linked to Team leader table by TLID, each manager has several team leaders)

What I am trying to accomplish is to have a command button on my general information form that opens a new form that will allow me to change an employee's team leader by first selecting the manager of the section and then selecting the team leader that reports to that manager.

Example:

Joe Employee works for Sally Team Leader whose manager is Bill.

Joe is now going to transfer to Frank's team, whose manager is John.


I apologize for being so wordy, but any help with this would be greatly appreciated.

D

 

[lameid]

I am having a hard time completely following your table structure. The closest to manageable that is similar to what you have is..

Employee

Employee Team Leader Bridge Entity Table
FK to both Employee and Team Leader

TeamLeader
FK to Manager

Manager


If you have that simply updating the team leader would be what you need to do.

My hunch is you have a problem in table structure not how to do it. If necessary, there are ways to deal with bad table structure but you are generally better off fixing it before it becomes even harder to fix. Obviously at some point you through in the towel and start over. I doubt you are anywhere near that point.

 

[Vyurr]

Thanks for the reply.

I'm not completely convinced that the issue is table structure. I'll try to clarify how the tables are structured:

Table 1 - Employee Table
Has a unique employee ID# as well as general information about the employee (Name, phone #, etc)

Table 2 - Manager Table
Our division is divided into 3 sections. Each section has a manager. This table has a mgrName, mgID and the section name.

Table 3 - Team Leader Table
Each section (headed by a manager) has several work units in it. This table has tlname, unitName, tlID (team leader ID), and the mgrID that the team leader reports to.

Table 4 - Employee's TL Table
This table is just a linking table that contains the empID (Employee's ID) and the tlID (Team Leader ID) that the employee reports to. When an employee transfers to a different unit, this is the table that should be updated to reflect the change.

The idea is to change the unit that the employee is working in by changing the tlID of that employee.

I hope this is more clear.

Thanks again,

D

 

[lameid]

On the form you are creating, put 2 comboboxes on it. One for Manager and the other for Team leader.

Have the query that is the rowsource of team leader use the manager as criteria.

On the after update event of the manager combo box requery the teamleader combo box. Your code should look soemthing like the below.

Private Sub cboManager_afterupdate()
    Me!cboTeamLeader.Requery
End Sub

 

[Vyurr]

Success! Thank you for the assistance. Have a star.

D