filter frustration

[johnlute]

I have a form with a combobox that filters it:
SELECT tblProfiles.*, tblProfiles.Type AS PKType
FROM tblProfiles INNER JOIN tblProfilesClasses ON
tblProfiles.txtProfileID = tblProfilesClasses.txtProfileID
WHERE (((tblProfiles.Type)=[Forms]![frmPackaging].[Form]!
[cbqryTypes])) OR ((([Forms]![frmPackaging].[Form]![cbqryTypes]) Is
Null));

[cbqryTypes] has an AfterUpdate event that filters the form:
Private Sub cbqrytypes_AfterUpdate()
Me.Filter = ""
Me.cbProfileID.Requery
Forms![frmPackaging].Form.Refresh
Forms![frmPackaging].Form.Requery
End Sub

There's also a "clear" button that clears [cbqryTypes] resulting in
all records being displayed:
Private Sub cmdclrcbqrytypes_Click()
Me.Filter = ""
Me.cbqrytypes = Null
Me.cbProfileID.Requery
Forms![frmPackaging].Form.Refresh
Forms![frmPackaging].Form.Requery
End Sub

These work fine EXCEPT when the form is opened via a double click
event from another form which results in it being filtered by
[txtProfileID] which happens to be the primary key of the record set.
This filter "interferes" with the above AfterUpdate event of
[cbqrytypes] rendering it useless.

I'm exhausted. I've tried LOTS of different ways to clear the
[txtProfileID] filter and just can't get it.

How can I clear the filter so that the AfterUpdate event will work?

Thanks!!!

 

[Rob Parker]

Hi John (again),

Although you're asking about filtering the records in your form, that
doesn't appear to be what you are doing. Instead, you are changing the
records which are being displayed by setting a criterion on the form's
RecordSource (ie. the SELECT ... WHERE ... statement). Filtering is when
the form's recordsource does not change, but you limit the records displayed
by setting a filter (essentially a WHERE clause on the recordset).

The Me.Filter = "" line in cbqrytypes_AfterUpdate will probably not be doing
anything - only way would be if you have set a Filter on the form (perhaps
by a selection from the standard right-click menu, or via a menu or toolbar;
alternatively, you may have other code somewhere which sets a filter), when
it would change the filter string to one which would allow all records to
show. And generally, if you are setting a filter in code, you would follow
the Me.Filter = ... line with a Me.FilterOn = True, to ensure that the
filter will take effect.

I'm also puzzled as to why you are requerying the combobox itself; that's
normally only done when the RecordSource for the combobox changes (usually
via code) and you need the new choices to become available - commonly done
for cascading comboboxes, where the choice in a second box is dependent on
the choice in the first box.

I think that all you should need for your code would be:

Private Sub cbqrytypes_AfterUpdate()
Forms![frmPackaging].Form.Requery
End Sub

which will cause the form to re-execute the SELECT ... statement which is
its RecordSource, and

Private Sub cmdclrcbqrytypes_Click()
Me.cbqrytypes = Null
Forms![frmPackaging].Form.Requery
End Sub

to clear the combobox and (again) re-execute the SELECT ...

The issue of the form appearing to be filtered by [txtProfileID] when opened
in a different fashion indicates that there may be filtering done elsewhere.
Perhaps there's a filter attached to the form in design view, which is
taking effect when it opens. Check that the Filter field in the data tab of
the form's property dialog is empty. If that doesn't work, and you can't
find any code elsewhere which is setting the filter, try:
Me.FilterOn = False
in the form's open event.

HTH,

Rob

I have a form with a combobox that filters it:
SELECT tblProfiles.*, tblProfiles.Type AS PKType
FROM tblProfiles INNER JOIN tblProfilesClasses ON
tblProfiles.txtProfileID = tblProfilesClasses.txtProfileID
WHERE (((tblProfiles.Type)=[Forms]![frmPackaging].[Form]!
[cbqryTypes])) OR ((([Forms]![frmPackaging].[Form]![cbqryTypes]) Is
Null));

[cbqryTypes] has an AfterUpdate event that filters the form:
Private Sub cbqrytypes_AfterUpdate()
Me.Filter = ""
Me.cbProfileID.Requery
Forms![frmPackaging].Form.Refresh
Forms![frmPackaging].Form.Requery
End Sub

There's also a "clear" button that clears [cbqryTypes] resulting in
all records being displayed:
Private Sub cmdclrcbqrytypes_Click()
Me.Filter = ""
Me.cbqrytypes = Null
Me.cbProfileID.Requery
Forms![frmPackaging].Form.Refresh
Forms![frmPackaging].Form.Requery
End Sub

These work fine EXCEPT when the form is opened via a double click
event from another form which results in it being filtered by
[txtProfileID] which happens to be the primary key of the record set.
This filter "interferes" with the above AfterUpdate event of
[cbqrytypes] rendering it useless.

I'm exhausted. I've tried LOTS of different ways to clear the
[txtProfileID] filter and just can't get it.

How can I clear the filter so that the AfterUpdate event will work?

Thanks!!!

 

[johnlute]

Hi, Rob.

Thanks for indulging again!

I think that all you should need for your code would be:

Private Sub cbqrytypes_AfterUpdate()
   Forms![frmPackaging].Form.Requery
End Sub

which will cause the form to re-execute the SELECT ... statement which is
its RecordSource,

I wish it were this easy! I've actually tried that and it doesn't
work. The form doesn't clear at all and the controls all display null.
Granted, it works fine IF and only IF I open the form either directly
or through other methods that do NOT change the record source
criteria...

Ahhh...I think this is the problem. When I open the form from another
form that filters for the Primary Key [txtProfileID] this results in
[cbqrytypes] being NULL. When I make a selection in [cbqrytypes] its
AfterUpdate event doesn't properly fire because...why...I don't know
but now that I've read through your comments and tinkered some more it
appears that this is the problem.

How to resolve it? I haven't a clue because I don't know exactly why
[cbqrytypes] being NULL is causing such grief...

 

[Rob Parker]

Hi John,

My major comment is that you've not really thought your user interface
through before designing these forms and how they're interacting. It seems
much too complex - and I expect that's where your problems are coming from.

But nevertheless, here's something more which may help:

If your alternative method of opening frmPackaging via a double-click from
another form uses code that has a WHERE parameter, this will "filter" the
records which appear from the form's recordsource (after applying the
criteria set by cbqryTypes in the SELECT statement which is the form's
RecordSource).

Also, if you change a combobox's value through code, the AfterUpdate event
does not fire - you need to call that code following the line which changes
the combobox's value. Eg:

Me.cbqyTypes = (something)
cbqryTypes_AfterUpdate

Note that is is not contradicting the advice/code I gave in my last post;
that's because the new value of the combobox will be used when the form's
Recordsource will be used when the Requery method is executed.

Again, HTH,

Rob

Hi, Rob.

Thanks for indulging again!

I think that all you should need for your code would be:

Private Sub cbqrytypes_AfterUpdate()
Forms![frmPackaging].Form.Requery
End Sub

which will cause the form to re-execute the SELECT ... statement
which is its RecordSource,

I wish it were this easy! I've actually tried that and it doesn't
work. The form doesn't clear at all and the controls all display null.
Granted, it works fine IF and only IF I open the form either directly
or through other methods that do NOT change the record source
criteria...

Ahhh...I think this is the problem. When I open the form from another
form that filters for the Primary Key [txtProfileID] this results in
[cbqrytypes] being NULL. When I make a selection in [cbqrytypes] its
AfterUpdate event doesn't properly fire because...why...I don't know
but now that I've read through your comments and tinkered some more it
appears that this is the problem.

How to resolve it? I haven't a clue because I don't know exactly why
[cbqrytypes] being NULL is causing such grief...