Printing Multiple Copies of a Report

[Dave Richardson]

Hi,

I'm having a nightmare trying to use a form as the basis of capturing the number of copies that a user requires of a report to be printed. I originally used the input box as a solution, but wanted something more elegent by using a txt control on a form instead.

The input box code is like this:


' Accepts user input in order that the number of copies required for an exam may be input


Dim Message, Title, Default, MyValue

Message = "How many Prints would you like?"

Title = "Print Reports"
Default = "1"
MyValue = InputBox(Message, Title, Default)

DoCmd.OpenReport "RptTest", acViewPreview

DoCmd.SelectObject acReport, "RptTest", True

DoCmd.PrintOut acPages, , , , MyValue

DoCmd.Close acReport, "RptTest"


My question is how can I get a form to do the same as the above. As you can guess I am a 'newbie' but am enjoying the steep learning curve!..

Kind Regards,

Dave

[Scott Price]

The general syntax when referring to a control on a form is:

Forms!frmInput!txtInput

Where frmInput is the name of your form, and txtInput is the name of your text box.

By the way, a couple of things that are different with vba from a number of other programming languages... If you do not explicitly specify the data type of your variables vba assumes them to be of Variant type. This is handy when you don't know what type to declare them as, however, it slows things down because then vba has to programatically determine the best data type for the data that you enter. Also, declaring variables with one Dim statement can be tricky!

Dim MyValue, Default, MyEtc As String

As an example... This statement results in the first two variables being declared as Variants and the third declared as String, NOT all three being declared as String! VBA evaluates each variable, not each Dim line...

A second point I noticed is you are setting your Default variable to a string value or "1", not a number value of 1. You might run into troubles with this later in your code since acPages is presumably expecting a number format. However, if this code is actually working for you it means that Access has been smart enough to interpret what you want from what you are telling it :-)

So to sum this up: To use this code with a form reference you will need a few changes...

Dim Message, Title, Default, MyValue
 
Message = "How many Prints would you like?"
 
Title = "Print Reports"
Default = "1"
MyValue = InputBox(Message, Title, Default)
 
DoCmd.OpenReport "RptTest", acViewPreview
 
DoCmd.SelectObject acReport, "RptTest", True
 
DoCmd.PrintOut acPages, , , , MyValue
 
DoCmd.Close acReport, "RptTest"

This is what I would try first (not tested, but off top of head, so use with that in mind :-) Place this code in the declarations section of the code module behind your form.

Public Message As String, Title As String, Default As Integer
 
Message = "How many Prints would you like?"
 
Title = "Print Reports"
Default = 1

Then in the form On_Open event:

Me!labelMessage.Caption = Message
Me!labelTitle.Caption = Title
Me!textPrintouts = Default

Then in the AfterUpdate event of your text box, or alternatively in the Click event of a command button:

Default = Me!textPrintouts
DoCmd.OpenReport "RptTest", acViewPreview
 
DoCmd.SelectObject acReport, "RptTest", True
 
DoCmd.PrintOut acPages, , , , Default
 
DoCmd.Close acReport, "RptTest"

Obviously, on this form you will need two labels and one unbound text box named the same as I have named them in the code.

Give this a try!

Regards,
Scott

[Dave Richardson]

The general syntax when referring to a control on a form is:

Forms!frmInput!txtInput

Where frmInput is the name of your form, and txtInput is the name of your text box.

By the way, a couple of things that are different with vba from a number of other programming languages... If you do not explicitly specify the data type of your variables vba assumes them to be of Variant type. This is handy when you don't know what type to declare them as, however, it slows things down because then vba has to programatically determine the best data type for the data that you enter. Also, declaring variables with one Dim statement can be tricky!

Dim MyValue, Default, MyEtc As String

As an example... This statement results in the first two variables being declared as Variants and the third declared as String, NOT all three being declared as String! VBA evaluates each variable, not each Dim line...

A second point I noticed is you are setting your Default variable to a string value or "1", not a number value of 1. You might run into troubles with this later in your code since acPages is presumably expecting a number format. However, if this code is actually working for you it means that Access has been smart enough to interpret what you want from what you are telling it :-)

So to sum this up: To use this code with a form reference you will need a few changes...

Dim Message, Title, Default, MyValue
 
Message = "How many Prints would you like?"
 
Title = "Print Reports"
Default = "1"
MyValue = InputBox(Message, Title, Default)
 
DoCmd.OpenReport "RptTest", acViewPreview
 
DoCmd.SelectObject acReport, "RptTest", True
 
DoCmd.PrintOut acPages, , , , MyValue
 
DoCmd.Close acReport, "RptTest"

This is what I would try first (not tested, but off top of head, so use with that in mind :-) Place this code in the declarations section of the code module behind your form.

Public Message As String, Title As String, Default As Integer
 
Message = "How many Prints would you like?"
 
Title = "Print Reports"
Default = 1

Then in the form On_Open event:

Me!labelMessage.Caption = Message
Me!labelTitle.Caption = Title
Me!textPrintouts = Default

Then in the AfterUpdate event of your text box, or alternatively in the Click event of a command button:

Default = Me!textPrintouts
DoCmd.OpenReport "RptTest", acViewPreview
 
DoCmd.SelectObject acReport, "RptTest", True
 
DoCmd.PrintOut acPages, , , , Default
 
DoCmd.Close acReport, "RptTest"

Obviously, on this form you will need two labels and one unbound text box named the same as I have named them in the code.

Give this a try!

Regards,
Scott

Hi Scott,

I'll get on this right away!. I really do appreciate the time you have extended to me in replying to this. Like I said i'm having a really great time getting into all this and love the directions it can take you and, equally, the frustrations :-).

I'll post the results from this.

Kindest regards

Dave

[Dave Richardson]

Hi Scott,

I have tried the suggested code and all looks ok to me apart from the usage of two lables with the unbound control. I can't get my head round why the second label is used.

1. Me!labelMessage.Caption = Message
2. Me!labelTitle.Caption = Title
3. Me!textPrintouts = Default

I also get the following error: "Invalid attribute in sub or Function"
with the statement provided for the declarations of the form:

Public Message As String, Title As String, Default As Integer

Message = "How many Prints would you like?"

Title = "Print Reports"
Default = 1

The "Public" line entry being highlighted asa the problem.


As i said before I greatly appreciate this assistance and it has given me alot to think about in other areas as well.

Kindest regards,

Dave

[Scott Price]

The Public declaration needs to take place above any of the Subs or Functions in your form module. As you look at the vba editor window with this form module selected at the very top you will see Option Compare Database. There should also be Option Explicit on the very next line below. IF there isn't, then type it in. This tells vba to require you to explicitly declare each and every variable. (otherwise you can make a spelling mistake down in your code somewhere and never find out about it since vba will accept it as a new variable and assign it a variant data type :-)

On the lines just below these two (but above any subs or functions in the form module) you will name these public variables. As for why two labels? Really not need, unless you wanted to assign the form caption separately, or want to place the two labels in different locations on the report.

And I forgot, but you will have to assign the text to these labels further down in the form module. The Public declaration goes at the top like I mentioned, but assigning text to them goes further down.

Regards,
Scott

[Dave Richardson]

Thanks Scott!, Many thanks for getting back so quick. I'll attack this when I'm at home this evening,

Dave