Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Combo Box Filtering

Author  Topic  MAmox Starting Member 1 Post Posted - 2004-01-12 : 09:44:37 Ok here is the story. I have a Db that has four tables that are M-M relationships. The data must follow the following rules: Tables: tblProtocols; tblPeople; tblDepartments; tblRoles Rules: 1. Each protocol has many people; Likewise each person can have many protocols 2. Each department has many people and each person can have many department. <<This is where it gets sticky.. 3. Each [person:department] can have many protocols; and each protocol can have many [person,department]'s. 4. The qualifier that discretly identifies each [Protocol:PersonDepartment] combination is the role. In short, each person:department can be on a single protocol multiple times as long as the role is different. Example: Protocol 1; John Smith; Department A; Director Protocol 1 ; John Smith, Department A; Budget Contact My problem is that functionally the people are entered into the database (frmPeople) and assigned a department (frmPeople.subDepartment) before the protocol is entered. I would like to have frmProtocol.subInvolved to be able to build this unique f-way relation. Ideally the user will have a continuous form frmPeople.subInvolved with 3 combo boxes, cboPeople, cboRole, cboDepartment. The user will select a person from cboPeople, the the appropriate role from cboRole, and then the cboDepartment will be filtered to allow only those departments which are valid for the person in cboPerson. To accomplish this I created a table tblDeptPerson which holds the people:department combinations, however since each person can have more than one entry when the user selects the cboPerson those people with more than one department have an entry for each department. QUESTION: How do I limit the cboDepartment list to only those department which correspond to the person selected in cboPerson AND have each person listed ONLY 1 time in the cboPerson control? If you have any suggestions please help. Thanks in advance!