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 programmaticall y.

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:
[code=vb]
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 = "frmJobSheetTri mble"
tblName = "tblTempJobShee t"
qryName = "qryJobSheetByD ate"

strSQL = "delete from tblTempJobSheet "

DoCmd.SetWarnin gs False
DoCmd.RunSQL strSQL

DoCmd.OpenQuery qryName, acViewNormal ' This creates the temporary table to print the record from.

Set db = CurrentDb()
Set rst = db.OpenRecordse t("tbltempJobSh eet")

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.SetWarnin gs True
DoCmd.close acForm, stDocName

stDocName1 = "frmJobSheetPri nted"
DoCmd.OpenForm stDocName1, , , stLinkCriteria

Exit_cmdOK_Clic k:
Exit Sub

Err_cmdOK_Click :
MsgBox Err.Description
Resume Exit_cmdOK_Clic k

End Sub
[/code]
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.