26686

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.

Answer1:

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.

<b>Main Form (no subform) - Use ApplyFilter </b>

<ol><li>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).</li> <li>Create a macro or VBA routine for an AfterUpdate or OnClick button event using the command ApplyFilter.</li> </ol>

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

<b>Main Form (with subform) - Use RecordSource</b>

<ol><li>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).</li> <li>Create a VBA routine for an AfterUpdate or OnClick button event to dynamically filter the RecordSource of subform:</li> </ol>

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

Answer2:

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"

Answer3:

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.

Recommend

  • Show loading screen while filter is being applied
  • removeFilter() function not working in IE9
  • How can i make icon and placeholder in same line using material?
  • Debian packages: distinguish “requested install” from “installed to satisfy dependencies”
  • Find where element is selected in source
  • Float range validation using AngularJs
  • Dropdowns and JSON Objects and Binding the selected value
  • Limit select2 selections by group?
  • WCF Service Application returns 404 using WebGet
  • Determining if a string has been entered with a leading single quote
  • Type mismatch: cannot convert from Connection to Connection
  • Drag and Drop for imageview not working
  • Read stdin in chunks in Bash pipe
  • How many View Controllers should I have in my game?
  • Quick Question About Get and Set
  • Find VMID for running instance
  • Changing references to deprecated methods C++
  • How do I formally document a C# Attribute in UML?
  • How to print columns containing value
  • Clarification on min distance on LocationManager.requestLocationUpdates method, min Distance paramet
  • Using variable in a value field in jMeter
  • Linq Objects Group By & Sum
  • Retrieving value from sql ExecuteScalar()
  • output of program is not same as passed argument
  • jQuery tmpl and DataLink beta
  • Akka Routing: Reply's send to router ends up as dead letters
  • Comma separated Values
  • SQL merge duplicate rows and join values that are different
  • KeystoneJS: Relationships in Admin UI not updating
  • Proper way to use connect-multiparty with express.js?
  • Error creating VM instance in Google Compute Engine
  • How to set the response of a form post action to a iframe source?
  • Hits per day in Google Big Query
  • how does django model after text[] in postgresql [duplicate]
  • Turn off referential integrity in Derby? is it possible?
  • Add sale price programmatically to product variations
  • unknown Exception android
  • Checking variable from a different class in C#
  • Unable to use reactive element in my shiny app
  • How do I use LINQ to get all the Items that have a particular SubItem?