How To Make A MS Access Filter Form With Combobox Using VBA

Free MS Access VBA Programming Course

Hi, I was working on a filter similar to this query, and I noticed someone may have
some questions on how to do it.

So this post is going to show you how to filter some date records with a combobox, actually 2 comboboxes.

Here’s the jist of the question:

I have a Microsoft Access database. It has a customers table and a date of entry of each customer…

I want to make a combo box (drop down box) that would include months of the year (January, February, March, April, etc.). When the user chooses April from the combo box, only the records which were added in that month will show up in the records table.

So here’s how to make a MS Access filter form with combobox using VBA .

In our example I have 2 tables “Customers”, and “Orders”, a query “qryOrders”, and a filter form “frmOrders”.

This data comes from the Northwind database, but you can do the same just fine with your data.

Here’s an image of the form:

…and here is the design view:

In order to get the look of the form above, I’m using the “Split Form” with the datasheet on the bottom

For the months, I just need 12 of them so I just used a value list, with the row source:


If you do it this way, you will have all your values in 1 list vertically as in the following image:

…So make the column settings to 2, and change the column width like this, and you’ll be set:

After you set this code into place, you’ll have a great working filter!

Option Compare Database

Private m_Where As String

Private Sub btnReset_Click()
    Me.FilterOn = False
    'clear all the variables
    m_Where = ""
    Me.cboMonth = ""
    Me.cboYear = ""

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

Private Sub cboMonth_AfterUpdate()
    m_Where = m_Where & " AND [Mo] = '" & Me.cboMonth & "'"
End Sub

Private Sub cboYear_AfterUpdate()

    m_Where = m_Where & " AND [Yr] = '" & Me.cboYear & "'"
End Sub

Giving you this result:

Here it is in action:

Let me know if you have any questions.

Click here for more information on comboboxes and filter related posts.

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

Tags: , ,