Hello everyone.
With the help of some googling, I managed to write a sub for filtering my subform, using 2 list boxes and 1 checkbox, and pressing a button
I use the same code to open a filtered report (pasting it under another button's Click event), and it works fine.
The thing is that it seems more suitable to have a function that I could use when needed, rather that having the same lines of code again and again. Could you help me convert this sub into a working function?
Code:
Private Sub btn_filter_Click()
Dim var As Variant
Dim filtr As String
Dim mdlt As Variant
If Me.list_Shifts.ItemsSelected.Count > 0 Then
For Each var In Me.list_Shifts.ItemsSelected
Dim shft As String
' Get the initial Shift filter:
shft = "Pt_Shift=" & Me.list_Shifts.ItemData(var)
' loop through the next listbox and add that to the shft string
If Me.list_Modality.ItemsSelected.Count > 0 Then
For Each mdlt In Me.list_Modality.ItemsSelected
' ' since this is the "last" filter listbox, we can
' ' add this string to the "master" filter string
If Len(filtr) > 0 Then
filtr = filtr & " OR "
End If
filtr = filtr & shft & " AND Pt_Modality=" & Me.list_Modality.ItemData(mdlt)
' check for the Inactive filter
If Me.chk_Pt_InactiveList.Value = False Then
filtr = filtr & " AND Pt_Inactive=0"
End If
Next mdlt
' If the Modality filter is not applicable, apply the Shift and Inactive filters
Else
If Len(filtr) > 0 Then
filtr = filtr & " OR "
End If
filtr = filtr & shft
If Me.chk_Pt_InactiveList.Value = False Then
filtr = filtr & " AND Pt_Inactive=0"
End If
End If
Next var
' If nothing is selected in the Shift filter, do not use it
' and continue with the next filters
Else
shft = "Pt_Shift Is Null"
If Me.list_Modality.ItemsSelected.Count > 0 Then
For Each mdlt In Me.list_Modality.ItemsSelected
' ' since this is the "last" filter listbox, we can
' ' add this string to the "master" filter string
If Len(filtr) > 0 Then
filtr = filtr & " OR "
End If
filtr = filtr & shft & " AND Pt_Modality=" & Me.list_Modality.ItemData(mdlt)
' check for the Inactive filter
If Me.chk_Pt_InactiveList.Value = False Then
filtr = filtr & " AND Pt_Inactive=0"
End If
Next mdlt
' If the Modality filter is not applicable, apply the Shift and Inactive filters
Else
If Len(filtr) > 0 Then
filtr = filtr & " OR "
End If
filtr = filtr & shft
If Me.chk_Pt_InactiveList.Value = False Then
filtr = filtr & " AND Pt_Inactive=0"
End If
End If
End If
Me.sbfrm_PatientList.Form.filter = filtr
Me.sbfrm_PatientList.Form.FilterOn = True
End Sub
thanx in advance