Filtered lookup combo box?

[CryLittleSister]

On my form, I have two combo boxes which are populated by a table of employees. One is for project director and the other for project manager.
These are stored as ID numbers but displayed as full names in a dropdown list.

I'm wondering, as we only have 4 directors, can I filter the list so that the Director field only displays these 4 names?

 

[Minty]

Yes - you can make the rowsource of the combo a query (click on the build ... ) then apply what ever criteria you like to your employee list.

So if you have a position field in your employees table, then add that to the query and put "Director" in the criteria, or whatever matches your data.

 

[CryLittleSister]

Thank you

Actually, the directors have changed over the years and I'd like it to only show Directors who are still active as options (which it now does, thanks!). However, when I do this it removes the names from old projects where the Directors are no longer around. Is there a way to keep it so old projects keep their directors but the only options on the dropdown for new projects are the current ones?

 

[Minty]

Yes - there are a number of ways of achieving this, and I'm never sure which is the best.

Here's one way - By default display all employees so existing records are complete, but on entering/editing the combo change the rowsource to current filtered employees only, then after change switch it back again.

You can prevent the code firing by not making the combo box a tab stop, so that a user has to expressly click into it to make the code work.

 

[CryLittleSister]

How do I express that?

 

[Minty]

The easiest way is if you saved two queries - lets call them qryEmpDirectors and qryEmpAll, I'm sure you will see where this is going...

You set the rowsource for the combo to the query qryEmpAll. In the combo box click on the properties box and open the event tab. In the On Got Focus event click the [Event procedure} option and it will open up the code tab. In the VBA Window you now see type the following replacing the red bit with your control name.

Private Sub YourComboBoxControl_GotFocus()

    Me.[COLOR="Red"]YourComboBoxControl[/COLOR].Rowsource = "qryEmpDirectors"

End Sub

Then do the same but select the On lost focus event and type the following

Private Sub YourComboBoxControl_LostFocus()

    Me.[COLOR="Red"]YourComboBoxControl[/COLOR].Rowsource = "qryEmpAll"

End Sub