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