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.
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
Just in case it helps a more extended extract of the code is here.
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
Code:
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
Code:
' Flag the first record
rstEc.MoveLast
rstEc.MoveFirst
If Not rstEc.EOF Then
rstEc.Edit
rstEc!bEc = -1
rstEc.Update
End If
Code:
' 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
Sorry but stumped on this one. Anyone got any ideas
Thanks
Malcy