Solved Force the Combobox lookup on a form to only show the records that are allowed by the filter (1 Viewer)

[kengooch]

I have a form "fStaff" that is populated by a query "qStaff" with over 3000 records. The user can click a button "bCITC" which sets a filter to only show the records for employees who work in CITC. Once they click that filter button on the form to isolate the records to a subset. However, the Combobox still shows all the names! How do I tell the combo box to only show the records that the filter sets?

'This is the code that sets the filter to only look at the CITC Staff
Private Sub bCITC_Click()
    CITC = "[tStfDept] = 8"
    Me.Filter = CITC
    Me.FilterOn = True
    DoCmd.SetOrderBy "tStfLstNm ASC, tStfFstNm ASC"
    DoCmd.GoToControl "luStaff"
End Sub

'This is the code that does the record lookup on the form.
Private Sub vLUName_AfterUpdate()
' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[vStfFullNm] = '" & Me![vLUName] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    DoCmd.GoToControl "vLUName"
    Me![vLUName] = Null

End Sub

 

[cheekybuddha]

What is the RowSource of your combobox? is it just "qStaff"?

You can add code to your bCITC_Click() event like:

Private Sub bCITC_Click()
    CITC = "[tStfDept] = 8"
    Me.Filter = CITC
    Me.FilterOn = True
'   Filter the combo   
    Me.YourSearchCombo.RowSource = "SELECT * FROM qStaff WHERE " & CITC
    DoCmd.SetOrderBy "tStfLstNm ASC, tStfFstNm ASC"
    DoCmd.GoToControl "luStaff"
End Sub

If that doesn't work, post the SQL of qStaff

 

[kengooch]

What is the RowSource of your combobox? is it just "qStaff"?

You can add code to your bCITC_Click() event like:

Private Sub bCITC_Click()
    CITC = "[tStfDept] = 8"
    Me.Filter = CITC
    Me.FilterOn = True
'   Filter the combo  
    Me.YourSearchCombo.RowSource = "SELECT * FROM qStaff WHERE " & CITC
    DoCmd.SetOrderBy "tStfLstNm ASC, tStfFstNm ASC"
    DoCmd.GoToControl "luStaff"
End Sub

If that doesn't work, post the SQL of qStaff

Here is the line of code in the record source:
SELECT qStaff.vStfFullNm FROM qStaff ORDER BY qStaff.vStfFullNm;

 

[cheekybuddha]

Hi Kenneth,

I'm guessing the combo is vLUName.

So adjust what I wrote above to:

Private Sub bCITC_Click()
    CITC = "[tStfDept] = 8"
    Me.Filter = CITC
    Me.FilterOn = True
'   Filter the combo   
    Me.vLUName.RowSource = "SELECT vStfFullNm FROM qStaff WHERE " & CITC & " ORDER BY qStaff.vStfFullNm;"
    DoCmd.SetOrderBy "tStfLstNm ASC, tStfFstNm ASC"
    DoCmd.GoToControl "luStaff"
End Sub

 

[kengooch]

Here is the line of code in the record source:
SELECT qStaff.vStfFullNm FROM qStaff ORDER BY qStaff.vStfFullNm;

 

[kengooch]

Perfect!! This worked EXACTLY like what I needed to do!! Thanks so very much!

 

[cheekybuddha]

Great! Glad it worked for you

Do you have something similar to remove the CITC filter?

 

[kengooch]

Great! Glad it worked for you

Do you have something similar to remove the CITC filter?

Good Point!! I just realized that on the Show All button I needed to reverse the filter. Can I just replace the CITC with and * to mean all, or is there a simpler command?

 

[kengooch]

Good Point!! I just realized that on the Show All button I needed to reverse the filter. Can I just replace the CITC with and * to mean all, or is there a simpler command?

My * idea didn't work!

 

[bastanu]

You need to use the Like operator with * instead of equal.

 

[kengooch]

You need to use the Like operator with * instead of equal.

You need to use the Like operator with * instead of equal.

I tried it in several places with several methods but can't get it to work. Where do I place it?

 

[kengooch]

You need to use the Like operator with * instead of equal.

I tried it in several places with several methods but can't get it to work. Where do I place it?

 

[bastanu]

You simply replace the original line with something like this:
CITC = "[tStfDept] Like '*'"

Showing your full code usually is the best way to get targeted help .

Cheers,

 

[kengooch]

You simply replace the original line with something like this:
CITC = "[tStfDept] Like '*'"

Showing your full code usually is the best way to get targeted help .

Cheers,

I didn't put the single quotes around the asterick!! Thanks so much!

 

[bastanu]

You're very welcome!

 

[cheekybuddha]

Cheers, Vlad for stepping in! Have only just seen this.

 

[kengooch]

Thanks everyone! It's working great for me now!