BeforeUpdate, what's going wrong?

[Shane S via AccessMonster.com]

I have read a number of threads about validating required fields in the forms
BeforeUpdate event so I'm trying to accomplish what has been recommended, but
having one problem. The code below will trap whether the LastName control has
been filled in or not and will display the messages correctly but if I choose
"Yes" as a choice when asked if I know the name, then I expect to have the
focus set to the LastName control but instead the form goes again a closes.
What am I doing wrong that I can not get the form to not close and get the
focus set to the LastName control?

If Trim(Nz(Me.LastName, 0)) = 0 Then
If Eval("Msgbox('Last Name Field Has Been Left Blank@Last Name field must
been filled out in order to " & _
"save record@Do you know the Last Name?@@',4,'Amigo Message System')") = vbNo
Then
Eval ("Msgbox('I can not save this record at this time!@I can not save a
record on a P.T. and not " & _
"know their last name. Sorry!!!@@',0,'Amigo Message System')")
Cancel = True
Else
Me.LastName.SetFocus
Cancel = True
Exit Sub
End If
End If

TIA,
Shane

 

[Rick Brandt]

I have read a number of threads about validating required fields in
the forms BeforeUpdate event so I'm trying to accomplish what has
been recommended, but having one problem. The code below will trap
whether the LastName control has been filled in or not and will
display the messages correctly but if I choose "Yes" as a choice when
asked if I know the name, then I expect to have the focus set to the
LastName control but instead the form goes again a closes. What am I
doing wrong that I can not get the form to not close and get the
focus set to the LastName control?

If Trim(Nz(Me.LastName, 0)) = 0 Then
If Eval("Msgbox('Last Name Field Has Been Left Blank@Last Name field
must been filled out in order to " & _
"save record@Do you know the Last Name?@@',4,'Amigo Message
System')") = vbNo Then
Eval ("Msgbox('I can not save this record at this time!@I can not
save a record on a P.T. and not " & _
"know their last name. Sorry!!!@@',0,'Amigo Message System')")
Cancel = True
Else
Me.LastName.SetFocus
Cancel = True
Exit Sub
End If
End If

TIA,
Shane

You should set Cancel to True no matter how they respond to the MsgBox. That way
focus is never lost so there is no reason for the SetFocus command.

The difference is that if you want to discard the record you also issue an Undo
command.

 

[Shane S via AccessMonster.com]

Hey Rick,

Thanks for the reply. I have set Cancel to True no matter what the response,
however the form is still closing. This code is in the forms BeforeUpdate
event. How do I stop the form from closing and get the focus back to the
LastName control, if the user chooses "Yes?"

Shane

I have read a number of threads about validating required fields in
the forms BeforeUpdate event so I'm trying to accomplish what has

[quoted text clipped - 24 lines]

TIA,
Shane

You should set Cancel to True no matter how they respond to the MsgBox. That way
focus is never lost so there is no reason for the SetFocus command.

The difference is that if you want to discard the record you also issue an Undo
command.

 

[Rick Brandt]

Hey Rick,

Thanks for the reply. I have set Cancel to True no matter what the
response, however the form is still closing. This code is in the
forms BeforeUpdate event. How do I stop the form from closing and
get the focus back to the LastName control, if the user chooses "Yes?"

Shane

I have read a number of threads about validating required fields
in the forms BeforeUpdate event so I'm trying to accomplish what
has

[quoted text clipped - 24 lines]

TIA,
Shane

You should set Cancel to True no matter how they respond to the
MsgBox. That way focus is never lost so there is no reason for the
SetFocus command.

The difference is that if you want to discard the record you also
issue an Undo command.

If it's in the BeforeUpdate event then what is causing the form to close?

If the form is attempting to save the record because the user is closing the
form then cancelling the update should cancel the close as well. The exception
is if you have created a custom close button. In that case you have to
explicitly issue a save to the record before calling the close method.

 

[Shane S via AccessMonster.com]

Thanks Rick,

It is a custom cmdButton that I'm using to Close, so with that being said,
what do you mean when you say, "you have to explicitly issue a save to the
record before calling the close method?"

Also, the reason for me asking this question in the first place is because of
reading many post on several forum sites and seeing the advise given over and
over that the ONLY place to validate data is in th forms BeforeUpdate event,
so I thought well if so many are saying that then I guess that's what I need
to do. Before I decided to change, I have all of the validation at the
cmdClose and everything seemed to work fine there. It did not save a record
to the table if the User did not give me the data necessary for that record.
Is validating data at the BeforeUpdate event the only way to go? Am I
setting myself up for problems if I don't do it that way?

TIA,
Shane

BTW-- I wondered if the custom cmdClose button was causing the problem so
last night I added the forms Close button back the form and clicked there to
close the form and the form still closed. Did not set the focus at the
LastName field, which made me think that it did not make a difference.

Hey Rick,

[quoted text clipped - 18 lines]
If it's in the BeforeUpdate event then what is causing the form to close?

If the form is attempting to save the record because the user is closing the
form then cancelling the update should cancel the close as well. The exception
is if you have created a custom close button. In that case you have to
explicitly issue a save to the record before calling the close method.

 

[Rick Brandt]

Thanks Rick,

It is a custom cmdButton that I'm using to Close, so with that being
said, what do you mean when you say, "you have to explicitly issue a
save to the record before calling the close method?"

Instead of...

DoCmd.Close

....use...

Me.Dirty = False
DoCmd.Close

The first line will trigger your BeforeUpdate validation code and if the update
event is cancelled by your code then the Close line will never even be executed.

Also, the reason for me asking this question in the first place is
because of reading many post on several forum sites and seeing the
advise given over and over that the ONLY place to validate data is in
th forms BeforeUpdate event, so I thought well if so many are saying
that then I guess that's what I need to do. Before I decided to
change, I have all of the validation at the cmdClose and everything
seemed to work fine there. It did not save a record to the table if
the User did not give me the data necessary for that record. Is
validating data at the BeforeUpdate event the only way to go? Am I
setting myself up for problems if I don't do it that way?

Well yes, you could be. There are MANY actions the user can perform that will
trigger an automatic save of the current record. If they did any of those prior
to pressing your close button then any validation you have in that event would
be too late to stop the save. BeforeUpdate fires just before saving the record
regardless of what action triggers the save.

 

[Shane S via AccessMonster.com]

Hey Rick,

Hope your hanging with me, cause you've at least gotten me close than anyone
has. I have actually asked this question before but didn't get much help.

Ok, now it will stop the form from closing but need to report to you several
things it's doing.

1. I now get an Access error message stating, "The setting you entered isn't
valid for this property."

2. If I chose "NO" I do not want to save, then the form does not close.

Because of this, I don't think I can tell if it's cancelling adding a record
yet.

Got anymore good idea's?
Shane

Thanks Rick,

It is a custom cmdButton that I'm using to Close, so with that being
said, what do you mean when you say, "you have to explicitly issue a
save to the record before calling the close method?"

Instead of...

DoCmd.Close

...use...

Me.Dirty = False
DoCmd.Close

The first line will trigger your BeforeUpdate validation code and if the update
event is cancelled by your code then the Close line will never even be executed.

Also, the reason for me asking this question in the first place is
because of reading many post on several forum sites and seeing the

[quoted text clipped - 6 lines]

validating data at the BeforeUpdate event the only way to go? Am I
setting myself up for problems if I don't do it that way?

Well yes, you could be. There are MANY actions the user can perform that will
trigger an automatic save of the current record. If they did any of those prior
to pressing your close button then any validation you have in that event would
be too late to stop the save. BeforeUpdate fires just before saving the record
regardless of what action triggers the save.

 

[Shane S via AccessMonster.com]

Rick,

In playing with this a little more. I think the Cancel = True is the part
that's causing the Access error. If I take it away then on the "NO" side of
things, everything works as needed, but on the "YES" side of things it does
not. The form will go ahead and close and will save also.

Just a little more info,
Shane

Hey Rick,

Hope your hanging with me, cause you've at least gotten me close than anyone
has. I have actually asked this question before but didn't get much help.

Ok, now it will stop the form from closing but need to report to you several
things it's doing.

1. I now get an Access error message stating, "The setting you entered isn't
valid for this property."

2. If I chose "NO" I do not want to save, then the form does not close.

Because of this, I don't think I can tell if it's cancelling adding a record
yet.

Got anymore good idea's?
Shane
[quoted text clipped - 25 lines]

be too late to stop the save. BeforeUpdate fires just before saving the record
regardless of what action triggers the save.

 

[Rick Brandt]

The error your getting is the normal reaction to canceling the update using
Me.Dirty = False. In your close code you just need to add an Error Handler and
set it up to ignore that particular error.

Rick,

In playing with this a little more. I think the Cancel = True is the
part that's causing the Access error. If I take it away then on the
"NO" side of things, everything works as needed, but on the "YES"
side of things it does not. The form will go ahead and close and
will save also.

Just a little more info,
Shane

Hey Rick,

Hope your hanging with me, cause you've at least gotten me close
than anyone has. I have actually asked this question before but
didn't get much help.

Ok, now it will stop the form from closing but need to report to
you several things it's doing.

1. I now get an Access error message stating, "The setting you
entered isn't valid for this property."

2. If I chose "NO" I do not want to save, then the form does not
close.

Because of this, I don't think I can tell if it's cancelling adding
a record yet.

Got anymore good idea's?
Shane

Thanks Rick,

[quoted text clipped - 25 lines]

be too late to stop the save. BeforeUpdate fires just before
saving the record regardless of what action triggers the save.

 

[Shane S via AccessMonster.com]

Rick,

Thanks for all of your help and patience. I greatly appreciate it.

Shane

The error your getting is the normal reaction to canceling the update using
Me.Dirty = False. In your close code you just need to add an Error Handler and
set it up to ignore that particular error.

Rick,

[quoted text clipped - 33 lines]