Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1064
  • Last Modified:

Run-time error '3167': Record is deleted

I am trying to execute a loop that deletes records based on certain criteria. My intent is to go to the end of the recordset, store a concatenation of 2 field values in a variable, move to the previous record and store the same two field values in another variable and compare the two. If they are the same, then delete the current record. Then repeat the process until you get to the beginning of the recordset. But I get an error "Run-time error '3167': Record is deleted". I assume this is because it is trying to store the field values of a deleted record. How do I wok around this?
Set qd = db.CreateQueryDef("ReviewStats", sql)
Set td = db.CreateTableDef("TempReviewStats")
td.Fields.Append td.CreateField("Role", dbText, 10)
td.Fields.Append td.CreateField("Protocol", dbText, 10)
td.Fields.Append td.CreateField("Reviewer", dbText, 50)
td.Fields.Append td.CreateField("RecDat", dbDate, 20)
td.Fields.Append td.CreateField("ComplDat", dbDate, 20)
 
db.TableDefs.Append td
 
db.Execute ("INSERT INTO TempReviewStats SELECT * FROM ReviewStats;")
 
Set rs = td.OpenRecordset
 
rs.MoveLast
 
Do Until rs.BOF
    dtTest1 = Trim(rs!Reviewer) & Trim(rs!RecDat)
    rs.MovePrevious
    dtTest2 = Trim(rs!Reviewer) & Trim(rs!RecDat)
    If dtTest1 = dtTest2 Then
        rs.Delete
    End If
Loop

Open in new window

0
NashVegas
Asked:
NashVegas
  • 3
  • 2
1 Solution
 
Chuck WoodCommented:
You need to move previous again to skip over the deleted record.
Set qd = db.CreateQueryDef("ReviewStats", sql)
Set td = db.CreateTableDef("TempReviewStats")
td.Fields.Append td.CreateField("Role", dbText, 10)
td.Fields.Append td.CreateField("Protocol", dbText, 10)
td.Fields.Append td.CreateField("Reviewer", dbText, 50)
td.Fields.Append td.CreateField("RecDat", dbDate, 20)
td.Fields.Append td.CreateField("ComplDat", dbDate, 20)
 
db.TableDefs.Append td
 
db.Execute ("INSERT INTO TempReviewStats SELECT * FROM ReviewStats;")
 
Set rs = td.OpenRecordset
 
rs.MoveLast
 
Do Until rs.BOF
    dtTest1 = Trim(rs!Reviewer) & Trim(rs!RecDat)
    rs.MovePrevious
    dtTest2 = Trim(rs!Reviewer) & Trim(rs!RecDat)
    If dtTest1 = dtTest2 Then
        rs.Delete
        rs.MovePrevious
    End If
Loop

Open in new window

0
 
NashVegasAuthor Commented:
I thought as much and tried that previously. However, I then get this message:
Run-time error '3021': No current record
...and the line "dtTest2 = Trim(rs!Reviewer) & Trim(rs!RecDat)" is highlighted. Ah, but I just realized why this is happening. It is because dtTest1 is already the first record and then it moves to previous and is out of range. So I need to modify my loop to prevent this. Is this the best way?:
Do Until rs.BOF
    dtTest1 = Trim(rs!Reviewer) & Trim(rs!RecDat)
    rs.MovePrevious
    If Not rs.BOF Then
        dtTest2 = Trim(rs!Reviewer) & Trim(rs!RecDat)
        If dtTest1 = dtTest2 Then
            rs.Delete
            rs.MovePrevious
        End If
    End If
Loop
0
 
Chuck WoodCommented:
Either that or put the BOF test before the MovePrevious
-chuck wood
Do Until rs.BOF
    dtTest1 = Trim(rs!Reviewer) & Trim(rs!RecDat)
    rs.MovePrevious
    dtTest2 = Trim(rs!Reviewer) & Trim(rs!RecDat)
    If dtTest1 = dtTest2 Then
        rs.Delete
        If Not rs.BOF Then rs.MovePrevious
    End If
Loop

Open in new window

0
 
NashVegasAuthor Commented:
Actually, that does not work because that is doing the same thing the "Do Unitl rs.BOF" statement is doing. When it is at the first record, the "If Not rs.BOF" validation still passes. It then re-enters the loop, concatenates the fields and then tries to move to the previous record which is out of range. So I have to test right after the first "rs.MovePrevious" statement within the loop.
0
 
Chuck WoodCommented:
Yes, that makes sense. Goo dluck on your project.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now