Results 1 to 8 of 8

Thread: Combo Box Woes

  1. #1
    Join Date
    Jan 2004
    Location
    Houston, TX
    Posts
    29

    Unhappy Unanswered: Combo Box Woes

    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!

    -Mr. Research
    Attached Thumbnails Attached Thumbnails rel.jpg  

  2. #2
    Join Date
    Dec 2003
    Location
    Bristol, England
    Posts
    29
    Hi

    "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?"

    Firstly, I do not quite understand your table design in relation to your explanation. Do you wish to give the user the choice of selecting:
    a person and then showing his department(s) and Role(s)
    a department and then showing the person(s) and role(s) involved therein
    a Role and then showing the person(s) and department(s) involved therein.

    or do you simply want to display the departments and roles of any selected person?

    You are not trying to use the comboboxes for original data entry are you?

    Where do the protocoles come in all this?

    One immediate comment I would make is that I would not expect to see a field for the PeopleID in the tblDepartments table. By doing that you will be duplicating many records in that table.

    Regards,
    taxes

  3. #3
    Join Date
    Jan 2004
    Location
    Houston, TX
    Posts
    29
    I hope this answers your questions:

    1. PeopleID in Departments table:lease disregard this was a mistake.

    2. Ideally I would like the following to happen: When I open the Protocols table there is a subform on that table subInvolved which will allow me to assign people to each protocol (same as a project). The three combo boxes should work like this: First a person will be selected from the People Cbo on the After_Update event, the Department Cbo will be filtered to only those deprtments which are assigned to the selected person in the tblDeptPeople. The user then selects which department they would like. Finally a role will be selected from the Roles Cbo to create a unique combination of Person, Department, and Role, and Protocol (which is automatically assigned based on the Parent/Child Relationship of the Subform).

    My dilemma is to 1.) Limit the people combo box so that a person only appears 1 time, and 2) to populate the Departments Combo with the appropriate departments for the slected person.

    Thanks,

    Mr.Research

    Originally posted by taxes
    Hi

    "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?"

    Firstly, I do not quite understand your table design in relation to your explanation. Do you wish to give the user the choice of selecting:
    a person and then showing his department(s) and Role(s)
    a department and then showing the person(s) and role(s) involved therein
    a Role and then showing the person(s) and department(s) involved therein.

    or do you simply want to display the departments and roles of any selected person?

    You are not trying to use the comboboxes for original data entry are you?

    Where do the protocoles come in all this?

    One immediate comment I would make is that I would not expect to see a field for the PeopleID in the tblDepartments table. By doing that you will be duplicating many records in that table.

    Regards,

  4. #4
    Join Date
    Dec 2003
    Location
    Bristol, England
    Posts
    29
    Hi,

    O.K. we are moving along.

    The structure of your tblPersonsDept needs looking at. How many departments are there and will that number increase in future. If so, by how many? My reason for asking is that you need to keep the number of fields as small as possible (good practice) and if you have many more people than departments (which I would expect to be the case) then I would prefer to see the key field be the personID, with several fields in the same record, 1 for each department. Alternatively, what is the maximum number of departments in which 1 person could work?

    Regards,
    taxes

  5. #5
    Join Date
    Jan 2004
    Location
    Houston, TX
    Posts
    29
    Originally posted by taxes
    Hi,

    O.K. we are moving along.

    The structure of your tblPersonsDept needs looking at. How many departments are there and will that number increase in future. If so, by how many? My reason for asking is that you need to keep the number of fields as small as possible (good practice) and if you have many more people than departments (which I would expect to be the case) then I would prefer to see the key field be the personID, with several fields in the same record, 1 for each department. Alternatively, what is the maximum number of departments in which 1 person could work?

    Regards,
    Initially there will be 20-30 departments growing to an unknown number as time progresses and the internal and external environment changes. Each person has the potential to have many departments up to 10 or so depending on the persons scope of work.

    The purpose of the tblDeptPeople is to unite an individual and a department in a unique relationship. Thus the reason for the multiple field key. (To prevent duplicates.)

  6. #6
    Join Date
    Dec 2003
    Location
    Bristol, England
    Posts
    29
    HI,

    Try this:

    Fill the people combo from tblpeople sorted by name. the combo ValueMember property to be the ID number.

    Construct a query on tblDeptPeople linked to tblDepartments selecting all rows where the PeopleID = the selectedvalue of the people combo and use it to fill the cboDepartments with the department name, the valuemember being the department ID.

    Does that sound OK so far? If so how do you want to deal with the Role situation - will the user then allocate a selected Role or does each person have a predetermined Role or Roles?

    Regards

    Please note amendment made at 01.35 GMT
    Last edited by taxes; 01-12-04 at 21:37.
    taxes

  7. #7
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    "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?"

    hey-

    This might be silly but, couldn't you create a summary query on the cboDepartment field and use that as the recordsource for the list?

  8. #8
    Join Date
    Dec 2003
    Location
    Bristol, England
    Posts
    29
    Hi Moderator,

    Sorry to try to get your attention this way, but my e-mail report seems to have been ignored. The time shown on the forum is incorrect. It is running 1 hour behind GMT. The time now is 01:41 GMT.
    Regards.
    taxes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •