467,891 Members | 1,843 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,891 developers. It's quick & easy.

Printing several records from a form - looping through


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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOK_Click()
  2. On Error GoTo Err_cmdOK_Click
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.     Dim db As DAO.Database
  7.     Dim rst As DAO.Recordset
  8.     Dim strdate As String
  9.     Dim strRanger As String
  10.     Dim qryName As String
  11.     Dim tblName As String
  12.     Dim strSQL As String
  13.     Dim i As Integer
  15.     stDocName = "frmJobSheetTrimble"
  16.     tblName = "tblTempJobSheet"
  17.     qryName = "qryJobSheetByDate"
  19.     strSQL = "delete from tblTempJobSheet"
  21.     DoCmd.SetWarnings False
  22.     DoCmd.RunSQL strSQL
  24.     DoCmd.OpenQuery qryName, acViewNormal ' This creates the temporary table to print the record from.
  26.     Set db = CurrentDb()
  27.     Set rst = db.OpenRecordset("tbltempJobSheet")
  29.     i = 0
  30.     MsgBox "You are about to print" & " " & Count & " " & "records.", vbOKCancel
  32.     DoCmd.OpenForm stDocName, , , , , acWindowNormal
  34.             rst.MoveFirst
  35.                 Do While Not rst.EOF
  36.                     i = i + 1
  37.                     If rst![SpecificRiskAss] = True Then
  38.                        DoCmd.PrintOut acPages, 1, 2, , , Yes
  40.                     End If
  42.                     If rst![SpecificRiskAss] = False Then
  43.                        DoCmd.PrintOut acPages, 1, 1, , , Yes
  44.                     End If
  46.             rst.MoveNext
  47.         Loop
  49.     rst.close
  51.     Set rst = Nothing
  52.     Set db = Nothing
  54.     DoCmd.SetWarnings True
  55.     DoCmd.close acForm, stDocName
  57.     stDocName1 = "frmJobSheetPrinted"
  58.     DoCmd.OpenForm stDocName1, , , stLinkCriteria
  60. Exit_cmdOK_Click:
  61.     Exit Sub
  63. Err_cmdOK_Click:
  64.     MsgBox Err.Description
  65.     Resume Exit_cmdOK_Click
  67. End Sub
Any help gratefully appreciated.

Sep 5 '07 #1
  • viewed: 1858
4 Replies
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:

Expand|Select|Wrap|Line Numbers
  1. 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?
Sep 5 '07 #2
Expert 1GB
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.
Sep 5 '07 #3
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.
Sep 6 '07 #4
Expert 1GB
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.
Sep 6 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Michael Goerz | last post: by
1 post views Thread by Earl Anderson | last post: by
3 posts views Thread by MattB | last post: by
20 posts views Thread by Stewart Graefner | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.