Suppress Access Error Message (1 Viewer)

Steve R.

Retired
Local time
Today, 11:10
Joined
Jul 5, 2006
Messages
4,617
I would like to display my own error message instead of the Access error message which is vague. I have a DATE field. If a user enters an invalid date access gives the message "The value you entered isn't valid for this field". I would like it to say "Please enter a valid date". The code below is set to operate on the "BEFORE UPDATE" event. There appear to be two issues.
1. The Access error message is not suppressed.
2. The error subroutine below never seems to be triggered since I do not get the "Hi" message.
Code:
Private Sub comments_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Comments_BeforeUpdate
    DoCmd.SetWarnings False
    MsgBox "Before Update Event Occured" <=Revised message box to be added after it works.
    Exit Sub
Err_Comments_BeforeUpdate:
    DoCmd.SetWarnings False
    MsgBox "HI" <=Revised message box to be added after it works.
    DoCmd.CancelEvent
    DoCmd.SetWarnings True
    Exit Sub
End Sub

First: Is the "BEFORE UPDATE" event the correct point at which to check the validity of data?
Second: Is this one of the Access messages that simply can't be turned off?
 

boblarson

Smeghead
Local time
Today, 08:10
Joined
Jan 12, 2001
Messages
32,059
You COULD set the validation at table level and display a message of your choosing there.
 

RuralGuy

AWF VIP
Local time
Today, 09:10
Joined
Jul 2, 2005
Messages
13,826
Try this code for grins:
Code:
Private Sub comments_BeforeUpdate(Cancel As Integer)
If MsgBox("Do you want to keep this Date?") = vbNo Then
   Cancel = True
End If
End Sub
It sounds to me like you already have some validation code in the table. I believe you can catch those errors in the OnError event of the form but I'm not positive about that. As a general rule, I put *all* of my validation code in my forms where I have better control of the interaction with the user. Just my $.02.
 

Steve R.

Retired
Local time
Today, 11:10
Joined
Jul 5, 2006
Messages
4,617
Thanks. I can give it a try, the problem is that the "BeforeUpdate" event is being superseded by the Access error message, so the "BeforeUpdate" event does not occur. Theoretically, this is a non-issue since the user is forced to make a correct entry. Just my desire to beat Access into submission, which is a masochistic exercise.

Thanks Bob Larson, I did a quickie experiment with table, it didn't work-but it may if I work with the input mask. Also experimented with the Control's property validation rule and text. Moreover, I ran across some VBA advice on using "fld.ValidationRule = strValidRule" and "fld.ValidationText = strValidText"
which I will have to study some.

Anyway this was the irritant of today, and I have to finish other work so I can go home. So I will put this aside for a while.

PS: I had experimented earlier with "acDataErrorContinue", but that didn't work either. I could have been using it incorrectly though.
 
Last edited:

Steve R.

Retired
Local time
Today, 11:10
Joined
Jul 5, 2006
Messages
4,617
Workaround Found

I believe that my problem was caused by the control being BOUND. By un-bounding it I am able to: 1) eliminate the Access error message and 2) test the control is see if it is a valid date. If the date format is valid, then the field is updated to the new value. The revised working code is below.
Code:
Private Sub comments_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Comments_BeforeUpdate
    If IsDate(Me.comments) Then
            Me.commentstartdate = Me.comments
        Else
            MSG1 = "Please enter a valid date."
            TITLE = "INVALID DATE"
            MsgBox MSG1, vbOKOnly, TITLE
            DoCmd.CancelEvent
        End If
    Exit Sub
Err_Comments_BeforeUpdate:
    Rem no code for now
    Exit Sub
End Sub

PS: Based on testing it appears that the Access error message was triggered by attempting to enter incompatible data with the bound control and not by an Access event. I had assumed that I could "catch" this error with the "before update" event, but I guess that was not the case. By making the control unbound, I get around this issue. Thanks Boblarson and Rural Guy.
 
Last edited:

Micron

AWF VIP
Local time
Today, 11:10
Joined
Oct 20, 2018
Messages
3,476
I realize this is old but I've been trying to help someone elsewhere with the same issue and suggested BeforeUpdate event, being blissfully unaware it wouldn't work. Since I did come across a solution and in the process, found this thread, I thought I'd post the solution I found.

You could use the form DataErr event, trap 2113 and keep the fields bound. That will suppress the system error, and the BeforeUpdate would run if you still need it. Could have a select case block in there for every control name that might be involved rather than write an event (e.g. OnChange) for every applicable control but would probably have to rely on ActiveControl property to do so.
 

Users who are viewing this thread

Top Bottom