Printing several records from a form - looping through

[ske]

Hi

I've inherited an Access database where there is a form setup which prints out a Job Sheet. This works fine with one record. However I need to set it to loop through several records and print them all out programmatically.

This is my code which appears to work fine, but doesn't loop through the records, just prints off the first record however many times there are records to print. This is my code:

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strdate As String
    Dim strRanger As String
    Dim qryName As String
    Dim tblName As String
    Dim strSQL As String
    Dim i As Integer
 
    stDocName = "frmJobSheetTrimble"
    tblName = "tblTempJobSheet"
    qryName = "qryJobSheetByDate"
 
    strSQL = "delete from tblTempJobSheet"
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
 
    DoCmd.OpenQuery qryName, acViewNormal ' This creates the temporary table to print the record from.
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tbltempJobSheet")
 
    i = 0
    MsgBox "You are about to print" & " " & Count & " " & "records.", vbOKCancel
 
    DoCmd.OpenForm stDocName, , , , , acWindowNormal
 
            rst.MoveFirst
                Do While Not rst.EOF
                    i = i + 1
                    If rst![SpecificRiskAss] = True Then
                       DoCmd.PrintOut acPages, 1, 2, , , Yes
 
                    End If
 
                    If rst![SpecificRiskAss] = False Then
                       DoCmd.PrintOut acPages, 1, 1, , , Yes
                    End If
 
            rst.MoveNext
        Loop
 
    rst.close
 
    Set rst = Nothing
    Set db = Nothing
 
    DoCmd.SetWarnings True
    DoCmd.close acForm, stDocName
 
    stDocName1 = "frmJobSheetPrinted"
    DoCmd.OpenForm stDocName1, , , stLinkCriteria
 
Exit_cmdOK_Click:
    Exit Sub
 
Err_cmdOK_Click:
    MsgBox Err.Description
    Resume Exit_cmdOK_Click
 
End Sub
 

Any help gratefully appreciated.

Thanks
Sandra

[barry07]

Hi Sandra,
The simplest solution is to modify the query (qryName) so it selects all the jobs you need instead of just one. You will also need to modify the PrintOut statement as follows:

DoCmd.PrintOut acPrintAll, , , , , Yes

I can't guarantee that this will necesssarily print one job per page. That's another day's work. Have you looked at Access Reports?

[JKing]

Hi there, I dont quite understand what you're doing here.

First you delete the temporary table, then open a query, open a form and start printing.

Is the query the form's recordsource? Also you don't need to open a query to use it as a recordset.

I believe the multiple copies of the same record is caused by printing the same page numbers over and over. No where in your code are you changing the page numbers or changing the records of the form.

Off the top of my head I think either a) incrementing the page numbers would help or b) filtering the form to equal each specific record print then move to the next record.

[ske]

Thanks for replying.

I can get the form to print out fine without the IF statements around the printout statement. The reference in the IF statement seems to be the problem.

I know the statements at the beginning are a bit round the houses but I can sort that out later. Unfortunately I need to print out each record one or two sided depending on whether SpecificRiskAss field is ticked.

[JKing]

You have a variable i that doesn't seem to be used. Upon adding the if statements did you accidentally remove it from the Docmd.PrintOut parameters? Instead of the 1 I think you may actually want to be using the i integer you've created.