Macro to filter to all records with a like field

[gojets1721]

I have a form of complaints and what I want to do is create a button that utilizes a macro which when pressed, will filter down to all customer complaints for the customer that the user is currently looking at. It would do this based on a unique CustomerID field.

Any idea how to code the macro?

 

[theDBguy]

Hi. Does it have to be a macro? Could you use VBA?

 

[moke123]

I dont use macros but a vba solution is extremely easy.

Assuming you have some sort of control on your form with the CustomerID. The easiest is a combobox with customerID and Customer Name. The CustomerID is the first bound column in the combobox. Put this in the header of a continuos form.

In the button click event you would have

Me.Filter = "CustomerID = " & Me.TheNameOfYourComboBox
Me.FilterOn = true

 

[Gasman]

Use the ApplyFilter macro

 

[moke123]

here's a simple example using the afterupdate of a combobox rather than a button click

 

[gojets1721]

Hi. Does it have to be a macro? Could you use VBA?

No it could definitely be VBA

 

[gojets1721]

here's a simple example using the afterupdate of a combobox rather than a button click

So my form is set up differently. It only shows one complaint at a time with a 'next' and 'previous' button to move around

So I'm really just looking for a button that when pressed will filter down to all complaints based on the customer ID that the user is currently looking at

 

[moke123]

It would help to see your set up.

Simplest way would be to open your form using the where argument of the docmd.openform method.

DoCmd.OpenForm method (Access)

Access VBA reference

docs.microsoft.com

Another way is to use a subform with the master\child links set to customerID.

 

[gojets1721]

Use the ApplyFilter macro

How would you set it up? I tried using that but failed

 

[Gasman]

You put the form control as the criteria in the where field. Use the builder to get the syntax correct. Use macro ShowAllRecords to remove the filter.

 

[gojets1721]

You put the form control as the criteria in the where field. Use the builder to get the syntax correct. Use macro ShowAllRecords to remove the filter.

I attached a pic of the macro. IDK what I'm doing wrong. When I press the button, I don't get an error but it doesn't filter any of the other entries out

 

[Gasman]

Where condition would be

CustomerID = Forms!yourforname!controlname

Again use the builder. I do not think you need the name field, see the help text?

 

[gojets1721]

Where condition would be

CustomerID = Forms!yourforname!controlname

Again use the builder. I do not think you need the name field, see the help text?

That worked!!! Thank you!!