Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hide Items in Combo Drop-Down (2003 SP2)

    I use a combo control to select a person's name from a staff list. The record ID for that person in the staff table is the bound field for the control (the bound column width is set to zero so only the name appears in the drop-down list). If a person leaves the company, I flag that person in the staff table as hidden/inactive. The source query for the combo control is set up to exclude hidden staff (i.e., it lists only current staff). This works fine. However, when I move to a (historical) record where that inactive person is associated with the record, the combo control appears blank (since the inactive person is not in the recordsource for the control). Is there a way to show these names when they are the current value for the combo control, but not have them show up in the drop-down list?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    Make sure that the name of the employee is part of the record source of the table.
    Place a text box bound to the name field on top of the combo box, making it fit the text box part exactly (i.e. don't cover the dropdown arrow).

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    An alternative is to change the rowsource of the combobox in your form's Current event based on whether or not it is a new record. For new records, you only want Active Staff in the rowsource. For existing records, you want everyone.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    I don't think this would work if this is a continuous form. (it may not be a continuous form in this case, but it is often continuous forms where I have this issue to deal with)

    If you go to a new record, then all the names in the previous rows would disappear.
    Regards
    John



  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    In a continuous form, you'd have to use 2 combo boxes, laying one on top of the other. The top combobox would have all Staff, the bottom one (which comes to the top when it gets the focus, would only have current staff.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    Hans/Mark/John -

    Thanks for the tips. Very sneaky. I have this situation for both single forms and continuous forms. I'll try Hans' overlaid text box scheme first, although I'm wondering if this approach allows typing into the combo box to select an item or whether you have to use the drop down arrow. Also, there are a few cases where I allow new values to be typed in and, if confirmed, the new value is added to the list. I'll have to see how this works with these "ghost" controls.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    I usually add one extra thing.

    In the row source of the combo box, I add an "or" condition that includes the current record. So if the key was "personID" then the criteria would be:

    [Active]= true or [Personid]=" & [personId]

    Then I requery the combo in the oncurrent event. This means that if you click the drop down in any old non-current record, the name is still there in the list.
    Regards
    John



  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    John,

    This technique works nicely for me -- for single forms (for continuous forms, as noted in. It has the advantage that an additional control doesn't need to be created (Hans' solution). I'll try Hans' text control or Mark's 2nd combo box solution for my continuous forms situations.

    Thanks.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    Mark,

    Maybe this is a no-brainer, but how do you force the bottom one to get the focus (so the drop down shows only current staff) when the user clicks on the combo box to make a selection? Seems like the top one (with the full list) would get the focus. This is what happens for me when I test this technique.

    Thanks.

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    The top box has its TabStop property set to False, so you can't tab into it. The only way to get to it is to click it, so on its OnEnter event you need code that will then shift focus to the other combo box.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    Thanks. Easy enough!

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Hide Items in Combo Drop-Down (2003 SP2)

    I use this Or condition in addition to the Text box on continuous forms.
    Regards
    John



Posting Permissions

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