BeforeUpdate Problem

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

I have a textbox control (txtSurveyDate) for a date field,
and I've put some code for that control on the BeforeUpdate
event to alert the user if they put in last year's date.

If they *have* put in last year's date, my message box will
alert them, and give them the choice of accepting that date
or cancelling.

The problem is, when the user selects "Cancel", my code
"un-does" and cancels the entire record, and leaves the
focus at the said control (which is not the first control
for the record).

What I would prefer, is when the user selects "Cancel", that
the focus would stay on that control, without further error
messages.

Here's the code:

******
Private Sub txtSurveyDate_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtSurveyDate_BeforeUpdate

If Me.NewRecord Then
If Year([txtSurveyDate]) = Year(Now()) - 1 Then
DoCmd.Beep
Dim Msg, Style, Title, Response
Msg = "If you sure the year you entered is_
correct, click 'OK'. " & vbCrLf &_
"Otherwise, click 'Cancel'"
Style = 1 + 32 + 256
Title = "The year you entered may not be_
correct"
Response = MsgBox(Msg, Style, Title, _
Help, Ctxt)
If Response = 2 Then
Me.Undo
Cancel = True
End If
End If
End If

Exit_txtSurveyDate_BeforeUpdate:
Exit Sub

Err_txtSurveyDate_BeforeUpdate:
MsgBox Err.Description
Resume Exit_txtSurveyDate_BeforeUpdate

End Sub
******
 
croy said:
I have a textbox control (txtSurveyDate) for a date field,
and I've put some code for that control on the BeforeUpdate
event to alert the user if they put in last year's date.

If they *have* put in last year's date, my message box will
alert them, and give them the choice of accepting that date
or cancelling.

The problem is, when the user selects "Cancel", my code
"un-does" and cancels the entire record, and leaves the
focus at the said control (which is not the first control
for the record).

What I would prefer, is when the user selects "Cancel", that
the focus would stay on that control, without further error
messages.

Here's the code:

******
Private Sub txtSurveyDate_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtSurveyDate_BeforeUpdate

If Me.NewRecord Then
If Year([txtSurveyDate]) = Year(Now()) - 1 Then
DoCmd.Beep
Dim Msg, Style, Title, Response
Msg = "If you sure the year you entered is_
correct, click 'OK'. " & vbCrLf &_
"Otherwise, click 'Cancel'"
Style = 1 + 32 + 256
Title = "The year you entered may not be_
correct"
Response = MsgBox(Msg, Style, Title, _
Help, Ctxt)
If Response = 2 Then
Me.Undo
Cancel = True
Click to expand...

Me.Undo indoes the entire record. If you want to undo just
one control use:

Me.txtSurveyDate.Undo
Click to expand...

Thanks for the reply.

When I do that, I then get the error:

"The value in the field or record violates the validation
rule for the record or field."

And I really don't want to change my validation rule.
 
croy said:
Marshall said:
croy wrote:

I have a textbox control (txtSurveyDate) for a date field,
and I've put some code for that control on the BeforeUpdate
event to alert the user if they put in last year's date.

If they *have* put in last year's date, my message box will
alert them, and give them the choice of accepting that date
or cancelling.

The problem is, when the user selects "Cancel", my code
"un-does" and cancels the entire record, and leaves the
focus at the said control (which is not the first control
for the record).

What I would prefer, is when the user selects "Cancel", that
the focus would stay on that control, without further error
messages.

Here's the code:

******
Private Sub txtSurveyDate_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtSurveyDate_BeforeUpdate

If Me.NewRecord Then
If Year([txtSurveyDate]) = Year(Now()) - 1 Then
DoCmd.Beep
Dim Msg, Style, Title, Response
Msg = "If you sure the year you entered is_
correct, click 'OK'. " & vbCrLf &_
"Otherwise, click 'Cancel'"
Style = 1 + 32 + 256
Title = "The year you entered may not be_
correct"
Response = MsgBox(Msg, Style, Title, _
Help, Ctxt)
If Response = 2 Then
Me.Undo
Cancel = True

Me.Undo undoes the entire record. If you want to undo just
one control use:

Me.txtSurveyDate.Undo
Click to expand...

Thanks for the reply.

When I do that, I then get the error:

"The value in the field or record violates the validation
rule for the record or field."

And I really don't want to change my validation rule.
Click to expand...


If the Me.txtSurveyDate.Undo line is causing that error,
then the value that was previously in the text box was
invalid and could not have been entered. Based on this
logic, I conclude that there is more going on than you've
explained.

Perhaps the DefaultValue does not pass the validation rule
or it does not have a DefaultValue and you have the field's
Required property set to Yes.
Click to expand...

That last bit is the case. I was thinking (hoping?) that
undoing the entry, and canceling the BeforeUpdate event
would leave the control as if it had just gotten the focus.
In these cases, your only
options are to either undo the entire record as you
originally were trying to avoid or to not undo the text box
and leave the unacceptable value in the text box for the
user to replace.
Click to expand...

I tried remarking out the "undo" part, and leaving the
"cancel" part, but got the same error. When I click away
the error box, the entered value remains, with the cursor
flashing after the last character. Not bad, except I'd
rather the user didn't see the error message.

If I leave the "undo" in there, the result is the same,
except that the entire value entered is selected. This
would be perfect if the user didn't have to deal with the
error message.
 
I am indebted to you for your continued efforts to get my
brain to work. More below...

croy said:
Marshall said:
croy wrote:

Marshall Barton wrote:

croy wrote:

I have a textbox control (txtSurveyDate) for a date field,
and I've put some code for that control on the BeforeUpdate
event to alert the user if they put in last year's date.

If they *have* put in last year's date, my message box will
alert them, and give them the choice of accepting that date
or cancelling.

The problem is, when the user selects "Cancel", my code
"un-does" and cancels the entire record, and leaves the
focus at the said control (which is not the first control
for the record).

What I would prefer, is when the user selects "Cancel", that
the focus would stay on that control, without further error
messages.

Here's the code:

******
Private Sub txtSurveyDate_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtSurveyDate_BeforeUpdate

If Me.NewRecord Then
If Year([txtSurveyDate]) = Year(Now()) - 1 Then
DoCmd.Beep
Dim Msg, Style, Title, Response
Msg = "If you sure the year you entered is_
correct, click 'OK'. " & vbCrLf &_
"Otherwise, click 'Cancel'"
Style = 1 + 32 + 256
Title = "The year you entered may not be_
correct"
Response = MsgBox(Msg, Style, Title, _
Help, Ctxt)
If Response = 2 Then
Me.Undo
Cancel = True

Me.Undo undoes the entire record. If you want to undo just
one control use:

Me.txtSurveyDate.Undo

Thanks for the reply.

When I do that, I then get the error:

"The value in the field or record violates the validation
rule for the record or field."

And I really don't want to change my validation rule.


If the Me.txtSurveyDate.Undo line is causing that error,
then the value that was previously in the text box was
invalid and could not have been entered. Based on this
logic, I conclude that there is more going on than you've
explained.

Perhaps the DefaultValue does not pass the validation rule
or it does not have a DefaultValue and you have the field's
Required property set to Yes.
Click to expand...

That last bit is the case. I was thinking (hoping?) that
undoing the entry, and canceling the BeforeUpdate event
would leave the control as if it had just gotten the focus.
In these cases, your only
options are to either undo the entire record as you
originally were trying to avoid or to not undo the text box
and leave the unacceptable value in the text box for the
user to replace.
Click to expand...

I tried remarking out the "undo" part, and leaving the
"cancel" part, but got the same error. When I click away
the error box, the entered value remains, with the cursor
flashing after the last character. Not bad, except I'd
rather the user didn't see the error message.

If I leave the "undo" in there, the result is the same,
except that the entire value entered is selected. This
would be perfect if the user didn't have to deal with the
error message.
Click to expand...


How about setting the text box's DefaultValue to Date() so
the Undo should set it back to that?
Click to expand...

Not a bad thought, but I'd rather the data-entry clerk see
the "bad" data they had entered (and be able to see what was
wrong), rather than see some date that not what is should
be, and not what they had entered.
If you want to select the entire contents, I think you can
add two more lines:
Me.txtSurveyDate.Undo
Cancel = True
Me.txtSurveyDate.SelStart = 0
Me.txtSurveyDate.SelLength = 99
Click to expand...

It seems that as long as the source field is a "required"
field, the "Cancel = True" statement causes the canned
"validation" error message to appear.

Here's what I don't understand: If I completely remove the
"Undo" part from the BeforeUpdate code for the txtSurveyDate
control, but leave in the "Cancel = True" part, when the
user enters "bad" data, the focus stays on that control, and
leaves the "bad" value in place--why would that trigger the
canned "validation" error? The record is still dirty,
there's an entry (at least in the control on the form) and
the update for that control has been cancelled, right?
Shouldn't the record be just like if there was no
BeforeUpdate code, and the user had just entered data in
that control, but hadn't done anything to move the focus to
the next record?
 
croy said:
I am indebted to you for your continued efforts to get my
brain to work. More below...

croy wrote:

Marshall Barton wrote:

croy wrote:

Marshall Barton wrote:

croy wrote:

I have a textbox control (txtSurveyDate) for a date field,
and I've put some code for that control on the BeforeUpdate
event to alert the user if they put in last year's date.

If they *have* put in last year's date, my message box will
alert them, and give them the choice of accepting that date
or cancelling.

The problem is, when the user selects "Cancel", my code
"un-does" and cancels the entire record, and leaves the
focus at the said control (which is not the first control
for the record).

What I would prefer, is when the user selects "Cancel", that
the focus would stay on that control, without further error
messages.

Here's the code:

******
Private Sub txtSurveyDate_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtSurveyDate_BeforeUpdate

If Me.NewRecord Then
If Year([txtSurveyDate]) = Year(Now()) - 1 Then
DoCmd.Beep
Dim Msg, Style, Title, Response
Msg = "If you sure the year you entered is_
correct, click 'OK'. " & vbCrLf &_
"Otherwise, click 'Cancel'"
Style = 1 + 32 + 256
Title = "The year you entered may not be_
correct"
Response = MsgBox(Msg, Style, Title, _
Help, Ctxt)
If Response = 2 Then
Me.Undo
Cancel = True

Me.Undo undoes the entire record. If you want to undo just
one control use:

Me.txtSurveyDate.Undo

Thanks for the reply.

When I do that, I then get the error:

"The value in the field or record violates the validation
rule for the record or field."

And I really don't want to change my validation rule.


If the Me.txtSurveyDate.Undo line is causing that error,
then the value that was previously in the text box was
invalid and could not have been entered. Based on this
logic, I conclude that there is more going on than you've
explained.

Perhaps the DefaultValue does not pass the validation rule
or it does not have a DefaultValue and you have the field's
Required property set to Yes.

That last bit is the case. I was thinking (hoping?) that
undoing the entry, and canceling the BeforeUpdate event
would leave the control as if it had just gotten the focus.

In these cases, your only
options are to either undo the entire record as you
originally were trying to avoid or to not undo the text box
and leave the unacceptable value in the text box for the
user to replace.

I tried remarking out the "undo" part, and leaving the
"cancel" part, but got the same error. When I click away
the error box, the entered value remains, with the cursor
flashing after the last character. Not bad, except I'd
rather the user didn't see the error message.

If I leave the "undo" in there, the result is the same,
except that the entire value entered is selected. This
would be perfect if the user didn't have to deal with the
error message.


How about setting the text box's DefaultValue to Date() so
the Undo should set it back to that?
Click to expand...

Not a bad thought, but I'd rather the data-entry clerk see
the "bad" data they had entered (and be able to see what was
wrong), rather than see some date that not what is should
be, and not what they had entered.
If you want to select the entire contents, I think you can
add two more lines:
Me.txtSurveyDate.Undo
Cancel = True
Me.txtSurveyDate.SelStart = 0
Me.txtSurveyDate.SelLength = 99
Click to expand...

It seems that as long as the source field is a "required"
field, the "Cancel = True" statement causes the canned
"validation" error message to appear.

Here's what I don't understand: If I completely remove the
"Undo" part from the BeforeUpdate code for the txtSurveyDate
control, but leave in the "Cancel = True" part, when the
user enters "bad" data, the focus stays on that control, and
leaves the "bad" value in place--why would that trigger the
canned "validation" error? The record is still dirty,
there's an entry (at least in the control on the form) and
the update for that control has been cancelled, right?
Shouldn't the record be just like if there was no
BeforeUpdate code, and the user had just entered data in
that control, but hadn't done anything to move the focus to
the next record?
Click to expand...


Apparently the validation rule checks are not affected by
Cancel = True. If a validation rule is violated then Access
has to cancel the update anyway. I have not tested it, but
your BeforeUpdte event may not even be called in that case.

A messy alternative might be to make the bound date text box
invisible and use an unbound text box for users to enter the
date. At least this gives you a chance to do your thing in
the unbound text box's BeforeUpdate event and only if you do
not cancel it, copy the date to the hidden bound text box
where the validation rule will be checked.
Click to expand...

Well now that's something I hadn't thought of doing! It's
really not all that "messy", and if it gets the job done....
 
croy said:
I am indebted to you for your continued efforts to get my
brain to work. More below...

On Wed, 05 Jan 2011, Marshall Barton wrote:

croy wrote:

Marshall Barton wrote:

croy wrote:

Marshall Barton wrote:

croy wrote:

I have a textbox control (txtSurveyDate) for a date field,
and I've put some code for that control on the BeforeUpdate
event to alert the user if they put in last year's date.

If they *have* put in last year's date, my message box will
alert them, and give them the choice of accepting that date
or cancelling.

The problem is, when the user selects "Cancel", my code
"un-does" and cancels the entire record, and leaves the
focus at the said control (which is not the first control
for the record).

What I would prefer, is when the user selects "Cancel", that
the focus would stay on that control, without further error
messages.

Here's the code:

******
Private Sub txtSurveyDate_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtSurveyDate_BeforeUpdate

If Me.NewRecord Then
If Year([txtSurveyDate]) = Year(Now()) - 1 Then
DoCmd.Beep
Dim Msg, Style, Title, Response
Msg = "If you sure the year you entered is_
correct, click 'OK'. " & vbCrLf &_
"Otherwise, click 'Cancel'"
Style = 1 + 32 + 256
Title = "The year you entered may not be_
correct"
Response = MsgBox(Msg, Style, Title, _
Help, Ctxt)
If Response = 2 Then
Me.Undo
Cancel = True

Me.Undo undoes the entire record. If you want to undo just
one control use:

Me.txtSurveyDate.Undo

Thanks for the reply.

When I do that, I then get the error:

"The value in the field or record violates the validation
rule for the record or field."

And I really don't want to change my validation rule.


If the Me.txtSurveyDate.Undo line is causing that error,
then the value that was previously in the text box was
invalid and could not have been entered. Based on this
logic, I conclude that there is more going on than you've
explained.

Perhaps the DefaultValue does not pass the validation rule
or it does not have a DefaultValue and you have the field's
Required property set to Yes.

That last bit is the case. I was thinking (hoping?) that
undoing the entry, and canceling the BeforeUpdate event
would leave the control as if it had just gotten the focus.

In these cases, your only
options are to either undo the entire record as you
originally were trying to avoid or to not undo the text box
and leave the unacceptable value in the text box for the
user to replace.

I tried remarking out the "undo" part, and leaving the
"cancel" part, but got the same error. When I click away
the error box, the entered value remains, with the cursor
flashing after the last character. Not bad, except I'd
rather the user didn't see the error message.

If I leave the "undo" in there, the result is the same,
except that the entire value entered is selected. This
would be perfect if the user didn't have to deal with the
error message.


How about setting the text box's DefaultValue to Date() so
the Undo should set it back to that?

Not a bad thought, but I'd rather the data-entry clerk see
the "bad" data they had entered (and be able to see what was
wrong), rather than see some date that not what is should
be, and not what they had entered.

If you want to select the entire contents, I think you can
add two more lines:
Me.txtSurveyDate.Undo
Cancel = True
Me.txtSurveyDate.SelStart = 0
Me.txtSurveyDate.SelLength = 99

It seems that as long as the source field is a "required"
field, the "Cancel = True" statement causes the canned
"validation" error message to appear.

Here's what I don't understand: If I completely remove the
"Undo" part from the BeforeUpdate code for the txtSurveyDate
control, but leave in the "Cancel = True" part, when the
user enters "bad" data, the focus stays on that control, and
leaves the "bad" value in place--why would that trigger the
canned "validation" error? The record is still dirty,
there's an entry (at least in the control on the form) and
the update for that control has been cancelled, right?
Shouldn't the record be just like if there was no
BeforeUpdate code, and the user had just entered data in
that control, but hadn't done anything to move the focus to
the next record?
Click to expand...


Apparently the validation rule checks are not affected by
Cancel = True. If a validation rule is violated then Access
has to cancel the update anyway. I have not tested it, but
your BeforeUpdte event may not even be called in that case.

A messy alternative might be to make the bound date text box
invisible and use an unbound text box for users to enter the
date. At least this gives you a chance to do your thing in
the unbound text box's BeforeUpdate event and only if you do
not cancel it, copy the date to the hidden bound text box
where the validation rule will be checked.
Click to expand...

Well now that's something I hadn't thought of doing! It's
really not all that "messy", and if it gets the job done....
Click to expand...

Ok. It's messy. |:-l
 
Alright, we're agreed on our terminology ;-)

But did you get it to work and, if so, does it solve the
problem?
Click to expand...

After mulling over how it would work for new records vs.
modifying existing records, I decided to simply change my
validation rule in the table to not allow dates more than
300 days back from today, or in the future.

I could get bitten if some reason comes up that we'd need to
enter a record for a very old date, but that would be a very
odd case (hasn't happened in the last five years, that I
know of).

This has turned into a ridiculous expenditure of time for
the simple goal of catching entries early in a new year,
where the clerk inadvertently entered the old year. It
seemed like such a simple thing when I first started!

Thanks for your thoughts on this--I'm sure some good will
come from your suggestions.
 
Back
Top