Hide Items in Combo Drop-Down (2003 SP2)

Topic

New Reply

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.

Reply | Quote

Replies

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).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks. Easy enough!

Reply | Quote