Validation Control - Comparing data entered on a form to a table

[Ration]

Hello,

I have a form that has 3 fields:

Agent
Supervisor
Manager

These 3 fields are on the form and users select each entry from a combo box (drop-down). Each of the fields has it's own table. The colums in the tables are:

tbl_Agent
Agent_ID (Auto_Number)
Agent_Name (Text)
Sup_ID (Number)(Based off of tbl_Supervisor)
Mgr_ID (Number)(Based off of tbl_Manager)

tbl_Supervisor
Sup_ID (Auto-Number)
Name (Text)
Mgr_ID (Number)(Based off of tbl_Manager)

tbl_Manager
Mgr_ID (Auto_Number)
Name (Text)

Each Manager has more than 1 Supervisor and each Supervisor has more than 1 Agent but each agent has only 1 Supervisor and 1 Manager. I need to validate the Manager and Supervisor fields on my form so that when my users enter an Agent from the drop-down, it will return an error if the user selects a different Supervisor or Manager than the ones that have been assigned to the Agent. Any ideas?

 

[gol4]

lots of other options but I would suggest you limit their choices based on their selections see faq
faq702-4289

and there other faqs as well