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 & "'" 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.