Generate Sequential Numbers on Report 2

[kmkland]

I am trying to generate numbers on a report for which the user will enter the parameters (i.e. Number: 550 for the first report.....Number: 600 for the last report -->thus printing 50 copies of my report, each with a different sequential number). My report is basically a template. I print the report template, each with different numbers, and employees use the printed report as a form to complete and submit to their supervisors.

Is there any way to do this and also set it up so the user can enter the specific number parameters when it is time to print the reports?

Thanks in advance!!!
Kim.

Rgds,
Kmkland

 

[Remou]

Here is a suggestion that may suit.
1. Create a report called, say, rptReport, with a text box that references the form, frmForm, described below. The reference would look like:
[tt]=Forms!frmForm!txtCount[/tt]

2. Create a form called, say, frmForm, with two text boxes and a command button. The text boxes are txtCount, to hold a starting number, and txtCopies, for number of copies. The code for the command button is:

Do Until Me.txtCopies = 0
    DoCmd.OpenReport "rptReport"
    Me.txtCount = Me.txtCount + 1
    Me.txtCopies = Me.txtCopies - 1
Loop

 

[kmkland]

Hi Remou,

Thanks for your response.

I tried your suggestion. I will enter "501" for the number and "3" for the copies and only 1 copy will print with "504" printed on the report....instead of 3 sequential copies from 501-503.

Rgds,
Kim.

Rgds,
Kmkland

 

[Remou]

I wonder in what way our systems differ? If I enter 3 in the number of copies box, three copies are printed out with the expected sequence numbers. I tested initially with printing to file, which worked, but I have just tried with a printout, which also worked.

 

[kmkland]

I don't know. It is only printing 1 copy of the ending number that comes after the last sequential number I want to print.

If anyone has any suggestions, I would greatly appreciate it!!

Thanks for any and all help on this matter!!

Rgds,
Kim.

Rgds,
Kmkland

 

[Remou]

Can you print more than one report in any way? For example does:
[tt]For i = 1 To 3
DoCmd.OpenReport "rptReport"
Next[/tt]
Produce more than one report?

 

[Remou]

Here is another idea, but it is more work. You mention that the report is a template, so I am assuming it is based on either a single record, or does not have a table attached. You will need the form from my first post.
1. Create a table with a single, numeric field containing the digits 1 to the largest number of copies.
2. If your report does not have a table attached, create a query based on this table alone:
[tt]SELECT tblCopies.CopyNumber
FROM tblCopies
WHERE tblCopies.CopyNumber<=[Forms]![frmForm].[txtCopies];[/tt]
If your report is based on a table, create a similar query including the table on which the report is based.
3. use this query as the record source for your table.
4. Add two textboxes to the detail section of your report. Set the first equal to 1 and make it a Running Sum Over All; set the Visible property to No; call it, say, txtRSum. The control source for the second textbox should be:
[tt]=[Forms]![frmForm].[txtCount]+[txtRSum]-1[/tt]
This should produce the required number of copies, numbered appropriately, in both preview and print.

 

[kmkland]

Remou:

I am confused.

Do I still use the codes you posted earlier?
Is the second text box on my report visible?

I made the query the control source for my template. I am guessing that I do not need the codes now (?).

Please, advise what I need to do concerning this. It is now generating the report but the number is the last number (not the number I need) and it's generated multiple copies. (I set Count=5 and Copies=2 to test it)

Thanks for your help!


Rgds,
Kmkland

 

[Remou]

In the second idea I posted, the report has 2 textboxes, one visible to hold the sequential number, and one invisible, to hold a running sum. For testing purposes, you may wish to leave the running sum visible.

I cannot reproduce the problem you are having with the reports not numbering properly (though I have not tried very hard), which is one of the reasons I posted a second idea.

 

[kmkland]

So, do I continue to use the codes at all? The original idea that you had posted?

Rgds,
Kmkland

 

[Remou]

It does not seem to be working for you, and I cannot tell why, because it works for me, so there does not seem to be much point. If you wish, you can try to play around a bit. You say that you can get several copies using the original idea, but if they are not numbered as you wish, there may be no point in playing around, either. Because I cannot tell why the first idea did not work, I cannot say for sure that the second idea will work, it is just a thought.
You have checked that the name of the form and so on on the report is right? It sounds as if it is, if you are getting the last number (txtCount + txtCopies) printed on your report. I do not understand why the intermediate numbers are not printing, as I say, it worked for me. If you like, you can post the code you are using on your form, in case I can spot some small problem.

 

[BoxHead]

Kim,

Double-check your spelling and the name of the report you're opening in the Do...Loop statement. I tried Remou's original code and it worked fine.

The only way I could think to get your results would be if the report name in the Do...Loop Statement is different from your actual report and you still have some previously-written code calling the print action.

HTH

John



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[BoxHead]

OR... are you printing to PDF without save prompts in which case each report would print and replace the previous one leaving only the last...




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[kmkland]

Here is the code:

Private Sub btnGenRpt_Click()
Do Until Me.txtCopies = 0
    DoCmd.OpenReport "rptNonconfFrt", acViewPreview, , , acWindowNormal
    Me.txtCount = Me.txtCount + 1
    Me.txtCopies = Me.txtCopies - 1
Loop
End Sub

I have the report in Print Preview to review it before I print. All the names of the reports, text boxes, and forms are correct.

Ok. So to clarify one more time: I enter, let's say "501" in the text box for txtCount for the beginning number. And I enter "5" in the text box for txtCopies for the number of sequentially numbered reports I want (I want to end on 506). If this is the case, then it is not working properly for me. I did this and received 1 copy of "506".

You all are so helpful...I think that it will eventually get sorted out.

Thanks so much!

Rgds,
Kmkland

 

[Remou]

Unfortunately, this snippet will not work with preview, as each report is replaced by the next one and you never get to see the intermediate numbers. If you must have a preview, you will have to use the second, clunkier idea. If you just wish to test without wasting paper, create a generic, text only printer that prints to a file and use this for testing.

 

[BoxHead]

Kim, be aware that with Remou's first solution, if you print to file you will have to provide a new file name for each page that is printing. If you let your system (or choose to) overwrite each of the files as they are generated, you will still be left with just the last file.

The distinction to be clear on is that Remou's first solution generates and prints a new one-page report for each number in the sequence. His second solution generates a single report with as many pages as you specify.

Remou, I don't want to hang a star on a thread until the poster has a working solution, but I want to thank you for your first solution. I've used the second option (clunkier, as you say) in the past for controlling documents, but your "snippet" will replace that.

Thanks,

John

When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[Remou]

Thank you for your comments BoxHead. Between the three of us, we are sure to get this sorted.

 

[kmkland]

Remou:

I am having network issues and printer settings. I will try your suggestion with sending the reports directly to the printer, instead of displaying a preview. I am sure that it will work.

I will let you know how things go.

Thanks for everyone's help.

Rgds,
Kmkland

 

[kmkland]

Remou:

Sorry I didn't let you know sooner the result of your suggestion. It worked brilliantly!

Cheers for everyone's help!



Rgds,
Kmkland