Using Form_Error to trap Data Errors

[John Pritchard]

I'm testing various ideas to make Access forms work elegently - eliminate
meaningless default messages etc. One idea is to set values in the table defn
- required = yes etc - then trap data errors in the forms Form Error section.
Questions:-
1. Where can I findout what the DataErr numbers mean
2. What does response do

Here's my test code so for - I've been guessing but it seems to work
reasonable except when I delete the PrimKey (indexed no dups in the table
def) on an existing record when I can't move from the field for some reason.

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr

Case 3022
MsgBox "Primkey already exists"
DoCmd.GoToControl "Primkey"

Case 3314
If IsNull(Me.PrimKey) Then
MsgBox "PrimKey can not be null"
DoCmd.GoToControl "Primkey"
ElseIf IsNull(Me.Fldone) Then
MsgBox "Fldone can not be null"
DoCmd.GoToControl "Fldone"
End If
Case 2169
MsgBox "Changes to this record can not be saved"
Case Else
MsgBox "DataErr was " & DataErr
End Select

Response = vbCancel

End Sub

Thanks for any help / guidance - I think I've lots to still learn but need
pointing in the right direction!

 

[Allen Browne]

I'm not sure there is a list of the DataErr values.
Some common ones:
2113 wrong type of data
3024 file missing (e.g. if network breaks)
3201 related record required (for referential integrity)

The Response argument is provided so you can set it to tell Access how to
respond to the error. Typically you use:
Response = acDataErrContinue
where you want to suppress the built in message (e.g. to display your own
error), and:
Response = acDataErrDisplay
where you want Access to display its own error message.

It can be fun trying to figure out which control triggered the error. You
may be able to figure out the source field from
Form.ActiveControl.ControlSource, but it may not be that control.

A simple way to get a custom message for a "requried" field is to set its
Required property to No in table design. Set its Validation Rule to:
Is Not Null
and you can type whatever custom message you like into the field's
Validation Text.

HTH

 

[Mark]

Allen,
Years ago, I came across a little VBA code that loops through the errors
collection and writes them (along with the description) to a table. I can't
remember where I put it, but you should be able figure it out using
err.number and err.description. I'll look around for it and if you guys
haven't figured it out, I'll post it.

 

[Allen Browne]

You may be thinking of looping through the AccessError(), e.g.:
? AccessError(3021)

These are also listed in JetErr40.chm. For A2003, that's likely to be in:
"C:\Program Files\Common Files\Microsoft Shared\OFFICE11\1033\

Not sure if the are the same as the DataErr values.

 

[Mark]

You're right, Allen. I did a search and found a post by German Turayev at
the UtterAccess Discussions Forum. If you change "For lngCode = 0 To 3500"
to "For lngCode = 0 To 65000" you get a longer list of error messages. You
also need to set a reference to the " Microsoft ADO Ext. 2.6 for DDL and
Security " library. Here's a copy/paste from German's post:


The following procedure creates a table containing many of the error codes
and strings used or reserved by Microsoft Access and by the Microsoft Jet
database engine. Not all error codes are included in the resulting table, as
some exist outside the range of error codes evaluated by this procedure (0
to 4500).

Function AccessAndJetErrorsTable() As Boolean
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset, lngCode As Long
Dim strAccessErr As String

Const conAppObjectError = "Application-defined or object-defined error"
On Error GoTo Error_AccessAndJetErrorsTable

Set cnn = CurrentProject.Connection
' Create Errors table with ErrorNumber and ErrorDescription fields.
tbl.Name = "AccessAndJetErrors"
tbl.Columns.Append "ErrorCode", adInteger
tbl.Columns.Append "ErrorString", adLongVarWChar

Set cat.ActiveConnection = cnn
cat.Tables.Append tbl
' Open recordset on Errors table.
rst.Open "AccessAndJetErrors", cnn, adOpenStatic, adLockOptimistic
' Loop through error codes.
For lngCode = 0 To 3500
On Error Resume Next
' Raise each error.
strAccessErr = AccessError(lngCode)
DoCmd.Hourglass True
' Skip error numbers without associated strings.
If strAccessErr <> "" Then
' Skip codes that generate application or object-defined errors.
If strAccessErr <> conAppObjectError Then
' Add each error code and string to Errors table.
rst.AddNew
rst!ErrorCode = lngCode
' Append string to memo field.
rst!ErrorString = strAccessErr
rst.Update
End If
End If
Next lngCode
' Close recordset.
rst.Close
DoCmd.Hourglass False
RefreshDatabaseWindow
MsgBox "Access and Jet errors table created."
AccessAndJetErrorsTable = True

Exit_AccessAndJetErrorsTable:
Exit Function

Error_AccessAndJetErrorsTable:
MsgBox Err & ": " & Err.Description
AccessAndJetErrorsTable = False
Resume Exit_AccessAndJetErrorsTable
End Function

 

[Allen Browne]

That seems like a long way around to get there.
This kind of thing might be easier:

Function ShowErrors()
Dim lng As Long
For lng = 2000 To 2010
Debug.Print AccessError(lng)
Next
End Function

 

[Mark]

I agree, but you run it once and you get a table of error numbers and
corresponding descriptions that you can bring with you and use whenever,
wherever and however you want. At least it was a nice exercise to distract
me from my hum-drum existence...

 

[John Pritchard]

Thanks for all your help!! - I'm looking into getting hold of the error
source today. The idea is to keep field names on the table and in the form
the same so that I can have completely generic code for my err handler -
like:-
Case 3314 ' Null value in none nullable field
source = Getsourcename()
MsgBox source & " can not be null"
DoCmd.GoToControl source