Trap invalid value error 2

[48Highlander]

When I enter 31 Sep 2001 into a date field, I get the access error "The value you entered isn't valid for this field."

How do I trap this error?

Bill J

 

[THWatson]

48Highlander
I'm not clear what you want to trap. There are only 30 days in September, so that is what produces the error?

Do you want a message box to come up if the error is a specific error code?

Tom

 

[THWatson]

48Highlander
Is the error # 2113?

You could include Error Handling code on the BeforeUpdate (or maybe on the AfterUpdate) event for the date field, and in the code include something such as...

If Err.Number = 2113 Then
YourMessageBox text
End If

Tom

 

[48Highlander]

Yes I am trying to trap the error caused by there being only 30 days in Sept. I want users to get a more understandable error message.

I put my code in the before update event for the field but the same message appears and the custom error message does not appear.

Are you sure the error number is 2113? And where can I find a listing or eror numbers?

Bill J

 

[THWatson]

Bill
Am I sure that your error is 2113? No, but Error 2113 is one that produces this error message.

When the error produces, the error number should be indicated.

Have you included error handling in your BeforeUpdate event? The error handling part of the code would include something like this...

On Error GoTo Err_YourDateField_BeforeUpdate_Error

Err_YourDateField_BeforeUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure YourDateField_BeforeUpdate of VBA Document Form_YourForm"

If Err.Number = 2113 Then
Take desired action (whatever you want to do)
End If

Don't paste what I have shown. It may not fit exactly your situation. It's an example.

You have to generate a table of error numbers in order to find all of them. If you want me to sent the procedure post back and I can do so.

Tom

 

[THWatson]

Bill
To save you posting back, here's the procedure to get the Errors table.

1. Make a new blank database. Call it Access Errors.
2. Make one table, called tblErrorsAndDescriptions. In that table, have the following two fields...
ErrorNumber Number data typa PrimaryKey No dups
ErrorDescription Memo data type
3. In a new module, paste the following code...

Function sRecordAccessErrorMsg()

Dim ADOcnn As ADODB.Connection
Dim ADOrst As ADODB.Recordset

Dim intCounter As Long
Dim intErrornumber As Long
Dim strErrorText As String

Set ADOcnn = CurrentProject.Connection
Set ADOrst = New ADODB.Recordset

ADOrst.Open "tblErrorsAndDescriptions", ADOcnn, adOpenDynamic, adLockOptimistic

With ADOrst
    For intCounter = 0 To 32767
        .AddNew
        !ErrorNumber = intCounter
        If IsNull(AccessError(intCounter)) Then
            !ErrorDescription = "No Error"
        ElseIf AccessError(intCounter) = "" Then
            !ErrorDescription = "No Error"
        Else
            !ErrorDescription = AccessError(intCounter)
        End If
        .Update
    Next intCounter
End With

MsgBox "Completed enumerating errors to the table."

End Function

4. Run the code in the Immediate window of the module.

Tom

 

[48Highlander]

Here is the code:

Private Sub txtBirthdate_BeforeUpdate(Cancel As Integer)
On Error GoTo err_txtBirthdate_BeforeUpdate

    Dim LatestBirthYear As Integer
    LatestBirthYear = DatePart("yyyy", Date) - 16
    
    If Me.txtBirthdate < #1/1/1914# Or DatePart("yyyy", Me.txtBirthdate) > LatestBirthYear Then
        MsgBox "Invalid date: Date is earlier than 1914 or person is younger than 16", vbInformation + vbOKOnly, "Error"
        Me.txtBirthdate.Undo
        Cancel = True
    End If
    
Exit_txtBirthdate_BeforeUpdate:
    Exit Sub

err_txtBirthdate_BeforeUpdate:

    If Err.Number = 2113 Then
        MsgBox "Invalid date entered", vbOKOnly, "Error"
        Me.txtBirthdate.Undo
    Else
        MsgBox Err.Description
        Resume Exit_txtBirthdate_BeforeUpdate
    End If
    
End Sub

Bill J

 

[THWatson]

Bill
Does that code produce what you want to happen?

Tom

 

[48Highlander]

No I still get the same generic message

Bill J

 

[RoyVidar]

What you've encountered is a form error, not a runtime error. Form errors create those default messages if you don't trap them.

To just substitute the message with your own message, try the forms on error event

[tt]private sub form_error(dataerr as integer, response as integer)
if dataerr = 2113 then
msgbox "your meaningfull message"
response = acdataerrcontinue
end if
end sub[/tt]

but if the issue is to validate, try something like this in the controls before update

[tt]if isdate(me!txtBirthdate) then
' do your current stuff
else
msgbox "wrong date"
cancel=true
endif[/tt]

Note - to avoid the default message in this approach, you'll need acdataerrcontinue in the form error event for dataerr 2113.

But - a simpler approach could perhaps be to use date format and an input mask in the control?

Roy-Vidar

 

[THWatson]

Bill
There you go. Roy-Vidar has the answer for you.

Tom

 

[48Highlander]

Thanks to both of you. How does one learn that this was a form error and not a control error?

I guess I have to keep leaning on really bright folks like you two.

Bill J

 

[RoyVidar]

Thank you thank you...

How one learns - by keep getting those darn errors until one figgers them out;-)

Usually errors occuring by "user activity" on form, moving between controls breaking table level validation, control validation, primary key violation, null error on required fields... you get a form error.

Runtime errors occur as a consequence of coming across an obstacle in code;-) - so typically you'll get lot of the same errors both as form errors and runtime errors, the difference is what triggers them and how to deal with them.

Roy-Vidar

 

[THWatson]

Bill
Thanks. Let me know if you run into any hitches with the generation of the Access Error table.

Tom