Printing Report Packets

[Jaybird]

I have an application where the user would like to print alternating copies
of two different reports. I have a command button that calls the openreport
method for first one report, then the other, then the first again, then the
other again. These reports are query based. The problem I have is that the
user must input the record ID (per the query that the report is based on)
four times using this method. I have also tried to use the PrintOut method
because you can specify the number of copies to be printed, but then they are
out of sequence and must be sorted by hand. I'm trying to think of a way
around this problem. I was thinking of using an input box and openargs.
Does anyone have any suggestions?

 

[Marshall Barton]

I have an application where the user would like to print alternating copies
of two different reports. I have a command button that calls the openreport
method for first one report, then the other, then the first again, then the
other again. These reports are query based. The problem I have is that the
user must input the record ID (per the query that the report is based on)
four times using this method. I have also tried to use the PrintOut method
because you can specify the number of copies to be printed, but then they are
out of sequence and must be sorted by hand. I'm trying to think of a way
around this problem. I was thinking of using an input box and openargs.

You can bypass the prompting by using a form text box for
the value. Change the parameter from [whatever] to
Forms!nameofform,nameoftextbox Then use a command button on
the form to launch the report.

Unfortunately, even if you get rid of the prompt, printing
the same report multiple times usually fails at least some
of the time because opening an already open report just
hives it the focus.

The traditional way to do what you want is to create a new
report with your two reports as subreports in the detail
section. Put a Page Break control between the two subreport
controls. You may have to modify the subreports to deal
with the fact that the main report is in charge of page
related activities.

To generate a specific number of copies, create a utility
table (named Numbers) with one field (named Num) and
populated with values 1, 2, ... up to more than you will
ever need. Then bind the main report to the Numbers table.

The command button can limit the number of copies by opening
the report this way:
DoCmd.OpenReport "main report", , ,"Num<=" & txtCopies

where txtCopies is a form text box where you can enter the
number of copies you want to print.

You could even bind a main report text box to the Num field
to number each copy of the report.

There will probably be a few other little issues (e.g. page
numbering) that can be addressed after you have all of the
above working.

 

[Jaybird]

I actually have the reports set up to print this way already... They don't
like it. They think it's a pain in the whatchacallit to populate the textbox
on the form every time they want this packet printed. To be fair, I actually
have it set up to bring up those records in a combobox, not in a text box, so
there is some delay after the value is entered, but really...

I have some code that almost works:

Private Sub Cert_Packet_Click()
On Error GoTo Err_Cert_Packet_Click

Dim stDocName As String
Dim intPage As Integer
Dim ordnum As String
Dim entordnum As String

entordnum = InputBox("Enter Order Number")
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
Exit_Cert_Packet_Click:
Exit Sub
Err_Cert_Packet_Click:
MsgBox Err.Description
Resume Exit_Cert_Packet_Click
End Sub

The only problem is that it prints blank reports. I'm still trying to
figure it out.
--
Why are you asking me? I dont know what Im doing!

Jaybird

I have an application where the user would like to print alternating copies
of two different reports. I have a command button that calls the openreport
method for first one report, then the other, then the first again, then the
other again. These reports are query based. The problem I have is that the
user must input the record ID (per the query that the report is based on)
four times using this method. I have also tried to use the PrintOut method
because you can specify the number of copies to be printed, but then they are
out of sequence and must be sorted by hand. I'm trying to think of a way
around this problem. I was thinking of using an input box and openargs.

You can bypass the prompting by using a form text box for
the value. Change the parameter from [whatever] to
Forms!nameofform,nameoftextbox Then use a command button on
the form to launch the report.

Unfortunately, even if you get rid of the prompt, printing
the same report multiple times usually fails at least some
of the time because opening an already open report just
hives it the focus.

The traditional way to do what you want is to create a new
report with your two reports as subreports in the detail
section. Put a Page Break control between the two subreport
controls. You may have to modify the subreports to deal
with the fact that the main report is in charge of page
related activities.

To generate a specific number of copies, create a utility
table (named Numbers) with one field (named Num) and
populated with values 1, 2, ... up to more than you will
ever need. Then bind the main report to the Numbers table.

The command button can limit the number of copies by opening
the report this way:
DoCmd.OpenReport "main report", , ,"Num<=" & txtCopies

where txtCopies is a form text box where you can enter the
number of copies you want to print.

You could even bind a main report text box to the Num field
to number each copy of the report.

There will probably be a few other little issues (e.g. page
numbering) that can be addressed after you have all of the
above working.

 

[Jaybird]

I put in a test for null values:

If IsNull(entordnum) = False Then
DoCmd.OpenReport "Shipper2", acViewPreview, , "[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", acViewPreview, , "[Order Number] = 'entordnum'"
DoCmd.OpenReport "Shipper2", acViewPreview, , "[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", acViewPreview, , "[Order Number] = 'entordnum'"
Else
MsgBox ("You fool! It didn't work!")
End If

But I got the same results. So a value is being passed to the openreport
command, but for some reason it doesn't make sense to Access. If I change
this:

DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"

To this:

DoCmd.OpenReport "Shipper2", , , "[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Number] =
'entordnum'"
DoCmd.OpenReport "Shipper2", , , "[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Number] =
'entordnum'"

I get an error stating that the field [Order Number] could refer to more
than one table in the WHERE clause of my SQL statement. But the form where
this button is located is based on a table, not a query. So, where does the
SQL statement come in, and which one is it? I guess it comes from the
report, but then why wouldn't the original statement work?

 

[Marshall Barton]

I actually have the reports set up to print this way already... They don't
like it. They think it's a pain in the whatchacallit to populate the textbox
on the form every time they want this packet printed. To be fair, I actually
have it set up to bring up those records in a combobox, not in a text box, so
there is some delay after the value is entered, but really...

I have some code that almost works:

Private Sub Cert_Packet_Click()
On Error GoTo Err_Cert_Packet_Click

Dim stDocName As String
Dim intPage As Integer
Dim ordnum As String
Dim entordnum As String

entordnum = InputBox("Enter Order Number")
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
Exit_Cert_Packet_Click:
Exit Sub
Err_Cert_Packet_Click:
MsgBox Err.Description
Resume Exit_Cert_Packet_Click
End Sub

The only problem is that it prints blank reports. I'm still trying to
figure it out.

They think it's a pain to populate a text or combo box, but
it's ok to populate an InputBox??? That doesn't make sense.
.... Whatever :-/

Your problem is that you are using the name of the vaiable
instead of it value. Try this instead:

DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order
Number] = '" & entordnum "' "

 

[Jaybird]

Hey Marsh,

I get what you're saying. My syntax stinks. But are you sure that's the
right way to do it? I'm getting an 'expected end of statement' error.
--
Why are you asking me? I dont know what Im doing!

Jaybird

I actually have the reports set up to print this way already... They don't
like it. They think it's a pain in the whatchacallit to populate the textbox
on the form every time they want this packet printed. To be fair, I actually
have it set up to bring up those records in a combobox, not in a text box, so
there is some delay after the value is entered, but really...

I have some code that almost works:

Private Sub Cert_Packet_Click()
On Error GoTo Err_Cert_Packet_Click

Dim stDocName As String
Dim intPage As Integer
Dim ordnum As String
Dim entordnum As String

entordnum = InputBox("Enter Order Number")
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
Exit_Cert_Packet_Click:
Exit Sub
Err_Cert_Packet_Click:
MsgBox Err.Description
Resume Exit_Cert_Packet_Click
End Sub

The only problem is that it prints blank reports. I'm still trying to
figure it out.

They think it's a pain to populate a text or combo box, but
it's ok to populate an InputBox??? That doesn't make sense.
.... Whatever :-/

Your problem is that you are using the name of the vaiable
instead of it value. Try this instead:

DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order
Number] = '" & entordnum "' "

 

[Jaybird]

Marsh,

This worked:

DoCmd.OpenReport "Shipper2", acViewPreview, , "[Order Entry].[Order Number]
=" & "entordnum"

I guess it's not a text field after all...
The only problem is that it asks for [entordnum] at every instance of the
report. I thought I was working my way around that. When I take out the
quotes around entordnum and run the command I get a type mismatch error.
Okaaay... I guess it IS a text field. I'm running out of ways I can combine
these quotes. Please tell me which part of my brain I can just throw in the
trash.
--
Why are you asking me? I dont know what Im doing!

Jaybird

I actually have the reports set up to print this way already... They don't
like it. They think it's a pain in the whatchacallit to populate the textbox
on the form every time they want this packet printed. To be fair, I actually
have it set up to bring up those records in a combobox, not in a text box, so
there is some delay after the value is entered, but really...

I have some code that almost works:

Private Sub Cert_Packet_Click()
On Error GoTo Err_Cert_Packet_Click

Dim stDocName As String
Dim intPage As Integer
Dim ordnum As String
Dim entordnum As String

entordnum = InputBox("Enter Order Number")
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
Exit_Cert_Packet_Click:
Exit Sub
Err_Cert_Packet_Click:
MsgBox Err.Description
Resume Exit_Cert_Packet_Click
End Sub

The only problem is that it prints blank reports. I'm still trying to
figure it out.

They think it's a pain to populate a text or combo box, but
it's ok to populate an InputBox??? That doesn't make sense.
.... Whatever :-/

Your problem is that you are using the name of the vaiable
instead of it value. Try this instead:

DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order
Number] = '" & entordnum "' "

 

[Jaybird]

Got it! This works just like I want:

DoCmd.OpenReport "Shipper2", acViewPreview, , "[Order Entry].[Order
Number] =" & "'" & entordnum & "'"

Now if you could please tell me WHY, I might be able to do it again. Thanks
for all your help!
--
Why are you asking me? I dont know what Im doing!

Jaybird

I actually have the reports set up to print this way already... They don't
like it. They think it's a pain in the whatchacallit to populate the textbox
on the form every time they want this packet printed. To be fair, I actually
have it set up to bring up those records in a combobox, not in a text box, so
there is some delay after the value is entered, but really...

I have some code that almost works:

Private Sub Cert_Packet_Click()
On Error GoTo Err_Cert_Packet_Click

Dim stDocName As String
Dim intPage As Integer
Dim ordnum As String
Dim entordnum As String

entordnum = InputBox("Enter Order Number")
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order Number] =
'entordnum'"
DoCmd.OpenReport "Certs2", , , "[Order Entry].[Order Number] =
'entordnum'"
Exit_Cert_Packet_Click:
Exit Sub
Err_Cert_Packet_Click:
MsgBox Err.Description
Resume Exit_Cert_Packet_Click
End Sub

The only problem is that it prints blank reports. I'm still trying to
figure it out.

They think it's a pain to populate a text or combo box, but
it's ok to populate an InputBox??? That doesn't make sense.
.... Whatever :-/

Your problem is that you are using the name of the vaiable
instead of it value. Try this instead:

DoCmd.OpenReport "Shipper2", , , "[Order Entry].[Order
Number] = '" & entordnum "' "

 

[Marshall Barton]

Got it! This works just like I want:

DoCmd.OpenReport "Shipper2", acViewPreview, , "[Order Entry].[Order
Number] =" & "'" & entordnum & "'"

Now if you could please tell me WHY, I might be able to do it again. Thanks
for all your help!

That's the same thing I posted, but with an extra
concatenation.

The trick with concatenations is to look at the result you
have to have to do the job. In this case,
[Order Entry].[Order Number] ='abc'
and then work backward to figure out the expression that
will create that result string.

Since the value abc is in the variable entordnum, you need
to concatenate that with the fixed part of the argument:
"[Order Entry].[Order Number] =" & entordnum
which is all you need if the prder number really is a number
type field in the table. Since it's a Text field, you need
to make sure the value is enclosed in quotes:
"[Order Entry].[Order Number] ='" & entordnum & "' "
or
"[Order Entry].[Order Number] =""" & entordnum & """ "

Apparently you were not careful about making the distinction
between "' and '", which may look the same in some fonts.
To avoid that kind of confusion, some people prefer to use:
"[Order Entry].[Order Number] =" & Chr(34) & entordnum &
Chr(34)
wbere Chr(34) is a function that returns the " character.

 

[Jaybird]

I've been wondering what Chr(34) was! Now, I know. Thanks so much!
--
Why are you asking me? I dont know what Im doing!

Jaybird

Got it! This works just like I want:

DoCmd.OpenReport "Shipper2", acViewPreview, , "[Order Entry].[Order
Number] =" & "'" & entordnum & "'"

Now if you could please tell me WHY, I might be able to do it again. Thanks
for all your help!

That's the same thing I posted, but with an extra
concatenation.

The trick with concatenations is to look at the result you
have to have to do the job. In this case,
[Order Entry].[Order Number] ='abc'
and then work backward to figure out the expression that
will create that result string.

Since the value abc is in the variable entordnum, you need
to concatenate that with the fixed part of the argument:
"[Order Entry].[Order Number] =" & entordnum
which is all you need if the prder number really is a number
type field in the table. Since it's a Text field, you need
to make sure the value is enclosed in quotes:
"[Order Entry].[Order Number] ='" & entordnum & "' "
or
"[Order Entry].[Order Number] =""" & entordnum & """ "

Apparently you were not careful about making the distinction
between "' and '", which may look the same in some fonts.
To avoid that kind of confusion, some people prefer to use:
"[Order Entry].[Order Number] =" & Chr(34) & entordnum &
Chr(34)
wbere Chr(34) is a function that returns the " character.