filtering microsoft access records based on combo box predefined values

Question

I have a Microsoft Access database. It has a customers table and a date of entry of each customer. I also have a form which includes all customers and their info.

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.

Can this be done? I have been trying to do this for at least 3 days but no luck..

Kind regards.

Answer 1

Essentially, you need a drop down field that applies a filter functionality to filter corresponding records by the month. Do the following depending on if you are using a subform or not.

Main Form (no subform) - Use ApplyFilter

1. Create a combo box either with entered value list selections for all 12 months or with data from a Months table (using hidden or unhidden month number).
2. Create a macro or VBA routine for an AfterUpdate or OnClick button event using the command ApplyFilter.

Macro: ApplyFilter FilterName: (leave blank), Where Condition: ="=[Record Month Field]='" & Forms!MainForm!FilterMonthCombo & "'", Control Name: (leave blank)

VBA: DoCmd.ApplyFilter , "[Record Month Field]='" & Me.FilterMonthCombo & "'"

Main Form (with subform) - Use RecordSource

1. Create a combo box either with entered value list selections for all 12 months or with data from a Months table (using hidden or unhidden month number).
2. Create a VBA routine for an AfterUpdate or OnClick button event to dynamically filter the RecordSource of subform:

VBA: Forms!MainForm!Subform.Form.RecordSource = "SELECT * FROM Records WHERE [Record Month Field]='" & Forms!MainForm!FilterMonthCombo & "'"

Answer 2

Seems to me like a simple thing to do in MS Access. If you are comfortable with VBA, you can write a procedure on "AFTER UPDATE" Event of the Combo box. This procedure should simply update the RecordSource of the FROM to

"SELECT * FROM [the table with the records you want to filter] WHERE [Field with month number] = " & ComboxboxField.Column(0)

SET your Combo box to have two columns, one with the month number and the other with the month's names. When formatting your combo box, choose to hide the first column of data by setting its size to 0"

Answer 3

There are a few steps you need to, First go into the properties for the combo box and change the row source so that is has the number of the month in the first column and the month name in the second. You will need to make a table for this. Next, go into the properties of the combo box and change the column count to 2. Then change the column widths so that the first column is 0. It should look like this - 0"; 2". Now the combo box should list the month name in the drop down, but if you access it programmatically it will give you the number of the month.

That should get you started, from there depending on how you are getting that data you would need either a query that uses Month() to separate it out, or if you are using a child form you would need a field with month there to link off of.

Hope this helps if it's not too late.