Prev: List and count zip codes
Next: #Error Message in Report
From: MKammerer via AccessMonster.com on 9 Jul 2008 08:40
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200807/1

From: Allen Browne on 9 Jul 2008 09:57
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MKammerer via AccessMonster.com" <u44655(a)uwe> wrote in message
news:86e3b30bb4860(a)uwe...
> 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.

From: MKammerer via AccessMonster.com on 9 Jul 2008 10:30
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,

Allen Browne wrote:
>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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200807/1

From: Allen Browne on 9 Jul 2008 10:54
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MKammerer via AccessMonster.com" <u44655(a)uwe> wrote in message
news:86e4a89a37811(a)uwe...
> 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,
>
> Allen Browne wrote:
>>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.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200807/1
>

 | 
Pages: 1
Prev: List and count zip codes
Next: #Error Message in Report