Passing variables to reports

R

Ray

I have a one page report that I need to have print twice. In other words I
need the bottom of one copy to say something like "home office copy" and the
second one to say "head office copy"

I have tried dim'ing a value in the vb code for a bound control in the
report but cant seem to get the value to pass to the report.

Is there an easier way?

Any help appreciated

Ray
 
M

Marshall Barton

Ray said:
I have a one page report that I need to have print twice. In other words I
need the bottom of one copy to say something like "home office copy" and the
second one to say "head office copy"

I have tried dim'ing a value in the vb code for a bound control in the
report but cant seem to get the value to pass to the report.
Click to expand...


There is a little issue with printing the same report twice.
If a report is already open when you try to open a second
copy, Access just uses the one that is already running
(sometimes mixing things up in the process).

A safe way to print two copies is to do it all in a single
report that prints everything twice. This is fairly easy to
do using a simple query trick.

First create a little table (named offices) with one field
(named officetype). Populate the table with two records,
one with "home office" and the other with "head office".
Then modify the report's record source so it looks something
like:

SELECT yourtableorquery.*, offices.officetype
FROM yourtableorquery, offices

Then change the report to use a top level group on the
officetype field. Move anything you have in the report
header/footer to the group header/footer (might not be
acceptable if report has multiple columns). The page
header/footer sections can just use a text box bount to the
officetype field.
 
G

GeoffG

Ray:

You could use the DoCmd.OpenReport method to pass a string in the OpenArgs
argument. For example, assuming there's a textbox control at the bottom of
the report named txtFooter, use code like this in the report's class module:

Private mstrFooterText As String

Private Sub Report_Open(Cancel As Integer)

' Capture the OpenArgs string in the report's Open event:
mstrFooterText = Me.OpenArgs

End Sub

Private Function GetFooterText() As String

' Call this function from the txtFooter control's
' ControlSource property, using the syntax:
' =GetFooterText()

GetFooterText = mstrFooterText

End Function


Then in a standard module, you could print the report as follows:

Public Sub PrintReport1()

' Use the OpenArgs argument to pass value to report.
' The following code will send the report straight
' to the printer:
DoCmd.OpenReport "Report1", , , , , "Home Office Copy"
DoCmd.OpenReport "Report1", , , , , "Head Office Copy"

End Sub


Geoff
 
D

Dirk Goldgar

Marshall Barton said:
There is a little issue with printing the same report twice.
If a report is already open when you try to open a second
copy, Access just uses the one that is already running
(sometimes mixing things up in the process).

A safe way to print two copies is to do it all in a single
report that prints everything twice. This is fairly easy to
do using a simple query trick.

First create a little table (named offices) with one field
(named officetype). Populate the table with two records,
one with "home office" and the other with "head office".
Then modify the report's record source so it looks something
like:

SELECT yourtableorquery.*, offices.officetype
FROM yourtableorquery, offices

Then change the report to use a top level group on the
officetype field. Move anything you have in the report
header/footer to the group header/footer (might not be
acceptable if report has multiple columns). The page
header/footer sections can just use a text box bount to the
officetype field.
Click to expand...


I just wanted to say this is a brilliant answer. Well done, Marsh!
 
G

GeoffG

Second thoughts: if you need to open the report in preview mode or if you
need to open the report with or without an argument, then here's the
replacement code:


1. For the report's class module:


Private mstrFooterText As String


Private Sub Report_Open(Cancel As Integer)

' Capture the OpenArgs string in the report's Open event,
' converting a missing argument to a zero-length string:
mstrFooterText = Nz(Me.OpenArgs)

End Sub

Private Function GetFooterText() As String

' Call this function from the txtFooter control's
' ControlSource property, using the syntax:
' =GetFooterText()

GetFooterText = mstrFooterText

End Function


2. And for the standard module:


Public Sub PreviewReport1_WithoutArgument()

DoCmd.OpenReport "Report1", acViewPreview

End Sub

Public Sub PreviewReport1_WithArgument()

Dim objRPT As Access.Report
Dim fIsOpen As Boolean
Dim I As Integer

' Open report in Preview mode, using the OpenArgs
' argument to pass string to report:
DoCmd.OpenReport "Report1", acViewPreview, , , , "Home Office Copy"

WasteTimeWhileHomeOfficeCopyIsOpen:

fIsOpen = False
For Each objRPT In Access.Reports
If objRPT.Name = "Report1" Then
fIsOpen = True
Exit For
End If
Next
If fIsOpen Then
DoEvents
For I = 1 To 1000
' Waste time.
Next
GoTo WasteTimeWhileHomeOfficeCopyIsOpen
End If

' If we're here, the report has been closed.

' Open report again in Preview mode with new argument:
DoCmd.OpenReport "Report1", acViewPreview, , , , "Head Office Copy"

End Sub

Public Sub PrintReport1_WithoutPreview()

DoCmd.OpenReport "Report1", , , , , "Home Office Copy"
DoCmd.OpenReport "Report1", , , , , "Head Office Copy"

End Sub


Geoff
 
Top