Why do I get this Access error?

  • Thread starter Thread starter David Anderson via AccessMonster.com
  • Start date Start date
D

David Anderson via AccessMonster.com

I am puzzled as to why I get an error message (an Access error, not a VBA
error) in certain circumstances on a form of mine that is bound to a table.
For test purposes, I have reduced my form to only one text box bound to the
FirstFrameNo field in the underlying table. In that table, FirstFrameNo is
defined as a number field with size of Long Integer. This should happily
store any integral number in the range from
-2,147,483,648 to 2,147,483,647. There are no validation rules for
FirstFrameNo set up in either the table definition or the form’s field
properties.

In the BeforeUpdate Event for this text box I have the following code,

If 0 < Me![FirstFrameNo] Then
MsgBox "Unacceptable change"
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If

Entering a value of 1 in the FirstFrameNo field on this form should prompt
the appearance of my warning message box and the subsequent restoration of
the previous FirstFrameNo value. As far as I can see, the actual value of the
previous FirstFrameNo should be irrelevant.

Generally speaking, my code works fine. The only exception is that if the
previous value was a negative number then I get the Access Error “The value
you entered isn’t valid for this field. For example, you may have entered
text in a numeric field or a number that is larger than the FieldSize setting
permits.” This error occurs after exiting the BeforeUpdate Event subroutine
but does not prevent the correct restoration of the previous negative
FirstFrameNo value.

Can anyone explain this behaviour?
 
after creating a single-field table as you described, i data entry on a form
using the code you posted. i wasn't able to duplicate the error you got (i
got a different error), but you might try the following code, as

Private Sub FirstFrameNo_BeforeUpdate(Cancel As Integer)

If 0 < Me!FirstFrameNo Then
MsgBox "Unacceptable change"
Cancel = True
Me!FirstFrameNo.Undo
End If

End Sub

the Undo command restores the previous value in the field, of course. if you
want to simply stop the Update action, just delete the Undo command from the
code.

hth
 
Thanks, Tina. Your alternative code works without triggering any confusing
and unexpected errors. It also looks like a more elegant method than my
original (which was copied from the code generated by the Access 2000 Command
Button Wizard for an Undo button).

It's now just of academic interest, thanks to Tina, but can anyone else
provide a reason for my original error?
 
Back
Top