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

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:

1;January;2;February;3;March;4;April;5;May;6;June;7;July;8;August;9;September;10;October;11;November;12;December

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 & "'"
    FilterThis
End Sub

Private Sub cboYear_AfterUpdate()

    m_Where = m_Where & " AND [Yr] = '" & Me.cboYear & "'"
    FilterThis
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.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!