Using combo boxes to filter data on a form ...

A

AP

Hi,

Please help ...

I have a form which I would like to filter data on using combo boxes ...

Field 1 is 'ID', field 2 is 'Department', field 3 is 'Details' and field 4
is 'Issue Closed Date'.

I also have a label on the form which is displayed as either 'Open' or
'Closed' which is determined by the 'Issue Closed Date' field (if issue
closed date is null then it is open else it is closed).

What I would like to do is use a combo on Department so as when a
particular department is selected, it only displays records from this
department and then I would like to have another combo box which carries
open and closed and be able to further filter open and closed records.

Any ideas how to do this please? (Similar to auto filter in Excel).

I hope I have given enough information here. I am relatively new to all
this so please bear with me if I haven't.

Many thanks.

AP.
 
A

Allen Browne

Use unbound combos for the filtering. You will have a command button to
actually apply the filter after the user has selected their criteria. The
Click event procedure of this button will look something like this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboFilterID) Then 'Number field example.
strWhere = strWhere & "([ID] = " & Me.cboFilterID & ") AND "
End If

If Not IsNull(Me.cboFilterDetails) Then 'Text field example
strWhere = strWhere & "([Details] = """ & Me.cboFilterDetails & """)
AND "
End If

If Me.cboFilterHasDate = "Open" Then
strWhere = strWhere &"([Issue Closed Date] Is Null) AND "
ElseIf Me.cboFilterHasDate = "Closed" Then
strWhere = strWhere &"([Issue Closed Date] Is Not Null) AND "
End If

'Etc for any other boxes you wish to use.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub
 
A

AP

Use unbound combos for the filtering. You will have a command button to
actually apply the filter after the user has selected their criteria. The
Click event procedure of this button will look something like this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboFilterID) Then 'Number field example.
strWhere = strWhere & "([ID] = " & Me.cboFilterID & ") AND "
End If

If Not IsNull(Me.cboFilterDetails) Then 'Text field example
strWhere = strWhere & "([Details] = """ & Me.cboFilterDetails & """)
AND "
End If

If Me.cboFilterHasDate = "Open" Then
strWhere = strWhere &"([Issue Closed Date] Is Null) AND "
ElseIf Me.cboFilterHasDate = "Closed" Then
strWhere = strWhere &"([Issue Closed Date] Is Not Null) AND "
End If

'Etc for any other boxes you wish to use.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub
Click to expand...

Excellent. Thanks for that Allen.

Sorry, could you just explain what's happening with this section of code
please:

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If

I have it working but don't understand why this is required.

Many thanks.

AP.
 
A

Allen Browne

To make it easy to concatenate as many as you need into the Where string, we
end each one with " AND ". So in the end, we have to chop off the last 5
characters.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

AP said:
Use unbound combos for the filtering. You will have a command button to
actually apply the filter after the user has selected their criteria. The
Click event procedure of this button will look something like this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboFilterID) Then 'Number field example.
strWhere = strWhere & "([ID] = " & Me.cboFilterID & ") AND "
End If

If Not IsNull(Me.cboFilterDetails) Then 'Text field example
strWhere = strWhere & "([Details] = """ & Me.cboFilterDetails &
""")
AND "
End If

If Me.cboFilterHasDate = "Open" Then
strWhere = strWhere &"([Issue Closed Date] Is Null) AND "
ElseIf Me.cboFilterHasDate = "Closed" Then
strWhere = strWhere &"([Issue Closed Date] Is Not Null) AND "
End If

'Etc for any other boxes you wish to use.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub
Click to expand...

Excellent. Thanks for that Allen.

Sorry, could you just explain what's happening with this section of code
please:

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If

I have it working but don't understand why this is required.

Many thanks.

AP.
Click to expand...
 
A

AP

To make it easy to concatenate as many as you need into the Where string, we
end each one with " AND ". So in the end, we have to chop off the last 5
characters.
Click to expand...

Great stuff. Thanks again Allen.

AP.
 
Top