Error Code 3020 after setting Filter

[vic]

Hello,

I am porting an Access 2002 database to Access 2007 which has an option
group that turns on and off a filter. This works fine in Access 2002 and
filters as it's supposed to do. However, in Access 2007 when I select a
filter and then try to go to another record I get the error : "Update or
CancelUpdate without AddNew or Edit".

I know what it means that an update on a recordset was encountered without a
preceding Edit or Addnew but I did not do an update. I have traced the code
after the filter is set and there is no update. When I get the error I
cannot break into the code. If I include a Form_Error routine it is
throwing error code 3020.

Code for the filter:

If Me!dbFilter.Value = 1 Then
Me.Filter = "EntryNbr <> 0"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

I would really appreciate some help because I'm stuck. Like I said this
database works fine in Access 2002.

Thank you very muych!

Vic

 

[Allen Browne]

Vic, sometimes this error crops up when you move record (side effect of
applying a filter.)

The problem is that Access has to save the current record before it can move
to another. If the record cannot be saved for some reason (e.g. required
field not filled in, validation rule not met, duplicate index clash, bad
foreign key value, ...) the save fails, so the apply filter can't be applied
either.

If the message is caused by this issue, you can identify it by explicitly
saving the record before applying the filter. Try adding this line ahead of
your code:
If Me.Dirty Then Me.Dirty = False
Then if the record cannot be saved, you will get a different error message
saying that the property (i.e. the form's Dirty property) could not be set.

 

[vic]

Allen,

Thank you for the reply but sorry to say that doesn't help. I had read
posts where the dirty property sometimes resolves these problems but in my
case it doesn't. Any ideas as to why it would work in 2002 but not 2007?

I also tried adding the following to the current event as I read a post
where this had resolved a similiar situation. I think this is the same as
setting the dirty property to false.

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If

Thanks for the help Allen,

Vic

 

[vic]

Allen,

I noticed that if I use the right Mouse menu to apply and remove the filter
everything works fine. I also noticed that if I use the vba code to apply
the filter then the right mouse menu no longer contains the filter and sort
options. I have another form in the app where I'm dooing the same thing and
it works ok. After applying the filter with vba code I still have the filter
and sort options on the right menu.

Thought this might help .

Vic

 

[Pieter Wijnen]

Tip
Replace DoCmd.RunCommand acCmdSaveRecord with:

If Me.Dirty = True then
Me.Dirty = False
End If

Pieter

 

[vic]

Thanks Pieter, I did that but it didn't make any difference.

Vic

"Pieter Wijnen"

 

[Pieter Wijnen]

It wouldn't.
It was just meant as a general tip.

Pieter

 

[vic]

Allen & Pieter,

Thanks for the help and it is now resolved which was the result of a stupid
mistake. In the forms current event code there was an On Error Resume Next
which was concealing the real error. There was a control that was missnamed
which was apparantly causing a save problem after the filter was applied as
it passed throught the current event code. I added an ErrorHandler which
pointed out the problem.

Again, thanks very much your help is appreciated.

Vic

 

[Allen Browne]

Vic, thanks for posting the source of the real problem.

Another good example of why we should never to use On Error Resume Next in
anything more than a trivial procedure.