Cannot figure out how to get Undo to worker properly using a msgBox()

[CES]

Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

All,
How do you undo a required date field based on a message box response. The error message that I am receiving is:

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

Private Sub DateOfInvoice_BeforeUpdate(Cancel As Integer)
Dim objMsgBox As Integer

objMsgBox = MsgBox("Add an Invoice for: " & vbCrLf & "Year - " & DatePart("YYYY", Me.DateOfInvoice.Value, vbLongDate) & " ?", vbYesNo, "Continue")

If objMsgBox = vbYes Then
MsgBox ("Do something")
Else
Me.DateOfInvoice.Undo
Cancel = True
'Ideally I would like the field to go back to an empty state as opposed to the value just being highlighted.
'Me.DateOfInvoice.SetFocus
'Me.DateOfInvoice.Value = null or ""
End If
End Sub

Any thoughts would be greatly appreciated. Thanks in advance.- CES

 

[ruralguy via AccessMonster.com]

A couple of things. If you cancel the BeforeUpdate event of a control then
the focus remains on the control and a SetFocus is unnecessary. Assuming the
control is bound to a Date field then the UnDo returns the field to what it
was before the user changed anything. A Date field is really a fp number
field so what validation do you have in the table for this field?

 

[Peter Yang [MSFT]]

Hello Christopher,

I think this is a product limitation. A run-time error will occur if you
attempt to modify the control value inside a BeforeUpdate event. Generally
we use BeforeUpdate event to perform validations.You can refer to:

BeforeUpdate Event [Access 2003 VBA Language Reference]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/htm
l/acevtBeforeUpdate_HV03079783.asp

You may want to use Exit or Lostfocus event to do the job to see if could
meet your requirement. If you have any further questions, please feel free
to let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[CES]

Hello Christopher,

I think this is a product limitation. A run-time error will occur if you
attempt to modify the control value inside a BeforeUpdate event. Generally
we use BeforeUpdate event to perform validations.You can refer to:

BeforeUpdate Event [Access 2003 VBA Language Reference]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/htm
l/acevtBeforeUpdate_HV03079783.asp

You may want to use Exit or Lostfocus event to do the job to see if could
meet your requirement. If you have any further questions, please feel free
to let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Peter,
You're absolutely right... the problem was occurring on two fronts number one I was trying to reset a required table field to a null value which it will not allow me to do once an initial value has been added to the field. Secondly while I am attempting to validate the field you're absolutely right that resetting the value will not work unless you do the testing in lostFocus or Exit event's.

As always thank you ever so much for your help. - CES

 

[Peter Yang [MSFT]]

Hello Christopher,

My pleasure.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support


=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================