Combo search and form filtering

D

duncanjx

CURRENTLY
CboLookUp filters form when a Jurisdiction has been selected and Apply
Filter function has been used.
(Works Well) but if Jurisdiction value not selected then CboLookUp is
empty.

The main form is shows “tblAgency Details” where Staff and
Jurisdiction have been previously added.

CboLookUp – Easy search of records where “Jurisdiction” is selected –
Row Source - qryMain Search Jurisdiction
Parameter on the query - [forms]![frmCombinedforms].[cmbFilter1]

cmbFilter2 – Select Staff details and clears Jurisdiction selection
Row Source - tblStaff Details

cmbFilter1 - Selects Jurisdiction and clears Staff selection
Row Source - tblJurisdiction Details

Apply Filter Button – Applies filter where Manager or Jurisdiction was
selected, (CboLookUp shows listing where Jurisdiction has been
selected) (works well)
Code
Private Sub btnApplyFilter_Click()
If Me.cmbFilter1 <> "" And Me.cmbFilter2 = "" Then
Me.Form.Filter = "[Jurisdiction]='" & Me.cmbFilter1 & "'"
Me.Form.FilterOn = True

ElseIf Me.cmbFilter1 = "" And Me.cmbFilter2 <> "" Then
Me.Form.Filter = "[Manager]='" & Me.cmbFilter2 & "'"
Me.Form.FilterOn = True
Else
Me.Form.FilterOn = True
Me.Form.Requery
End If
Me.Refresh
End Sub

Clear Filter Button – Clears filter selection (CboLookUp is empty)

QUESTION
What I would like is to also have CboLookUp to show full listing where
Jurisdiction has not been selected as well to allow selection? When
records are several hundred/ thousands, scrolling would become more of
an effort.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top