test for child records

[DaveH]

Hi,

I have 3 linked tables: Departments - Team Managers - Agents. Agents are
assigned to a Team Manager, Team Managers are assigned to a Department.

The problem I have is that if a Team Manager changes department their
associated agents will not necessarily go with them.
The ideal solution would be to check if a team manager has any related
records in the Agents table when the user attempts to change their department.

e.g. a message appears saying "Joe Blogs currently has 10 agents, do you
wish to assign them to a different manager before linking Joe to a new
Department?"

Any suggestions would be greatly appreciated.

Many thanks

 

[GBA]

first let's be sure your term 'links' does not refer to table relationship
links - as these must be fixed and never dependent on someone changing jobs.

A manager's record contains a field that lists their department. Very
common. They change jobs - you change that department value...simple.

Right now you seem to say that the Agent records have a field that list the
manager - where it seems like you are better off that the agent record have a
field listing the department.

In any case it is extremely simple to query on the department value and pull
up all managers & agents that have that value...so that you can decide who to
change to a new department....

 

[DaveH]

Thanks for your response.

I was indeed referring to table relationships. Each department may contain
up to 10 managers, and as you say, I simply change the value of the
department field should they change jobs.

Now, each manager has 10-15 agents assigned to them, hence having a field in
the agent record specifying which manager they belong to rather than which
department – agents are far more likely to change manager than department.

This is where the issue arises. When I change the value of the department
field in the managers record, I want a simple check or count of any related
records in the agent table (i.e. agents currently assigned to that manager)

If I were to follow your suggestion of listing department rather than
manager in the agent record, how would I identify which manager within the
department is responsible for the agent?

Your help is much appreciated.

 

[Hans Up]

The problem I have is that if a Team Manager changes department their
associated agents will not necessarily go with them.
The ideal solution would be to check if a team manager has any related
records in the Agents table when the user attempts to change their department.

If your Agents table has a manager_id field, and your form has a control
(say txtManager_id) for the manager's unique ID value:

Dim lngAgents As Long
lngAgents = Dcount("agent_id", "Agents", "manager_id =" & _
Me.txtManager_id)

Check Help for the DCount() function in case I didn't get that right.

 

[DaveH]

Thanks very much, exactly what I needed.

If your Agents table has a manager_id field, and your form has a control
(say txtManager_id) for the manager's unique ID value:

Dim lngAgents As Long
lngAgents = Dcount("agent_id", "Agents", "manager_id =" & _
Me.txtManager_id)

Check Help for the DCount() function in case I didn't get that right.