Run-time error '3020': Update or CancelUpdate without AddNew or Edit (1 Viewer)

[gianniskar]

hi.I have my access tables to sql server and i have this code to "log" forms or reports when closing or opening in sql server.This function working when table is on local pc but when is in sql server and i am trying to close form i have this error "Run-time error '3020': Update or CancelUpdate without AddNew or Edit"

Function LogAction(obj As Object, Optional LastID As Long)
    With CurrentDb.OpenRecordset("tblLog", dbOpenDynaset, dbSeeChanges)
    
        If LastID Then
            LastID = IIf(obj.Tag <> vbNullString, obj.Tag, -1)
            obj.Tag = vbNullString
            .MoveFirst
            .FindFirst "LogID = " & LastID
    '
            If Not .NoMatch Then
                .Edit
                .Fields("CloseDateTime") = Format(Now, "yyyy-MM-dd hh:mm:ss")
            End If
        Else
            .AddNew
            LastID = Nz(.Fields("LogID"), 0)
            obj.Tag = LastID
            
            .Fields("OpenDateTime") = Format(Now, "yyyy-MM-dd hh:mm:ss")
            .Fields("DocName") = obj.name
            .Fields("ComputerName") = Environ("COMPUTERNAME")
            .Fields("WinUser") = Environ("USERNAME")
            .Fields("AppUser") = Application.CurrentUser
            
        End If
        
        .Update
        .Close
    End With
End Function

Private Sub Form_Close()
    LogAction Me, 1
End Sub

Private Sub Form_Load()
   LogAction Me, 0
End Sub

 

[arnelgp]

Function LogAction(obj As Object, Optional LastID As Long)
    Dim db As DAO.Database
    dim rs As DAO.Recordset
    dim bm as Variant
    Set db = DbEngine.WorkSpace(0).Databases(0)
   
        If LastID > 0 Then
            LastID = IIf(obj.Tag <> vbNullString, obj.Tag, -1)
            obj.Tag = vbNullString
            db.Execute "Update tblLog Set CloseDateTime = " & Format(Now, "yyyy-MM-dd hh:mm:ss") & " Where LogID=" & LastID, dbFailOnError
        Else
               set rs =db.OpenRecordset("select * from tblLog Where (1=0);")
            rs.AddNew
            rs("OpenDateTime") = Format(Now, "yyyy-MM-dd hh:mm:ss")
            rs("DocName") = obj.name
            rs("ComputerName") = Environ("COMPUTERNAME")
            rs("WinUser") = Environ("USERNAME")
            rs("AppUser") = Application.CurrentUser
            rs.Update
            bm= rs.LastModified
            rs.Bookmark = bm
            obj.Tag = rs!LogID
            rs.Close
            Set rs = Nothing
        End If
    set db = Nothing
End Function

 

[Minty]

I would try assigning your recordset to a recordset object.

Using the same syntax works for many instances in a number of my apps.

 

[gianniskar]

Function LogAction(obj As Object, Optional LastID As Long)
    Dim db As DAO.Database
    dim rs As DAO.Recordset
    dim bm as Variant
    Set db = DbEngine.WorkSpace(0).Databases(0)
   
        If LastID > 0 Then
            LastID = IIf(obj.Tag <> vbNullString, obj.Tag, -1)
            obj.Tag = vbNullString
            db.Execute "Update tblLog Set CloseDateTime = " & Format(Now, "yyyy-MM-dd hh:mm:ss") & " Where LogID=" & LastID, dbFailOnError
        Else
               set rs =db.OpenRecordset("select * from tblLog Where (1=0);")
            rs.AddNew
            rs("OpenDateTime") = Format(Now, "yyyy-MM-dd hh:mm:ss")
            rs("DocName") = obj.name
            rs("ComputerName") = Environ("COMPUTERNAME")
            rs("WinUser") = Environ("USERNAME")
            rs("AppUser") = Application.CurrentUser
            rs.Update
            bm= rs.LastModified
            rs.Bookmark = bm
            obj.Tag = rs!LogID
            rs.Close
            Set rs = Nothing
        End If
    set db = Nothing
End Function

thanks for help.I have error "method or data not found" in this line

Set db = DBEngine.Workspace(0).Databases(0)

 

[arnelgp]

Set db = DBEngine.Workspaces(0).Databases(0)

 

[gianniskar]

Set db = DBEngine.Workspaces(0).Databases(0)

ok i did that.Now i have 2 errors

1) you must use "dbseechanges" ....I tried with that "set rs =db.OpenRecordset("select * from tblLog Where (1=0);",dbseechanges) but not fixed
2)error "3075".Missing operator "2020-08-11 12:48:27" in this line "db.Execute "Update tblLog Set CloseDateTime = " & Format(Now, "yyyy-MM-dd hh:mm:ss") & " Where LogID=" & LastID, dbFailOnError"

 

[arnelgp]

1. set rs =db.OpenRecordset("select * from tblLog Where (1=0);", dbOpenDynaset, dbseechanges)
2. db.Execute "Update tblLog Set CloseDateTime = '" & Format(Now, "yyyy-MM-dd hh:mm:ss") & "' Where LogID=" & LastID

 

[gianniskar]

thanks but after put these code you tell me i have error again "you must use "dbseechanges" at this line now "db.Execute "Update tblLog Set CloseDateTime = '" & Format(Now, "yyyy-MM-dd hh:mm:ss") & "' Where LogID=" & LastID" and add db seechanges at the end of line

db.Execute "Update tblLog Set CloseDateTime = '" & Format(Now, "yyyy-MM-dd hh:mm:ss") & "' Where LogID=" & LastID,dbseechanges

Now its working as i want.
Can you tell me why first code i wrote in post didnt work?Can you any change in this code from my first post i wrote?

Thanks

 

[arnelgp]

see post#3

 

[gianniskar]

I would try assigning your recordset to a recordset object.

Using the same syntax works for many instances in a number of my apps.

Can you explain me about that?

 

[gianniskar]

see post#3

ok.Another question.Can i clear logs ? i mean i want log for 1 year then clear logs and start again log from start

Maybe i am thinking wrong....