write conflict error access 2002

[colinbell]

I've got a write conflict error when I click previous menu. it was ok before which leads me to think it is some new code I've included causing me the problem. the form runs the code below, calling functions which i've also included further on down.
I think it is the line
DoCmd.RunSQL ("UPDATE tblactions set validated = false where saref = '" & Me.saref & "'")
which is causing the problem
sorry about all the code - any help appreciated
ian

If Me.strFormAccess = "A" Then
If (CheckFields = True) And (Not IsNull(Me.txtCompleted) Or Me.txtCompleted <> "") Then
' contra1
Me.validated.locked = False
AllocateType
Me.validated.locked = True
DoCmd.OpenForm "frmSummaryTRAIN", , , "[Type] = 'TRAIN' and [directorate] = '" & [CurrentUser] & "' and [new_service] = '" & AuditGlobal & "'"
Forms!frmSummaryTRAIN!strsect = AuditGlobal
isclosing = True
DoCmd.Close acForm, Me.Name

AllocateType is the new function I've included which is shown below

Public Function AllocateType()

Dim db As DAO.Database
Dim rst, rst1 As Recordset
Dim str, str1 As String
Dim current, current1, current2, current3, checkcount As Integer

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * from tblTRAININGserviceLINKS where Ref = '" & Me.saref & "'"
DoCmd.SetWarnings False
AllocateRefs
AllocateRefs1

checkcount = 0

Set db = CurrentDb
'str = "SELECT * from tblPAIS_links where SECT_PAIS = '" & Me.saref & "'"
str = "SELECT * from tblTRAININGserviceLINKS where Ref = '" & Me.saref & "'"
Set rst = db.OpenRecordset(str)
If Not rst.EOF Then
With rst
.MoveFirst
Do Until rst.EOF
str1 = "SELECT * from tblSECT_funding where saref = '" & rst("linkedRef") & "'"
Set rst1 = db.OpenRecordset(str1)
If Not rst1.EOF Then
checkcount = 1
With rst1
.MoveFirst
Do Until rst1.EOF
If (rst1("revenue_existing") = True) Or (rst1("capital_existing") = True) Then
current = 1
Else
current = 0
End If
.MoveNext
current1 = current1 + current
Loop
End With

If current1 > 0 Then
current2 = 1
Else
current2 = 0
End If

Else
End If

.MoveNext
Loop
End With

If checkcount = 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = true where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = 0 where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
ElseIf (checkcount = 1 And current2 = 1) Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = false where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = 1 where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
ElseIf (checkcount = 1 And current2 = 0) Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = false where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = 2 where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
ElseIf (checkcount = 1 And current2 = Null) Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = true where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = 0 where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
End If

Else
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = true where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = 0 where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
End If

End Function

AllocateRefs and AllocateRefs1 are 2 further functions shown below

Public Function AllocateRefs()

Dim db As DAO.Database
Dim rst, rst1, rst2 As Recordset
Dim str, str1, str2, str3, str4 As String
Dim current, current1, current2, current3, checkcount As Integer

Set db = CurrentDb
str = "SELECT * from tblPAIS_links where SECT_PAIS = '" & Me.saref & "'"
Set rst = db.OpenRecordset(str)
If Not rst.EOF Then
With rst
.MoveFirst
Do Until rst.EOF
str1 = "select * from tblactions where saref = '" & rst("PAIS_SECT") & "'"
Set rst1 = db.OpenRecordset(str1)
If Not rst1.EOF Then
With rst1
.MoveFirst
Do Until rst1.EOF
If rst1("Type") = "Service" Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblTRAININGserviceLINKS (Ref, linkedref, PAISref) values('" & Me.saref & "' , '" & rst1("saref") & "' , '" & rst1("saref") & "')"
DoCmd.SetWarnings False
Else
If rst1("Type") = "PAIS" Then
str2 = "select PAIS_SECT, SECT_PAIS from tblPAIS_links where Ref= " & rst1("Ref")
Set rst2 = db.OpenRecordset(str2)
If Not rst2.EOF Then
With rst2
.MoveFirst
Do Until rst2.EOF
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblTRAININGserviceLINKS (Ref, LinkedRef, PAISref) values('" & Me.saref & "' , '" & rst2("PAIS_SECT") & "' , '" & rst2("SECT_PAIS") & "')"
DoCmd.SetWarnings False
.MoveNext
Loop
End With
Else
End If
Else
End If
End If
.MoveNext
Loop
End With
Else
End If
.MoveNext
Loop
End With
Else
End If

End Function


Public Function AllocateRefs1()

Dim db As DAO.Database
Dim rst, rst1, rst2 As Recordset
Dim str, str1, str2, str3, str4 As String
Dim current, current1, current2, current3, checkcount As Integer

Set db = CurrentDb
str = "SELECT * from tblPAIS_links where SECT_PAIS = '" & Me.saref & "'"
Set rst = db.OpenRecordset(str)
If Not rst.EOF Then
With rst
.MoveFirst
Do Until rst.EOF
str1 = "select * from tblactions where saref = '" & rst("PAIS_SECT") & "'"
Set rst1 = db.OpenRecordset(str1)
If Not rst1.EOF Then
With rst1
.MoveFirst
Do Until rst1.EOF
If rst1("Type") = "PAIS" Then
str2 = "SELECT PAIS_ref, SECT_ref from tbladds where PAIS_ref = '" & rst1("saref") & "'"
Set rst2 = db.OpenRecordset(str2)
If Not rst2.EOF Then
With rst2
.MoveFirst
Do Until rst2.EOF
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblTRAININGserviceLINKS (Ref, linkedref, PAISref) values('" & Me.saref & "' , '" & rst2("SECT_ref") & "' , '" & rst2("PAIS_ref") & "')"
DoCmd.SetWarnings False
.MoveNext
Loop
End With
Else
End If
Else
End If
.MoveNext
Loop
End With
Else
End If
.MoveNext
Loop
End With
Else
End If

End Function

 

[colinbell]

docmd.runcommand accmdsaverecord seems to have done the trick before the SQL update statements are run

very strange !?!