Passing data to a Report

[MKammerer via AccessMonster.com]

Currently I have a form that searches my main table using a user defined
filter which is stored to a sting variable. The best part of that is that I
can then use that string variable to pass as the filter for a report
generation. This all works nicely as long as the search form remains the
subform of the main form which contains the button that generates the report.
The next thing I would like to do is create a dynamic title on the report
that says what the user searched for, in other words display the filter.
However since the full text of the filter string doesn't look very good as
the title of the report I have added an additional algorithm to the search
form that generates another string explaining the search the user specified
in more lengthy text which looks better as a report title. Anyway the
problem is getting this search description string into the report to display
it properly. I can send the filter string to the report using this code:
DoCmd.OpenReport stDocName, acViewPreview, , [filterString]

I even took the time to save the search description string to a table which
is the data source of the main form where the button is that creates the
report, so the button has access to the filter decription but passing it to
the report is giving me trouble.

 

[Allen Browne]

What version of Access is this? In A2003 and later, reports have an
OpenArgs. You can use this to pass the description to the report, e.g.
DoCmd.OpenReport "stDocName, acViewPreview, , strWhere, strDescrip
Then show it on the report with a text box bound to:
=[Report].[OpenArgs]

If you are using an older version of Access, declare a public string
variable in a standard module, e.g.:
Public gstrReportFilter As String
Then set this before calling the report, e.g.:
gstrReportFilter = "July 2008 only"
and use code in the Format event of the Report Header or whatever section
contains the text box you want to display the filter in, and clear the
string:
If gstrReportFilter <> vbNullString Then
Me.Text0 = gstrReportFilter
gstrReportFilter = Null
End If

 

[MKammerer via AccessMonster.com]

Unfortunate;y I'm using Access 2002, so I think I will ahve to use the module
that you mentioned. Unfortuneately I'm not familiar with using modules at
all. I understand the coding of what you described but I have never used
modules and really don't know where to begin other than using the wizard to
create one.
(actually kinda funny that you replied since i'm using your code from
http://allenbrowne.com/ser-62.html to define the filter/search criteria I
spoke about)
Anyway any beginner pointers in modules will help me out or even a link to
tutorial.

Thanks,

What version of Access is this? In A2003 and later, reports have an
OpenArgs. You can use this to pass the description to the report, e.g.
DoCmd.OpenReport "stDocName, acViewPreview, , strWhere, strDescrip
Then show it on the report with a text box bound to:
=[Report].[OpenArgs]

If you are using an older version of Access, declare a public string
variable in a standard module, e.g.:
Public gstrReportFilter As String
Then set this before calling the report, e.g.:
gstrReportFilter = "July 2008 only"
and use code in the Format event of the Report Header or whatever section
contains the text box you want to display the filter in, and clear the
string:
If gstrReportFilter <> vbNullString Then
Me.Text0 = gstrReportFilter
gstrReportFilter = Null
End If

Currently I have a form that searches my main table using a user defined
filter which is stored to a sting variable. The best part of that is that

[quoted text clipped - 21 lines]

to
the report is giving me trouble.

 

[Allen Browne]

Actually, just checked, and A2002 does have OpenArgs for OpenReport.

Take a look at the OpenTheReport() function on this page:
http://allenbrowne.com/AppPrintMgtCode.html#OpenTheReport

To use it:
1. Click the Modules tab of the Database Window.

2. Click new.
Access opens a new code window.

3. Copy the code from the website for this one function, i.e. everything
from:
Function OpenTheReport(strDoc As String, _
down to the first:
End Function
Paste it into your module.

4. Delete the line:
Call SetupPrinter4Report(strDoc, strErrMsg)

5. Copy the Function LogError() later on that page, and paste it into your
module as well.

6. Add the text box to your report, with Control Source set to:
=[Report].[OpenArgs]

Now I assume you have a command button on your form that prepares the filter
string and description. At the end of that code, you will have a line that
starts:
DoCmd.OpenReport strDoc, acViewPreview, , strWhere
Replace that line with:
Call OpenTheReport(strDoc, acViewPreview, strWhere, strDescrip)

Explanation
=========
The code above is part of a utility you can download if you want. The part
you deleted is what the utility is about, but I personally use this
OpenTheReport() function instead of OpenReport everywhere in my databases.
It avoids a raft of problems e.g. where a report doesn't filter properly if
it's already open, and the errors you get if the report did not open. Hope
it helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Unfortunate;y I'm using Access 2002, so I think I will ahve to use the
module
that you mentioned. Unfortuneately I'm not familiar with using modules at
all. I understand the coding of what you described but I have never used
modules and really don't know where to begin other than using the wizard
to
create one.
(actually kinda funny that you replied since i'm using your code from
http://allenbrowne.com/ser-62.html to define the filter/search criteria I
spoke about)
Anyway any beginner pointers in modules will help me out or even a link to
tutorial.

Thanks,

What version of Access is this? In A2003 and later, reports have an
OpenArgs. You can use this to pass the description to the report, e.g.
DoCmd.OpenReport "stDocName, acViewPreview, , strWhere, strDescrip
Then show it on the report with a text box bound to:
=[Report].[OpenArgs]

If you are using an older version of Access, declare a public string
variable in a standard module, e.g.:
Public gstrReportFilter As String
Then set this before calling the report, e.g.:
gstrReportFilter = "July 2008 only"
and use code in the Format event of the Report Header or whatever section
contains the text box you want to display the filter in, and clear the
string:
If gstrReportFilter <> vbNullString Then
Me.Text0 = gstrReportFilter
gstrReportFilter = Null
End If

Currently I have a form that searches my main table using a user defined
filter which is stored to a sting variable. The best part of that is
that

[quoted text clipped - 21 lines]

to
the report is giving me trouble.