Resetting Filter (Combo Box) to All

[KevinK]

What I have is a form in which I can assign a new pattern to a particular
manufacturer. The form consists of a parent form with 2 filters at the top.
Both of these filters are combo boxes. The first filters on category and the
second filters on manufacturer, based on which category is selected. The
subform contains the pattern information. When a particular category and
manufacturer are selected all the associated patterns will display. The
problem I’m having is after I select a particular category and manufacturer
and all the patterns are displayed. I placed a double-click event in the
manufacturer filter to reset the manufacturer list to all within the selected
category. the code I used is as follows:
Private Sub ManufacturerFilter_DblClick(Cancel As Integer)
‘Double click action to filter on all manufacturer
gMfgID=â€*â€
Me!ManufacturerFilter=â€*â€
Me!PatternAddfsub.Requery
End Sub

The gMfgID is a global I created to help with searches and queries
throughout the program. The code for that is:
Function CurMfgID()
CurMfgID=gMfgID
End Function
I also set the default value in the ManufacturerFilter’s properties to “*â€.
When I double click in the ManufacturerFilter now the PatternAddfsub fields
fill with #Name? or ####.
I have also set relationships between table with referential integrity. Can
this be solved?

 

[Jeanette Cunningham]

Hi Kevin
an easy way is to have a reset button that will set the combo to null, then
requery the subform.

The code below is untested air code that gives you the idea.
You may need to adjust it to suit your form.

Private Sub cmdReset_Click()
Me.NameOfCombo = Null
Me!PatternAddfsub.Requery
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[KevinK]

Hi Jeanette
Thank you for code. I have revised it to fit the program as such:

Private Sub cmdResetFilter_Click()
'Resets the Manufacturer Filter
gMfgID = Forms!PatternAddfrm!ManufacturerFilter.Column(0)
Me.ManufacturerFilter = Null
Me!PatternAddfsub.Requery
Repaint
End Sub

The only problem is I need to click the button twice to reset the
PatternAddfsub. I’m not sure why it doesn’t with the first click.

Kevin

 

[Jeanette Cunningham]

I suggest that you have a look at a great search form sample on Allen
Browne's website.
If you follow his sample on how he builds the form filter and clears the
filter, I think it will make it easier for you.
http://allenbrowne.com/ser-62.html


I would also remove the default setting in the manufacturer filter - that
might be causing the need for clicking reset twice.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia