VBA MS Access Form Filter Example

Free MS Access VBA Programming Course

This post is going to show you a ms access form filter sample.

The database I created only has one table called “Categories”.

We are going to have a form called “frmCategory”.

ms_access_form_filter_example_create_form

The form is going to be a “Split Form”, because that seems to be quicker than the “main form / sub form” setup.

The “Datasheet on Bottom” setting is set giving it this effect. Having this format, gives us this effect:

ms_access_form_filter_example_after_split_form

These are the settings for the “Split Form”

ms_access_form_filter_example_settings1

ms_access_form_filter_example_settings2

I reordered my fields so I can filter better (here is how it looks):

ms_access_form_filter_example_field_reorder

Here is my form all together:

ms_access_form_filter_example_after_update

…and here is my code:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Option Compare Database

Private m_Where As String

Private Sub btnReset_Click()
    Me.FilterOn = False
   
    'clear all the variables
    m_Where = ""
    Me.cboDescription = ""
    Me.cboIncomeExpense = ""
    Me.chkTax = 0
       
   
End Sub

Private Sub cboDescription_AfterUpdate()
   

    m_Where = m_Where & " AND Description = '" & Me.cboDescription & "'"
    FilterThis
   
End Sub

Private Sub cboIncomeExpense_AfterUpdate()
   
    m_Where = m_Where & " AND [Income/Expense] = '" & Me.cboIncomeExpense & "'"
    FilterThis
End Sub
Private Sub chkTax_AfterUpdate()
    m_Where = m_Where & " AND [Taxable] = " & Me.chkTax
    FilterThis
End Sub

Sub FilterThis()
   
    'clean the string first
    If Left(m_Where, 4) = " AND" Then
        m_Where = Mid(m_Where, 5)
    End If
   
    Me.Filter = m_Where
    Me.FilterOn = True
   
End Sub

The variable “m_Where” is where the filter string is stored, and has modular level scope, so the value persists while the form is open.

Let me know if you have any questions.

Offsite Related Information:

“Go Fund Me” Page


($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Facebooktwitterredditpinterestlinkedinmailby feather
Tags: