Problem with On Error Go to in Event Procedure

[beebe]

Hi Everyone,

I have created two tables. Both tables have three fields in them, one
of the fields in each of the tables is a Key. The two tables have a
1-1 relationship setup with referential integerity, cascading delete
and update enforced.

I have created a form for each table to enter data into them. On the
forms I also have a button which closes the form, so the user can close
the form by closing the window (ie click on the X in the upper right
corner of the window) or the user can click on the close button I have
created. The code for the close button follows:

Private Sub Submit_Click()

On Error GoTo Err_Submit_Click

DoCmd.Close

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click

End Sub

Now heres my problem - if the user enters information on the record
that violates referential integerity and then clicks on the windows
close (ie the X) then Access will flag this as an error telling the
user that they cannot do this. Great. On the other hand if the user
clicks on the Button that I created, the form will close, but Access
will not give the errors - it does everything correctly just does not
display the errors.
If I set a break in debug and step through the code it will show the
errors, but if it is just allowed to run it does not even go to the
error routine.
I have already tried putting the code

DoCmd.SetWarnings True

in, but that did not help.
Any help would be welcome, thanks in advance.

Richard

[Allen Browne]

Yes, after 13 years, Access *still* loses your data without warning, exactly
as you say.

Once you know about this data-loss bug, it's easy enough to work around it.
*Always* explicitly save before you use the close method/action on a bound
form, e.g.:
If Me.Dirty Then Me.Dirty = False

If the record cannot be saved, that line will generate an error, so your
routine drops to the error handler, and the Close is never executed.

More info:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

I am convinced that this is one of the bugs that gives Access a bad
reputation as "unreliable". People don't figure out what went wrong: they
just know they entered something that Access "lost".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<be***@bvu.edu> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Hi Everyone,

I have created two tables. Both tables have three fields in them, one
of the fields in each of the tables is a Key. The two tables have a
1-1 relationship setup with referential integerity, cascading delete
and update enforced.

I have created a form for each table to enter data into them. On the
forms I also have a button which closes the form, so the user can close
the form by closing the window (ie click on the X in the upper right
corner of the window) or the user can click on the close button I have
created. The code for the close button follows:

Private Sub Submit_Click()

On Error GoTo Err_Submit_Click

DoCmd.Close

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click

End Sub

Now heres my problem - if the user enters information on the record
that violates referential integerity and then clicks on the windows
close (ie the X) then Access will flag this as an error telling the
user that they cannot do this. Great. On the other hand if the user
clicks on the Button that I created, the form will close, but Access
will not give the errors - it does everything correctly just does not
display the errors.
If I set a break in debug and step through the code it will show the
errors, but if it is just allowed to run it does not even go to the
error routine.
I have already tried putting the code

DoCmd.SetWarnings True

in, but that did not help.
Any help would be welcome, thanks in advance.

Richard

[Bob Quintal]

"be***@bvu.edu" <be***@bvu.edu> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

Hi Everyone,

I have created a form for each table to enter data into them.
On the forms I also have a button which closes the form, so
the user can close the form by closing the window (ie click on
the X in the upper right corner of the window) or the user can
click on the close button I have created.> Now heres my problem - if the user enters information on the
record that violates referential integerity and then clicks on
the windows close (ie the X) then Access will flag this as an
error telling the user that they cannot do this. Great. On the
other hand if the user clicks on the Button that I created,
the form will close, but Access will not give the errors - it
does everything correctly just does not display the errors.
If I set a break in debug and step through the code it will
show the errors, but if it is just allowed to run it does not
even go to the error routine.
Any help would be welcome, thanks in advance.

First you should validate the integrity of the data in the
form's BeforeUpdate event, not wait till the Close event.

--
Bob Quintal

PA is y I've altered my email address.

[beebe]

Thanks Allen,

That worked just great.

Richard