By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Printing several records from a form - looping through

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOK_Click()
  2. On Error GoTo Err_cmdOK_Click
  3.  
  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
  14.  
  15.     stDocName = "frmJobSheetTrimble"
  16.     tblName = "tblTempJobSheet"
  17.     qryName = "qryJobSheetByDate"
  18.  
  19.     strSQL = "delete from tblTempJobSheet"
  20.  
  21.     DoCmd.SetWarnings False
  22.     DoCmd.RunSQL strSQL
  23.  
  24.     DoCmd.OpenQuery qryName, acViewNormal ' This creates the temporary table to print the record from.
  25.  
  26.     Set db = CurrentDb()
  27.     Set rst = db.OpenRecordset("tbltempJobSheet")
  28.  
  29.     i = 0
  30.     MsgBox "You are about to print" & " " & Count & " " & "records.", vbOKCancel
  31.  
  32.     DoCmd.OpenForm stDocName, , , , , acWindowNormal
  33.  
  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
  39.  
  40.                     End If
  41.  
  42.                     If rst![SpecificRiskAss] = False Then
  43.                        DoCmd.PrintOut acPages, 1, 1, , , Yes
  44.                     End If
  45.  
  46.             rst.MoveNext
  47.         Loop
  48.  
  49.     rst.close
  50.  
  51.     Set rst = Nothing
  52.     Set db = Nothing
  53.  
  54.     DoCmd.SetWarnings True
  55.     DoCmd.close acForm, stDocName
  56.  
  57.     stDocName1 = "frmJobSheetPrinted"
  58.     DoCmd.OpenForm stDocName1, , , stLinkCriteria
  59.  
  60. Exit_cmdOK_Click:
  61.     Exit Sub
  62.  
  63. Err_cmdOK_Click:
  64.     MsgBox Err.Description
  65.     Resume Exit_cmdOK_Click
  66.  
  67. End Sub
  68.  
Any help gratefully appreciated.

Thanks
Sandra
Sep 5 '07 #1
Share this Question
Share on Google+
4 Replies


P: 47
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

JKing
Expert 100+
P: 1,206
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

P: 2
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.
Sep 6 '07 #4

JKing
Expert 100+
P: 1,206
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.