Repost: Errorhandling with Apply Filter Event

  • Thread starter Thread starter Fred Boer
  • Start date Start date
F

Fred Boer

Hello!

I am still struggling with an errorhandling problem, which I posted about
yesterday... I wonder if it might be better for me to pose the question this
way:

Assume I am using the shortcut menu item "Filter For:" to enter a filter
parameter for a filter which will be applied to a numeric field on a form.
Entering a letter will generate a 3464 "Data Type Mismatch" error. How can I
trap this error, substitute my own custom error message, and cancel the
application of the filter so that the recordset remains unfiltered?

This sounds to me like it should be straightforward, but I've been fumbling
with it unsuccessfully for a number of hours over three days now! :(

Thanks!
Fred Boer
 
Wait! Wait! I think I've finally got it...

Problem 1:

Ok. To catch error 3464, I put the following in Form_Error:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3464
MsgBox "This field does not contain this type of data. Please try
again.", vbOKOnly + vbInformation, "W. Ross Macdonald School"
Response = acDataErrContinue
Me.Undo
End Select
End Sub


How does that look? I *think* it is working... but if I still have your
attention...

Problem 2:

I am close to dealing with 3021 (No Current Record). If I enter a filter
parameter which will return no records, I have code that successfully pops
up my custom error message (below). BUT the user is left staring at the
empty record's empty fields. How do I cancel the application of the filter,
and leave them looking at an unfiltered recordset?

Private Sub Form_Current()
On Error GoTo Errorhandler
Me.lblFiltered.Visible = Me.FilterOn
ExitPoint:
Exit Sub
Errorhandler:
If Err.Number = 3021 Then
MsgBox "No records have been found. Please try again.", vbOKOnly +
vbInformation, "W. Ross Macdonald School"
Else
fncWRMSErrMsg Err.Number, Err.Description
End If
Resume ExitPoint
End Sub


Many, many thanks!
Fred
 
1. looks good to me.

2. I don't quite see how you detect that no records were
found, but can't you get rid of the filter by using
Me.FilterOn = False
 
Dear Marsh:

Thanks for your help! I have been working on this quite a bit this
afternoon, and am getting somewhat muddled. However:

PROBLEM 2:

You were correct to be confused. This is the new code which I am now using
in the Apply Filter event. It seems to work just fine to avoid the 3021 "No
current Record" error. So. That seems to be solved.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
On Error GoTo Errorhandler

If DCount("*", Me.RecordSource, Me.Filter) = 0 Then
MsgBox "There are no records which match this criteria.", vbInformation
+ vbOKOnly, "W. Ross Macdonald School"
Cancel = True
End If

ExitPoint:
Exit Sub
Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint
End Sub

BUT...PROBLEM 1:

The code below is actually *not* working. If I enter mismatched data as the
filter criteria.. I see *two* error messages. The first appears (given my
limited debugging skills) to be generated by the errorhandling within the
Apply Filter event (see above) ; the second is the code in the Form's Error
event (see below). Even if I try trapping for the 3464 error in the Apply
Filter event (see second snippet below), it *still* seems to trigger the
Form's error event, that is to say, it still generates a second Access error
message. I've been working on this for too long, perhaps, and I feel
somewhat muddled. I hope my questions aren't too confusing!

Thanks very much for any suggestions!
Fred



Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Errorhandler
Select Case DataErr
Case 3464
MsgBox "This field does not contain this type of data. Please try
again.", vbOKOnly + vbInformation, "W. Ross Macdonald School"
Response = acDataErrContinue
Me.Undo
End Select



ExitPoint:
Exit Sub

Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint

End Sub

CODE SNIPPET 2:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
On Error GoTo Errorhandler

If DCount("*", Me.RecordSource, Me.Filter) = 0 Then
MsgBox "There are no records which match this criteria.", vbInformation
+ vbOKOnly, "W. Ross Macdonald School"
Cancel = True
End If

ExitPoint:
Exit Sub
Errorhandler:
If Err.Number = 3464 Then
MsgBox "This is the wrong type of data for this field.",
vbInformation + vbOKOnly, "W. Ross Macdonald School"
Me.Undo
Else
fncWRMSErrMsg Err.Number, Err.Description
End If

Resume ExitPoint
End Sub
Marshall Barton said:
1. looks good to me.

2. I don't quite see how you detect that no records were
found, but can't you get rid of the filter by using
Me.FilterOn = False
--
Marsh
MVP [MS Access]



Fred said:
Wait! Wait! I think I've finally got it...

Problem 1:

Ok. To catch error 3464, I put the following in Form_Error:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3464
MsgBox "This field does not contain this type of data. Please try
again.", vbOKOnly + vbInformation, "W. Ross Macdonald School"
Response = acDataErrContinue
Me.Undo
End Select
End Sub


How does that look? I *think* it is working... but if I still have your
attention...

Problem 2:

I am close to dealing with 3021 (No Current Record). If I enter a filter
parameter which will return no records, I have code that successfully pops
up my custom error message (below). BUT the user is left staring at the
empty record's empty fields. How do I cancel the application of the
filter,
and leave them looking at an unfiltered recordset?

Private Sub Form_Current()
On Error GoTo Errorhandler
Me.lblFiltered.Visible = Me.FilterOn
ExitPoint:
Exit Sub
Errorhandler:
If Err.Number = 3021 Then
MsgBox "No records have been found. Please try again.", vbOKOnly +
vbInformation, "W. Ross Macdonald School"
Else
fncWRMSErrMsg Err.Number, Err.Description
End If
Resume ExitPoint
End Sub
Click to expand...
Click to expand...
 
Fred Boer said:
Dear Marsh:

Thanks for your help! I have been working on this quite a bit this
afternoon, and am getting somewhat muddled. However:

PROBLEM 2:

You were correct to be confused. This is the new code which I am now
using in the Apply Filter event. It seems to work just fine to avoid
the 3021 "No current Record" error. So. That seems to be solved.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
On Error GoTo Errorhandler

If DCount("*", Me.RecordSource, Me.Filter) = 0 Then
MsgBox "There are no records which match this criteria.",
vbInformation + vbOKOnly, "W. Ross Macdonald School"
Cancel = True
End If

ExitPoint:
Exit Sub
Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint
End Sub

BUT...PROBLEM 1:

The code below is actually *not* working. If I enter mismatched data
as the filter criteria.. I see *two* error messages. The first
appears (given my limited debugging skills) to be generated by the
errorhandling within the Apply Filter event (see above) ; the second
is the code in the Form's Error event (see below). Even if I try
trapping for the 3464 error in the Apply Filter event (see second
snippet below), it *still* seems to trigger the Form's error event,
that is to say, it still generates a second Access error message.
I've been working on this for too long, perhaps, and I feel somewhat
muddled. I hope my questions aren't too confusing!

Thanks very much for any suggestions!
Fred



Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Errorhandler
Select Case DataErr
Case 3464
MsgBox "This field does not contain this type of data. Please try
again.", vbOKOnly + vbInformation, "W. Ross Macdonald School"
Response = acDataErrContinue
Me.Undo
End Select



ExitPoint:
Exit Sub

Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint

End Sub

CODE SNIPPET 2:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
On Error GoTo Errorhandler

If DCount("*", Me.RecordSource, Me.Filter) = 0 Then
MsgBox "There are no records which match this criteria.",
vbInformation + vbOKOnly, "W. Ross Macdonald School"
Cancel = True
End If

ExitPoint:
Exit Sub
Errorhandler:
If Err.Number = 3464 Then
MsgBox "This is the wrong type of data for this field.",
vbInformation + vbOKOnly, "W. Ross Macdonald School"
Me.Undo
Else
fncWRMSErrMsg Err.Number, Err.Description
End If

Resume ExitPoint
End Sub
Click to expand...

Fred -

It looks to me like you need to Cancel the ApplyFilter action in the
Errorhandler code for Form_ApplyFilter() event procedure. What happens
if you do this:
 
(sending again as something seems to have happened to my first attempt)

Fred Boer said:
Dear Marsh:

Thanks for your help! I have been working on this quite a bit this
afternoon, and am getting somewhat muddled. However:

PROBLEM 2:

You were correct to be confused. This is the new code which I am now
using in the Apply Filter event. It seems to work just fine to avoid
the 3021 "No current Record" error. So. That seems to be solved.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
On Error GoTo Errorhandler

If DCount("*", Me.RecordSource, Me.Filter) = 0 Then
MsgBox "There are no records which match this criteria.",
vbInformation + vbOKOnly, "W. Ross Macdonald School"
Cancel = True
End If

ExitPoint:
Exit Sub
Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint
End Sub

BUT...PROBLEM 1:

The code below is actually *not* working. If I enter mismatched data
as the filter criteria.. I see *two* error messages. The first
appears (given my limited debugging skills) to be generated by the
errorhandling within the Apply Filter event (see above) ; the second
is the code in the Form's Error event (see below). Even if I try
trapping for the 3464 error in the Apply Filter event (see second
snippet below), it *still* seems to trigger the Form's error event,
that is to say, it still generates a second Access error message.
I've been working on this for too long, perhaps, and I feel somewhat
muddled. I hope my questions aren't too confusing!

Thanks very much for any suggestions!
Fred



Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Errorhandler
Select Case DataErr
Case 3464
MsgBox "This field does not contain this type of data. Please try
again.", vbOKOnly + vbInformation, "W. Ross Macdonald School"
Response = acDataErrContinue
Me.Undo
End Select



ExitPoint:
Exit Sub

Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint

End Sub

CODE SNIPPET 2:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
On Error GoTo Errorhandler

If DCount("*", Me.RecordSource, Me.Filter) = 0 Then
MsgBox "There are no records which match this criteria.",
vbInformation + vbOKOnly, "W. Ross Macdonald School"
Cancel = True
End If

ExitPoint:
Exit Sub
Errorhandler:
If Err.Number = 3464 Then
MsgBox "This is the wrong type of data for this field.",
vbInformation + vbOKOnly, "W. Ross Macdonald School"
Me.Undo
Else
fncWRMSErrMsg Err.Number, Err.Description
End If

Resume ExitPoint
End Sub
Click to expand...

Fred -

It looks to me like you need to Cancel the ApplyFilter action in the
Errorhandler code for Form_ApplyFilter() event procedure. What happens
if you do this:
 
Hi Dirk:

I'm posting this on my basement computer while pretending to clean the cat's
litter box... <g> I hope to have time to try your suggestion sometime
tonight!

Thanks!
Fred
 
Dear Dirk:
What happens if you do this:
Click to expand...

Sigh...it works

And so does this:

If Err.Number = 3464 Then
Cancel = True
MsgBox "This is the ....

And this:

If Err.Number = 3464 Then
MsgBox "This is the wrong type of data for this ..."
Cancel = True
Me.Undo


But, Dirk, Marsh, please believe me! I *swear* that I tried every
conceivable combination of "Cancel=True" and "Me.Undo" and
"Response=whatever", for hours and hours over the last few days!! I don't
know *how* it is possible that I never made it work... SIGH... Oh well...
tomorrow's another day...

Thanks so much Dirk and Marsh! The great and powerful! <g>

Fred (the small and meek..)
 
Hi Dirk:

I spoke a bit too soon... I am still having problems, and I think I might
have a clue why I couldn't make it work earlier; I was using a combobox for
my filter field instead of a text box. If I use the shortcut "Filter For:"
on a *Text* field it works perfectly. However, if I attempt to do a filter
on a combobox control, I get error(s)... The only visible error message is
2001 "Cancelled previous". However, I set some break points and I have
uncovered the following error message:

The expression you entered as a query parameter produced this error: 'The
object doesn't contain the Automation object 'Lookup_cboPubPlace.PubPlace."

Anyway.. I'm starting to work on this to see if I can figure this one out..
if I can't I might post back. If you don't mind could you please check back
here over the next day or so?

Thanks!
Fred
 
Fred Boer said:
Hi Dirk:

I spoke a bit too soon... I am still having problems, and I think I
might have a clue why I couldn't make it work earlier; I was using a
combobox for my filter field instead of a text box. If I use the
shortcut "Filter For:" on a *Text* field it works perfectly. However,
if I attempt to do a filter on a combobox control, I get error(s)...
The only visible error message is 2001 "Cancelled previous". However,
I set some break points and I have uncovered the following error
message:

The expression you entered as a query parameter produced this error:
'The object doesn't contain the Automation object
'Lookup_cboPubPlace.PubPlace."

Anyway.. I'm starting to work on this to see if I can figure this one
out.. if I can't I might post back. If you don't mind could you
please check back here over the next day or so?
Click to expand...

Sure, but I can tell you a little bit about your problem now. This is
the situation as I understand it. The value your combo box is
displaying comes from some column other than the bound column. When you
ask Access to filter on this value, it is "aware" of this and uses a
hidden query, "Lookup_cboPubPlace", to lookup the bound-column value
that has to be filtered on.

Consider this example: suppose your combo box's rowsource provides
these two columns of data:

1, "Fred"
2, "Dirk"
3, "Jeff"

Suppose also that the bound column is column 1, the number, but you're
displaying column 2, the name. Now you go to filter your form for all
records with the value "Fred" in the combo box. But "Fred" isn't
actually stored in the data, because this isn't the bound column.
Therefore, what I think Access does is build a query to look up the
bound-column value that corresponds to the displayed value (or it may
already have created the query to use with the combo box, I'm not sure),
join the form's recordsource to that query, and make a filter that
applies your criterion to the looked-up field from that query. I should
say that this is just my conclusions based on some fooling around I did
with this a long time ago; it shouldn't be taken as gospel.

In the case of your form, code, and error message, "Lookup_cboPubPlace"
is the name of the hidden query Access is using to filter the form. The
problem is that, when you go to use that filter yourself, as in your
DLookup, you don't have access to that hidden query.

As I said, I fooled around with this for a while a couple of years ago.
I was thinking about developing a general-purpose mechanism for
transforming such lookup references in filters into criteria that could
be applied directly recordsources, outside of built-in filtering
process. I was still struggling with it when other matters intervened,
and I put it aside, and haven't picked it up since.

I'm going to think about it a bit more now. In your case, if you don't
need a general solution, you can probably take the filter, parse out all
the criteria that refer to combo or listbox lookups, and transform them
yourself into corresponding criteria that refer only to the data
actually present in the recordsource. That would be a bit of work and
coding, but not really all that complicated if you know the names of the
combos in question.

Alternative to that would be finding a different way to accomplish your
greater goal, which as I understand it is just displaying a message if
the user tries to apply a filter that returns no records.

I'll get back to you.
 
Dirk Goldgar said:
Sure, but I can tell you a little bit about your problem now. This is
the situation as I understand it. The value your combo box is
displaying comes from some column other than the bound column. When you
ask Access to filter on this value, it is "aware" of this and uses a
hidden query, "Lookup_cboPubPlace", to lookup the bound-column value
that has to be filtered on.
Click to expand...

Verrrry interesting Yoda.
Everything you said sounds extremely plausible.
 
Hi Dirk (and Jeff):

Well, I've put another couple of hours into this, and I feel a lot better
after reading your message. I have to tell you I was feeling pretty stupid,
and more than a little frustrated!

Your assumptions are correct, particularly with respect to the way the
comboboxes are set up, etc. Today, I tried simplifying things by creating a
new, stripped-down database from scratch and working with it. I had gotten
as far as determining that the DCount was the problem, since that's where
the error popped up, but no matter what I did, I couldn't seem to catch that
error with the form's error event, or the Apply Filter event errorhandling -
so I was stuck.

Would I be correct in assuming that the error is actually generated in the
behind-the-scenes code, and that is why it is out of my reach?

BTW, I don't know if it is worth any hard thinking, Dirk, since it isn't
really a significant problem. I already have two versions of the same form -
one browse/edit, one read-only. The read-only form, based on a
non-updateable query, contains all of the same fields as the browse/edit
form, with the advantage that the controls are all textboxes. Since
textboxes are no problem, I can use the "filterable" form to perform the
filter. (I almost always use filtering simply to locate an individual
record). So, once I've found the record on the "filterable" form, I have a
command button that flips open the browse/edit form to the same record.

To be honest, the only reason I was putting so much energy into it was that
it seemed to me that it should be simple enough, and I was darned if I was
going to let the problem beat me... not because I really needed a solution.
So, you may pick up the problem again, or let it lie, as you wish. :)

Thanks very much!
Fred...
 
Back
Top