I’m needing to limit a combo box in a subform by a value in that subform. Having no success. The Combo is populated by my table “Supervisors” which has two fields - [ClientID] and [SupervisorNames]. (A client has multiple supervisors.) The subform includes a [ClientID] and contains the combo box to select the appropriate Supervisor. (There will be several records in this subform for each item in the Parent form, each with a different Supervisor). I need to limit to Combo to only the Supervisors associated with that [ClientID]. I’ve tried several criteria statements in the query, but to no avail. Any assistance is appreciated!!!
Form "Clients" collects Client data with a subform to list Supervisors employed by that client. Subform is based on the "Supervisor" table listing "Clients" table Primary Key plus the Supervisor name. A client will have multiple supervisors.
But that did not answer my question! If a Client can have multiple Supervisors then you need a way to define which Supervisor(s) have been selected for that Client. i.e. a check box or something. What is your criteria for that?
As it turns out, NOT RESPOLVED!! To clarify, a client employees multiple supervisors. I'll create a JobOrder for a client company (thru "frmJobOrders").Then in a subform, ("subJobAssignments") I assign multiple job assignments to the client company named in the "frmJobOrder" with each job assignment having a different supervisor. My subform combo (based on the table below) now selects only the first supv available for the client company and assigns that name to all assignments..ie Client 26, I can select only ”Ted” – Client 27, only “Cain” - Client 28, only “ Horace” I cannot select the 2nd or 3rd Supv listed in the combo.
SupvID ClientID SupervisorName
1 26 Ted
2 26 Fred
3 26 Ed
4 27 Cain
5 27 Able
6 28 Horace
7 28 Boris
8 28 Norris
Last edited by Dad; 07-02-14 at 13:40.
Reason: further clarification
If I understand correctly you need to create a unique Link to the subform for the Supervisors. You create an unbound text box somewhere in the form footer and use the Primary Key for that form as it's control source. Then on the subform, you link MANUALLY the Primary Key and the text box so that it ONLY show One related record for each client. You could use a combo box for the Supervisor.