Unable to edit record (1 Viewer)

[Malcy]

Another wee glitch. I am trying to edit a DAO recordset that is correctly Dimmed and Set but when I try to run it then it falls over.

    Dim rstEc As DAO.Recordset
Dim rstAct As DAO.Recordset

' Define constants
    Const CR = vbCrLf & vbCrLf
    Const QUOTE = """"
    
' Set the connection
    Set dbs = CurrentDb
    Set rstAct = dbs.OpenRecordset("tblActivityLog")
    Set rstEc = dbs.OpenRecordset("tblEvCancel")

The message is "The Microsoft Office Access database engine stopped the process because you and another user are attempting to change the same data at the same time.
The actual bit of code where it falls is at rstEc.Edit

' Flag the first record
            rstEc.MoveLast
            rstEc.MoveFirst
            
            If Not rstEc.EOF Then
                rstEc.Edit
                    rstEc!bEc = -1
                rstEc.Update
            End If

Just in case it helps a more extended extract of the code is here.

' Add transaction record to tblActivityLog
        rstAct.AddNew
            rstAct!dtmAl = Now
            rstAct!lngAl = 205
            rstAct!lngAlOp = intOp
            rstAct!lngAlEvent = Me.txtEbEv
        rstAct.Update
    
        Exit Sub
    Else

' Add transaction record to tblActivityLog
        rstAct.AddNew
            rstAct!dtmAl = Now
            rstAct!lngAl = 207
            rstAct!lngAlOp = intOp
            rstAct!lngAlEvent = Me.txtEbEv
        rstAct.Update

' Clear down tblEvCancel
        dbs.Execute "DELETE * FROM tblEvCancel"
        
' Add transaction record to tblActivityLog
        rstAct.AddNew
            rstAct!dtmAl = Now
            rstAct!lngAl = 210
            rstAct!lngAlOp = intOp
            rstAct!lngAlEvent = Me.txtEbEv
        rstAct.Update
        
' Append valid records to tblTmpEvCancel
        DoCmd.SetWarnings False
            strQryDef = "qappTrEvCancel"
                DoCmd.OpenQuery strQryDef
        DoCmd.SetWarnings True
        
' Add transaction record to tblActivityLog
        rstAct.AddNew
            rstAct!dtmAl = Now
            rstAct!lngAl = 211
            rstAct!lngAlOp = intOp
            rstAct!lngAlEvent = Me.txtEbEv
        rstAct.Update
        
' Identify number of bookings to cancel
        If DCount("lngEcC", "tblEvCancel") = 0 Then
            
' Advise user
            MsgBox "There are no bookings to be cancelled for this event. The event itself will still be flagged as cancelled.", vbOKOnly, "No records to process"
            
' Update event record to show cancelled status
            rstEv.FindFirst "[lngEv] = " & Me.txtEbEv & ""
            
            If Not rstEv.NoMatch Then
                rstEv.Edit
                    rstEv!lngEvStatus = 1
                    rstEv!dtmEvLu = Now()
                    rstEv!lngEvOp = intOp
                rstEv.Update

' Add transaction record to tblActivityLog
                rstAct.AddNew
                    rstAct!dtmAl = Now
                    rstAct!lngAl = 208
                    rstAct!lngAlOp = intOp
                    rstAct!lngAlEvent = Me.txtEbEv
                rstAct.Update
            Else
            
' Advise user
                MsgBox "Record not found", vbCritical, "No match"
                
' Add transaction record to tblActivityLog
                rstAct.AddNew
                    rstAct!dtmAl = Now
                    rstAct!lngAl = 209
                    rstAct!lngAlOp = intOp
                    rstAct!lngAlEvent = Me.txtEbEv
                rstAct.Update
            End If

' Advise user
            MsgBox "The event has now been cancelled. There were no participants to notify.", vbInformation, "Confirmation"

' Add transaction record to tblActivityLog
            rstAct.AddNew
                rstAct!dtmAl = Now
                rstAct!lngAl = 206
                rstAct!lngAlOp = intOp
                rstAct!lngAlEvent = Me.txtEbEv
            rstAct.Update
    
' Close the form
            stDocName = "frmTrEvCancel"
                DoCmd.Close acForm, stDocName, acSaveNo
               
            Exit Sub
        End If
                    
' Flag the first record
            rstEc.MoveLast
            rstEc.MoveFirst
            
            If Not rstEc.EOF Then
                rstEc.Edit
                    rstEc!bEc = -1
                rstEc.Update
            End If

I did think it may have been due to the table being a local table so I have moved it off the the BE and linked it but same problem.
Sorry but stumped on this one. Anyone got any ideas
Thanks

Malcy

 

[James Dudden]

What versions of Access are all your tables/database in?

Have you tried compact/repairing everything?

 

[Malcy]

Hi and thanks for getting back
All in Access 2007 .accdb files (front and back end)
Have gone through the "corruption" route and happy that this is not the case. It is something specific to do with that rstEc recordset.

Currently also exploring other ways I can do everything I need to do to see if that is easier.

 

[James Dudden]

Just wondering (only because I never use them) why you are using a DAO recordset?

Also you could try and add what type or recordset you wanrt i.e. Set rstEc = dbs.OpenRecordset("tblEvCancel",dbOpenDynaset)

 

[Malcy]

OK, an update following further exploration. Prior to trying to edit the recordset I had set up a procedure to clear down a table using a SQL statement and then append valid records using an append query.
I eventually twigged that this was in some way locking the table that was used to create the recordset.
I have now changed the procedure to use a make table query and so far it appears to be behaving. Perhaps this may help someone else in the future.

As to DAO over ADO well I started off about ten years ago using ADO but about four years ago I felt everything was moving from ADO to DAO, and even read somewhere that Jet was not going to support ADO in future versions, so I switched from ADO to DAO. It was a bit of a learning curve but I now look at earlier databases I coded in ADO and find them horrible to work with since DAO gives you much more flexibility and also uses less code.
Do not know what current status of ADO vs DAO is but I seem to recall DAO is native for Acc2007.
Anyway that is why I use it. Thanks for the suggestions.
Best wishes

 

[gemma-the-husky]

maybe its nothing to do with that code

are you calling this sub from a form. is it possible that the record is genuninely dirty from within your form? maybe you have some logic in the current event that sets some field, and thereby "dirties" the record

if you dont have recordselectors turnerd on, turn them on, and see if the black triangle becomes a pencil.

 

[Malcy]

Thanks but I do think it was to do with the append query since the code now runs as sweet as a tin of syrup!
Not sure if that is the right analogy but I am happy as Punch now!