Print a single record multiple times 3

[marieparker]

Hi, I have an application where I would like to print the same report for a single record 2 or more times depending upon user input. I have a counter running at the print detail event , but the report keeps looping and reprinting .
I know it must be simple , I am doing the same thing for all the records in the db and it's working perfectly. Help?

 

[hymn]

Something like

DoCmd.OpenReport "Birthdays", acViewPreview
DoCmd.PrintOut , , , , 2

2 = no of copies

Hope this helps
Hymn

 

[dhookom]

You may also be able to use a cartestian query. This solution uses a table of numbers "tblNums" with a single numeric field "Num" with values in records 1, 2, 3,...x

You can add this table to your report's record source and don't join it to any other table. Drag the Num field to the grid and set a criteria to:
<=[Enter Number of Copies]

I would probably use a reference to a control on a form rather than the parameter prompt.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[THWatson]

Just to add to the mix, you could also put the following code on the Open event of your report. (I used this procedure with government forms in which multiple copies were required) And then, as DHookom suggests, have a form with a control in which you enter the number of times to repeat the report.

Option Compare Database
Option Explicit
Dim intPrintCounter As Integer
Dim intNumberRepeats As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   ' Note: intNumberRepeats and intPrintCounter are initialized
   ' in the report's OnOpen event.
   If intPrintCounter < intNumberRepeats Then
      intPrintCounter = intPrintCounter + 1
      ' Do not advance to the next record.
      Me.NextRecord = False
   Else
      ' Reset intPrintCounter and advance to next record.
      intPrintCounter = 1
      Me.NextRecord = True
   End If
End Sub


Private Sub Report_Open(Cancel As Integer)
   intPrintCounter = 1
   intNumberRepeats = Forms!YourFormName!txtRepeat
End Sub

Duane, how's your grandson? I'm a grandfather again, as of 2 weeks ago (a granddaughter)...7 in total now.

Tom

 

[dhookom]

TH,
Nice code. I have seen similar solutions before but they involve keeping the code somewhere and looking it up.


<OT>Jackson is 14 mths and couldn't be more fun. I trust each of your 7 all have their special place in grandpa's heart.</OT>

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[marieparker]

Thanks to you all. I've tried all the solutions. But I've still got a small problem. THWatson, I'm using a similar code to the one you provided. And Dhookem , I am using a control on the the form that supplies the # of times to print. The report is not looping now , but I'm getting the last page 1 extra time. So, I've still got some tweaking to do it seems. Any thoughts on that extral page?

 

[dhookom]

Marie,
Regarding the sql/cartesian solution, could you provide the SQL view of your report's recordsource? Also, set the report's primary sorting and grouping to the Num field.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]