Supervisor employee relationship

[Graeme at Raptup]

Hi,
I have a table called Tbl_Person where I have (PK) Person_ID and a field
Supervisor_ID. I am able to establish a supervisor from a combo box on a form
(Using the query builder and creating what I believe is called a self join).
However - I want the combo box to only list persons in the same company.
I have a form (Company) and a subform (Persons) - how do I limit the choice
in the combo box to only those persons that work at the same company?
Excuse me on this - I'm a bit rusty!
I've tried a number of options but they are not working...

 

[Evi]

Is CompanyID (or whatever you call it) in tblPersons? (a person can only
belong to 1 company at a time) or is it in another table containing PersonID
and CompanyID?

I'm assuming the former.
call the combo cboSupe

Create a query based on your PersonsTable with PersonID, PersonName and
CompanyID
Under companyID put the filter

[Forms]![NameOfYourMainForm].[Form].[CompanyID]

(replacing NameOfYourMainForm and CompanyID with the real names, of course)

You will also need to put this code into the On Current Event of the main
form.

Me.[TheNameOfYourSubform].Form.[cboSupe].Requery


(this will ensure that each time you change to a new page in the main form,
the combo is updated

Evi

 

[Armen Stein]

Hi,
I have a table called Tbl_Person where I have (PK) Person_ID and a field
Supervisor_ID. I am able to establish a supervisor from a combo box on a form
(Using the query builder and creating what I believe is called a self join).
However - I want the combo box to only list persons in the same company.
I have a form (Company) and a subform (Persons) - how do I limit the choice
in the combo box to only those persons that work at the same company?
Excuse me on this - I'm a bit rusty!
I've tried a number of options but they are not working...

You can add a Where clause to limit the combobox to only people from a
particular company. This will work only if all the Persons will be in
the same Company, as the Where clause will apply to all the rows in
your continuous subform. But since the main form is Company, this
should be fine.

A simple way to do it is to add the something like this to the
rowsource of the Person combobox:

WHERE Company_ID = Forms![MyCompanyFormName]!Company_ID

Be aware though, that if the first column in the combobox is the
Person's name (as it probably is), then if you change the Person's
company later and come back to this form, it will appear blank
(because the person isn't in that Company anymore, so their name can't
be retrieved).

Armen Stein
Microsoft Access MVP
www.JStreetTech.com