Loop and 2427 / 3035 errors

[toast]

Hello all,

Table 1 and Table 2 are joined.

I have a loop for outputting reports of all the records in Table 1, showing their linked data in Table 2.

The problem was whenever there were no linked records in Table 2 I got a 2427 error.

So I decided to add a Dcount into the loop to check if there were any records in Table 2 before outputting the report.

This now introduces 3035 memory errors. The database is not that large yet, so I'm wondering why this is occurring. The recordset is cleared at the end of the code.

Is there a better way to deal with these problems?

Thanks in advance for any pointers

 

[vbaInet]

I'm just trying to understand why you're needing to loop through the tables?Are you not displaying the records of the joined tables on a report?

Perhaps you could briefly explain what you're doing.

 

[toast]

Thanks for the reply.

The first table is employees.
The second table is what shifts they worked.
So each employee needs a separate PDF file of their work history.

Interestingly, today it didn't throw up the memory issue so I don't know what is hogging the record locks or memory.

The code is currently:

Private Sub BtAllDuty_Click()
On Error GoTo Err_AllDuty
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM Temployee")
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveFirst
        Do Until rst.EOF = True
          If DCount("*", "Tduty", "[Fdriver]=" & rst!FemployeeID & " OR [Floader]=" & rst!FemployeeID) > 0 Then
            DoCmd.OpenReport "ReportDutyBatch", acViewReport, , "[Fdriver]=" & rst!FemployeeID & " OR [Floader]=" & rst!FemployeeID
            [Reports]!ReportDutyBatch.Report!TBrole.ControlSource = "=IIf([Fdriver]=" & rst!FemployeeID & ",'Driver','Loader')"
            [Reports]!ReportDutyBatch.Report!TBother.ControlSource = "=IIf([Floader]=" & rst!FemployeeID & ",[Fdriver],[Floader])"
            [Reports]!ReportDutyBatch.Report!rollingduty.ControlSource = "=DSum('([Fdutyend]-[Fdutystart])*24','Tduty','([Fdriver]=" & rst!FemployeeID & " or [Floader]=" & rst!FemployeeID & ") AND [Fdutyend] between #' & Format([Fdutyend]+1,'mm\/dd\/yyyy') & '# and #' & Format([Fdutyend]-29,'mm\/dd\/yyyy') & '#')"
            [Reports]!ReportDutyBatch.Report!TBID = rst!FemployeeID
            [Reports]!ReportDutyBatch.Report!TBName = rst!Ffullname
            DoCmd.OutputTo acOutputReport, "ReportDutyBatch", acFormatPDF, "C:\Users\A96\Reports\" & rst!Ffullname & ".pdf", False, , , acExportQualityPrint
            DoCmd.Close acReport, "ReportDutyBatch"
          End If
            rst.MoveNext
        Loop
    Else
        MsgBox "No records found"
    End If

    MsgBox "Completed"

    rst.Close
   
Exit_AllDuty:
    Set rst = Nothing
    Exit Sub
    
Err_AllDuty:
    MsgBox Err.Number & Err.Description
    Resume Exit_AllDuty

End Sub

 

[vbaInet]

The DCount() function is too heavy, have a look at a different way of doing it:

http://www.access-programmers.co.uk/forums/showthread.php?t=204478

You can even use the Open event of the report to filter it as well. Then check against the report's HasData property or use the On No Data event.

 

[toast]

Thank you for the tips - I'll take a look and hopefully come up with a better solution!

 

[vbaInet]

Keep us informed!