Filtering a Form through VBA

Procedure for Preset Filters:

Private Sub CMDNullSalaries_Click()

Dim strNull As String

strNull = "(([Salary Query Form].[HR Salary] Is Null))"

Me.Form.Filter = strNull

Me.FilterOn = True

End Sub

Explanation:

Form “Salary Query Form” will display only the Record of whose “HR Salary” field contains nothing.

Procedure for filters passed from a Combo List:

Private Sub CMDFilter_Click()

On Error GoTo Err_CMDFilter_Click

Dim vFilter As String

vFilter = Forms![Salary Query Form]![DeptFilterCombo]

Me.Filter = "DeptID = '" & vFilter & "'"

Me.FilterOn = True

Exit_CMDFilter_Click:

Exit Sub

Err_CMDFilter_Click:

MsgBox Err.Description

Resume Exit_CMDFilter_Click

End Sub

Explanation:

1. vFilter variable is declared.

2. vFilter variable takes the value from Forms![Salary Query Form]![DeptFilterCombo]

3. DeptID Field will then be filtered with the value from vFilter variable.

NOTE: If the variable contains string, then vFilter should be enclosed by single quote.

"DeptID = '" & vFilter & "'"

4. Declare Me.FilterOn as True. This is to activate filter.