Record is Deleted Error While Printing Report

Question

I've been making some changes to our inventory database and I'm running into an error when trying to print a report. The macro starts to send the report to the printer, but then I get the error "The record is deleted." This doesn't happen with every item I try to print, just specific ones. Below is the code that runs to print, and I've marked the line the debugger highlights. I tried printing from a backup of the front end before my changes and I'm getting the same error. Not sure if it's related, but I'm also having problems where the barcode isn't printing on the report. I did not originally create this database, and it's running on the 32-bit version of Access. Any ideas as to what's going on?

Private Sub QARoll_Click()

Dim db As Database

Dim rs As Recordset

Dim jrs As Recordset

Dim P As Integer

Dim X As Integer

Dim y As Integer

Dim J As Integer

Dim I As Integer

DoCmd.RunCommand acCmdSaveRecord

Set db = CurrentDb

Set jrs = CurrentDb.OpenRecordset("Select * FROM tbl_Roll WHERE UniqueID = " & Me!UniqueID)

Set rs = CurrentDb.OpenRecordset("tbl_Temp_QA_Sheet")

If Not jrs.BOF Then

Do Until jrs.EOF 

    If Not IsNull(jrs!RollYards) Then 

        X = Int(jrs!RollYards / 200) 'each page room for 150 yards 

        If Int(X) < jrs!RollYards / 200 Then X = X + 1 

        End If 

    y = 0 

    P = 1 

    For I = 1 To X 

        'Clear the Temp table before populating the detail on the report 

        If Not rs.BOF Then 

            rs.MoveFirst 

            Do Until rs.EOF 

                rs.Delete   <--- This is the line the debugger highlights

                'rs.Update 

                rs.MoveNext 

            Loop 

            'rs.Requery 

        End If 

        'Populate 50 lines of detail - 4 columns 

        For J = 1 To 50 

            rs.AddNew 

            rs!COL1 = (J + y) 

            rs!COL2 = (J + y) + 50 

            rs!COL3 = (J + y) + 100 

            rs!COL4 = (J + y) + 150 

            rs.Update 

        Next J 

        P = P + 1 

        y = y + 200 

        DoCmd.OpenReport "rpt\_QA\_Sheet", acViewNormal, , "[UniqueID]=" & jrs!UniqueID 

    Next I 

    jrs.MoveNext 

Loop 

End If 'jrs.bof

rs.close

jrs.close

DoCmd.close acForm, "frm_order_edit_by_roll", acSaveYes

DoCmd.OpenForm "frm_order_edit_by_roll"

End Sub

Answer 1

I tried your suggestion and that fixed it. Thanks for your help!

Answer 2

Are you saying that the error is happening because the table isn't empty? I'm not sure I understand.

No, the Delete method of the DAO Recordset object deletes the recordset object, not a row returned by the recordset. In any case executing an SQL DELETE statement is a simple set operation which needs only one line of code, so using DAO to empty a table would make little sense. The error message which you are receiving is a little confusing; it's the recordset object which has been deleted, not the row (record).

NB: deleting a recordset object does not delete the table itself, only the reference to the table returned by the OpenRecordset method.

Answer 3

Are you saying that the error is happening because the table isn't empty? I'm not sure I understand.

Answer 4

Why not just execute a simple DELETE statement?

    db.Execute "DELETE * FROM tbl_Temp_QA_Sheet", dbFailOnError

This will delete all rows from the table.  If the table is already empty no error will occur.

PS: Do this once only, before the For I = 1 To X loop.