CODEWITHSUNDEEP
databasevbams-access
CasWalker
CasWalker

Reputation: 61

Update or CancelUpdate without AddNew or Edit - Access error

I have built a database that I get an occasional error in.

I have a bound form in split form/datasheet view. Occasionally when updating records, I get the "Update or CancelUpdate without AddNew or Edit" error when moving to a new record. This will happen in either the single record form, or on the datasheet.

It does not happen every time the record is saved. Maybe 1 out of 20 or 30 times.

I do have an AuditTrail built in, which is my only guess as to what may be causing the problem.

The VBA on the form:

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Call AuditChanges("ApptID", "DELETED PRIOR RECORD")
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Call AuditChanges("ApptID", "NEW")
Else
    Call AuditChanges("ApptID", "EDIT")
End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
If Status = acDeleteOK Then Call AuditChanges("ApptID", "DELETE BUTTON HIT")
End Sub

The AuditTrail code is:

Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM AuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
 strUserID = Environ("USERNAME")
Select Case UserAction
    Case "EDIT"
        For Each ctl In Screen.ActiveControl.Parent.Controls
            If ctl.Tag = "Audit" Then
                If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                    With rst
                        .AddNew
                        ![DateTime] = datTimeCheck
                        ![UserName] = strUserID
                        ![FormName] = Screen.ActiveControl.Parent.Name
                        ![Action] = UserAction
                        ![RecordID] = Screen.ActiveControl.Parent.Controls(IDField).Value
                        ![FieldName] = ctl.ControlSource
                        ![OldValue] = ctl.OldValue
                        ![NewValue] = ctl.Value
                        .Update
                    End With
                End If
            End If
        Next ctl
    Case Else
        With rst
            .AddNew
            ![DateTime] = datTimeCheck
            ![UserName] = strUserID
            ![FormName] = Screen.ActiveControl.Parent.Name
            ![Action] = UserAction
            ![RecordID] = Screen.ActiveControl.Parent.Controls(IDField).Value
            .Update
        End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub

If the error doesn't involve the VBA code, I have no idea what the problem could be.

Upvotes: 1

Views: 4263

Answers (2)

CasWalker
CasWalker

Reputation: 61

Turns out that the problem didn't seem to have any issues with the AuditTrail code.

There is a combobox that was getting hung up occasionally when moving to a new record.

I added the code below to the 'On Exit' event for the field, and I haven't seen the error come up since.

    If Me.Dirty Then
        Me. Dirty = False
    End If

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8404

I concur with dbmitch; adding some extra info to your error message would be a great help.

In addition, if that doesn't get you exactly what you want, you can also implement the little-known ERL function. Most people don't even know that Access can trap at the line level if they add line numbers (are you old enough to remember Basic?) to their code.

So, something like:

Sub AuditChanges(IDField As String, UserAction As String)
10  On Error GoTo AuditChanges_Err

20  Dim cnn As ADODB.Connection
30  Dim rst As ADODB.Recordset
40  Dim ctl As Control
50  Dim datTimeCheck As Date
60  Dim strUserID As String

70  Set cnn = CurrentProject.Connection
80  Set rst = New ADODB.Recordset
etc...

And then you could change your error handler to be something like:

400  MsgBox "UserAction: " & UserAction & vbCrLf & _
        "IDField: " & IDField & vbCrLf & _
        "Error Line: " & Erl & vbCrLf & _
        "Error: (" & Err.Number & ") " & Err.Description, vbCritical, "ERROR!"

Upvotes: 1

Related Questions