Improve company productivity with a Business Account.Sign Up

x
?
Solved

Run-time error '3167': Record is deleted

Posted on 2009-07-13
5
Medium Priority
?
1,089 Views
Last Modified: 2013-12-20
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
Comment
Question by:NashVegas
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 2000 total points
ID: 24843069
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
 

Author Comment

by:NashVegas
ID: 24843208
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24843290
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
 

Author Comment

by:NashVegas
ID: 24843394
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24843455
Yes, that makes sense. Goo dluck on your project.
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question