Data lost after BeforeUpdate is cancelled

[IanC]

Hi

I'm trying to write some code which will force the user to complete a
particular field before exiting a new record on a form. In the BeforeUpdate
event for the form I used the following:

If RequiredField.Value = "" Or IsNull(RequiredField.Value) Then
MsgBox "Please complete the XXX field"
RequiredField.SetFocus
Cancel = True
NoErrors = False
Exit Sub
Else
NoErrors = True
End If

In the Unload event I used the following:

If NoErrors = False Then
Cancel = True
End If
Exit Sub

NoErrors is a global variable which is set to True in the Current event.

If the user tries to navigate to another record this works fine (with the
addition of some error handling). If the user tries to close the form it
works OK in terms of stopping the incomplete record being added, but when the
user is returned to the form the fields they had already completed have now
been left blank, so they've got to fill the whole form in again, and not just
the offending field. Is there any way of cancelling the BeforeUpdate event
without losing the already added data?

The only workarounds I've found are either to use the AfterUpdate event
instead, and force the user to delete the new record if they want to move
away from it, or to use a "Close" button instead of BeforeUpdate and get rid
of the X control. I know the latter is many people's favoured option but I
prefer to keep the X contol if possible since it is intuitive for users.
Surely there has to be a simpler answer?

Thanks
Ian

 

[Allen Browne]

Ian, I think you are referring to this bad bug, which occurs in all versions
of Access:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

I'm assuming you used the BeforeUpdate event of the Form (not the control).
If the user clicks the Close button on the right-end of the form's title
bar, they should receive a standard Access message asking if they want to
lose their edits. That's as expected.

However, if you use a Close action (macro) or the Close method (VBA), the
entry is silently lost.

 

[IanC]

Hi Allen

Thanks for the reply. I had come across some references to this, but what I
don't get is why this happens when you cancel a BeforeUpdate event triggered
by closing the form, but not when you cancel a BeforeUpdate event triggered
by moving to another record. In the latter case you are prevented from moving
on but the data you'd already entered is preserved, even though the record
itself hasn't been inserted. If it can work for the latter I don't see why it
can't work for the former?!

Ian

 

[Allen Browne]

Ian, I agree completely, and I have spent considerable effort several times,
over several years trying to get Microsoft to fix this bug.

IMHO, this is one of the bugs that give Access a bad name. Users swear that
Access loses their entry, but they don't know why, so they just conclude
that Access is unreliable. The reality is that it is a simple bug that
should not be difficult to fix. You are in a long queue of people who keep
getting hurt by the same old unfixed problems.

Don't get me started! ;-)