Recordset errors on rsMyRecordset.Delete

[Penny]

A2002, Single User, Windows XP Pro(SP2)

Hi All, I show here code that opens a recordset on a joined table query. The
DAO recordset throws an error at the 'rsRemoveDups.Delete' line. The error
message is '3218 : Could not update; currently locked'. I've hard coded the
SQL just to illustrate the point but it errors the same as when the SQL is
created dynamically from controls.

Why wont it let me delete the current record in the recordset?

----------------------
strRecordSource = "SELECT * FROM (tblCandidates LEFT JOIN
tblCandidatePositionTypesSought ON tblCandidates.CandidateKeyNum =
tblCandidatePositionTypesSought.CandidateKeyNum) LEFT JOIN
tblCandidateIndustryExperience ON tblCandidates.CandidateKeyNum =
tblCandidateIndustryExperience.CandidateKeyNum WHERE
[CandidatePositionTypeSought] Like 'Secretary*' and [Activated] = True ORDER
BY tblCandidates.CandidateKeyNum"

Dim db As DAO.Database
Dim rsRemoveDups As DAO.Recordset
Dim strLastCandidateID As String
Dim intCount As Integer

intCount = 0
Set db = CurrentDb()
Set rsRemoveDups = db.OpenRecordset(strRecordSource, dbOpenDynaset,
dbInconsistent, dbOptimistic)
rsRemoveDups.MoveFirst

If Not rsRemoveDups.BOF Or rsRemoveDups.EOF Then
Do Until rsRemoveDups.EOF = True
strLastCandidateID =
rsRemoveDups.Fields("tblCandidates.CandidateKeyNum").Value
Else
If
rsRemoveDups.Fields("tblCandidates.CandidateKeyNum").Value =
strLastCandidateID Then
strLastCandidateID =
rsRemoveDups.Fields("tblCandidates.CandidateKeyNum").Value
rsRemoveDups.Delete
Else
strLastCandidateID =
rsRemoveDups.Fields("tblCandidates.CandidateKeyNum").Value
End If
End If
intCount = intCount + 1
rsRemoveDups.MoveNext
Loop
End If

 

[Guest]

A2002, Single User, Windows XP Pro(SP2)

Hi All, I show here code that opens a recordset on a joined table query. The
DAO recordset throws an error at the 'rsRemoveDups.Delete' line. The error
message is '3218 : Could not update; currently locked'. I've hard coded the
SQL just to illustrate the point but it errors the same as when the SQL is
created dynamically from controls.

Why wont it let me delete the current record in the recordset?

----------------------
strRecordSource = "SELECT * FROM (tblCandidates LEFT JOIN
tblCandidatePositionTypesSought ON tblCandidates.CandidateKeyNum =
tblCandidatePositionTypesSought.CandidateKeyNum) LEFT JOIN
tblCandidateIndustryExperience ON tblCandidates.CandidateKeyNum =
tblCandidateIndustryExperience.CandidateKeyNum WHERE
[CandidatePositionTypeSought] Like 'Secretary*' and [Activated] = True ORDER
BY tblCandidates.CandidateKeyNum"

Dim db As DAO.Database
Dim rsRemoveDups As DAO.Recordset
Dim strLastCandidateID As String
Dim intCount As Integer

intCount = 0
Set db = CurrentDb()
Set rsRemoveDups = db.OpenRecordset(strRecordSource, dbOpenDynaset,
dbInconsistent, dbOptimistic)
rsRemoveDups.MoveFirst

If Not rsRemoveDups.BOF Or rsRemoveDups.EOF Then
Do Until rsRemoveDups.EOF = True
strLastCandidateID =
rsRemoveDups.Fields("tblCandidates.CandidateKeyNum").Value
Else
If
rsRemoveDups.Fields("tblCandidates.CandidateKeyNum").Value =
strLastCandidateID Then
strLastCandidateID =
rsRemoveDups.Fields("tblCandidates.CandidateKeyNum").Value
rsRemoveDups.Delete
Else
strLastCandidateID =
rsRemoveDups.Fields("tblCandidates.CandidateKeyNum").Value
End If
End If
intCount = intCount + 1
rsRemoveDups.MoveNext
Loop
End If

Penny,

To see if the problem is with the "strRecordSource = ...." statement, create
a new query and paste in the SQL from strRecordSource. Run the query,then
select a duplicate record and try deleting it. If you can delete a record,
the problem is with the code, else the recordset you create in code is not
updatable and you will have to come up with a different way to delete
duplicates.

Problems with the code that I found:

1) Trying to do a .MoveFirst before checking if there are records will cause
an error

2) This line

If Not rsRemoveDups.BOF Or rsRemoveDups.EOF Then

should be

If Not rsRemoveDups.BOF AND NOT rsRemoveDups.EOF Then

is says if you are not at BOF AND you are NOT at EOF there are records

3) In the following line

rsRemoveDups.Fields("tblCandidates.CandidateKeyNum").Value

you cannot have "tblCandidates." The field name "CandidateKeyNum" is a
field in the recordset. The recordset "rsRemoveDups" is a "virtual" table, so
"rsRemoveDups.Fields" IS the table name for the field "CandidateKeyNum".

4) You crossed control structures:

This is bad...

--> IF .... THEN
|
| DO UNTIL <--
| |
--> ELSE |
| |
| LOOP <----
|
--> END IF

Thsi is good:

--> IF .... THEN
|
| DO UNTIL <--
| |
| LOOP <----
--> ELSE |
| |
|
--> END IF


I tried to rewrite you code. It *should* work, but I don't have your tables
so I really couldn't test it.


'---beg code-----
Dim db As DAO.Database
Dim rsRemoveDups As DAO.Recordset
Dim strLastCandidateID As String
Dim intCount As Integer
Dim strSQL As String

strSQL = "SELECT * FROM (tblCandidates "
strSQL = strSQL & " LEFT JOIN tblCandidatePositionTypesSought "
strSQL = strSQL & " ON tblCandidates.CandidateKeyNum = "
strSQL = strSQL & " tblCandidatePositionTypesSought.CandidateKeyNum) "
strSQL = strSQL & " LEFT JOIN tblCandidateIndustryExperience "
strSQL = strSQL & " ON tblCandidates.CandidateKeyNum = "
strSQL = strSQL & " tblCandidateIndustryExperience.CandidateKeyNum "
strSQL = strSQL & " WHERE [CandidatePositionTypeSought] Like
'Secretary*' and [Activated] = True "
strSQL = strSQL & " ORDER BY tblCandidates.CandidateKeyNum"

intCount = 0
Set db = CurrentDb()
Set rsRemoveDups = db.OpenRecordset(strSQL, dbOpenDynaset,
dbInconsistent, dbOptimistic)

If Not rsRemoveDups.BOF And Not rsRemoveDups.EOF Then
'not BOF and not EOF means there are records in recordset
rsRemoveDups.MoveFirst
'get the first CandidateKeyNum
strLastCandidateID = rsRemoveDups.Fields("CandidateKeyNum")
'move to next record
rsRemoveDups.MoveNext

'now ready to loop thru recordset
Do Until rsRemoveDups.EOF = True
If rsRemoveDups.Fields("CandidateKeyNum").Value =
strLastCandidateID Then
'equal
strLastCandidateID = rsRemoveDups.Fields("CandidateKeyNum")
rsRemoveDups.Delete
Else
'NOT equal = get new CandidateKeyNum and start again
strLastCandidateID = rsRemoveDups.Fields("CandidateKeyNum")
End If
intCount = intCount + 1
rsRemoveDups.MoveNext
Loop

End If

'if done then clean up
rsRemoveDups.Close
Set rsRemoveDups = Nothing
Set db = Nothing
'----end code ----