Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I feel like I'm learning but I keep getting caught by simple things!

    I have an employee table which, among other fields, has the employee's manager name, and cost center.
    I have a contract table on which I have Manager and Cost center. These are historical and need to be on this table since the other table with this information can change over time. I created the Manager name in the table as a combo box with two columns (bound on the first column): Manager Name and Cost Center from the employee table.
    I have a form for the contract, on it I have the two fields: Manager Name and Cost center. The Manager name drop down shows the two columns. I have an "On Change" event procedure for the Manager field which populates the Cost Center from the second column of the drop down ("[Cost Center] = [Manager].Column(1)"). This works fine on another similar set of fields but in this case it isn't working. There are duplicate manager names with different cost centers (each manager can have multiple cost centers). Regardless of which combination of manager and cost center I choose in the drop down only the first one gets used (the cost center gets populated from the first of the manager's entries and the manager drop down highlights the first of the manager's entries if I go back and click the drop down). Any ideas? I tried a lot of different things. Even in the table if I choose one of the the later entries for a manager name, when I click the drop down button again, it points to the first one. I tried adding the primary key of the customer table as the first column for the manager name combo box in the contract table and making the width 0 so I wouldn't see it but that didn't help either. I deleted the fields from the form and added them back after each change I tried and that didn't help either. I'm stumped.


    (There are also duplicates of Manager/cost center right now but I'm not worried about it yet. Originally I used a query to remove duplicates and sort on manager name but I dropped that to eliminate it as a source of my problem. Now the combo box in the contract table points directly to the employee table and I still have the same problem.)

    I appreciate any help you can offer!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I would use a combo box for Manager that displays the list of unique manager names (and no cost centers), and another combo box for Cost Center that displays only the cost centers associated with the selected manager. See" How to Create Synchronized Combo Boxes, or search this forum for "cascading combo boxes".

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Nice solution! Thanks Hans.
    I had trouble getting this to work based on the first link though. If I read this right, the example had the requery run only when the form became current. I created an update event for the manager name combo box and did two things: changed the value of the cost center field to "Choose" and then ran the requery. It seems to be working great!!
    Thanks again for pointing me in the right direction and helping me to learn!

Posting Permissions

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