I have a continuous form in access with 3 combo boxes in the header to filter the data. Currently I have a fairly lengthy nested IF to to check how many combo boxes are being used and cover all 8 permeatations. With 3 boxes it's not too bad but I may add a 4th or even 5th in which case it is going to get ridiculous.
Is there an easier way to set up combo box filters for continuous forms?
Current Code
Private Sub filters()
Dim fstr As String, rgS As String, piS As String, hcS As String
rgS = "research_group_id = " & Me.fRG
piS = "pi_id = " & Me.fPI
hcS = "healthcat_id = " & Me.fHC
If IsNull(Me.fRG) Then
If IsNull(Me.fPI) Then
If IsNull(Me.fHC) Then
' Do Nothing
Else
fstr = hcS
Call filton(Me.Name, fstr)
End If
Else
If IsNull(Me.fHC) Then
fstr = piS
Call filton(Me.Name, fstr)
Else
fstr = piS & " AND " & hcS
Call filton(Me.Name, fstr)
End If
End If
Else
If IsNull(Me.fPI) Then
If IsNull(Me.fHC) Then
fstr = rgS
Call filton(Me.Name, fstr)
Else
fstr = rgS & " AND " & hcS
Call filton(Me.Name, fstr)
End If
Else
If IsNull(Me.fHC) Then
fstr = rgS & " AND " & piS
Call filton(Me.Name, fstr)
Else
fstr = rgS & " AND " & piS & " AND " & hcS
Call filton(Me.Name, fstr)
End If
End If
End If
End Sub
The above code is fired after update of each combo box.
Public Function filton(frmname As String, fstr As String)
With Forms(frmname)
.FilterOn = False
.Filter = fstr
.FilterOn = True
End With
End Function