CODEWITHSUNDEEP
ms-access
moeido
moeido

Reputation: 63

filtering microsoft access records based on combo box predefined values

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.

Upvotes: 4

Views: 70289

Answers (4)

Mina Garas
Mina Garas

Reputation: 1

in case you will use baradosa solution you must first dim strsql as string then make strsql = "SELECT * FROM [the table with the records you want to filter] WHERE [Field with month number] = " & ComboxboxField.Column(0)"

Upvotes: -1

baradosa
baradosa

Reputation: 44

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"

Upvotes: 1

Parfait
Parfait

Reputation: 107767

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

Upvotes: 7

Lance
Lance

Reputation: 1

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.

Upvotes: 0

Related Questions