Application.setoption???

[Joe_dn]

I have a form that writes data to a table, I have added the following:-

Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Action Queries", False

This means that the user doesnt have to agree to amend the table when updating. Sometimes this update invalidates a primary key and if the wrong option is selected then the whole system goes into debug, is there an option set to stop this from happening??

Basically exit the sub with out the error or the update???

 

[Oldsoftboss]

You need to add error handling to your code. Look at the code created by the cmdbutton wizard.

Most times it is good to trap the error and display your own custom message.

If you are really sure you can put the following....

Private Sub cmdButtonName_Click()
On Error Resume Next

...your code here

Exit Sub

Dave

PS..

If it is the delete warning that is being displayed, this can be turned off in the database under Tools -> Options -> Edit/Find then Confirm

or you can use

Docmd.SetWarnings False

Docmd.SetWarnings True

 

[Joe_dn]

Can you do the same for a run time error, that is, set an option so it just doesnt display it???

 

[Pat Hartman]

Here's some code that traps for runtime errors. The value assigned to the Response argument specifies whether the system error message is to be displayed or not. If you read the code, you'll see that two values are assigned - acDataErrContinue (which continues without displaying the system message) and acDataErrDisplay (which displays the system message).

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
    Case 2113
        MsgBox "The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or an invalid date.", vbOKOnly
        Response = acDataErrContinue
    Case 2237
        MsgBox "You must choose a Tool from the list.", vbOKOnly
        Me.Undo
        Response = acDataErrContinue
    Case 3022
        MsgBox "This Tool has already been mapped to this Activity.", vbOKOnly
        Me.Undo
        Response = acDataErrContinue
    Case Else
        MsgBox "form error " & DataErr
        Response = acDataErrDisplay
End Select
End Sub

Here's a sub that will create a table will all the Access error messages. I find it helpful to have around. The system messages don't display the error number but you need to find the number in order to trap for it.

Public Sub AccessAndJetErrorsADO()
'Add in References MS ADO 2.7 Ext for DLL and Security
'Then paste code:

Dim cat As New ADOX.Catalog 'Data base 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 error
tbl.Name = "AccessAndJetErrorsADO"
tbl.Columns.Append "ErrorCode", adInteger
tbl.Columns.Append "ErrorString", adLongVarWChar

Set cat.ActiveConnection = cnn
cat.Tables.Append tbl
rst.Open "AccessAndJetErrorsADO", cnn, adOpenStatic, adLockOptimistic
For lngCode = 0 To 5000
    On Error Resume Next
    strAccessErr = AccessError(lngCode)
    DoCmd.Hourglass True
    If strAccessErr <> "" Then
        If strAccessErr <> conAppObjectError Then
            rst.AddNew
            rst!ErrorCode = lngCode
            rst!ErrorString = strAccessErr
            rst.Update
        End If
    End If
Next lngCode
rst.Close
DoCmd.Hourglass False
RefreshDatabaseWindow
MsgBox "Access and Jet errors table created."

Exit_accessAndJetErrorsTable:
Exit Sub
Error_AccessandJetErrorsTable:
MsgBox Err & ": " & Err.Description
'AccessAndJetErrorsTable = False
Resume Exit_accessAndJetErrorsTable
End Sub