set text box value in page header via vb

[Analysis&Solutions]

Hi:

I'm using the following Visual Basic code to open a report and set the
value of an unbound text box in its page header. It works... but only on
pages other than the first one.

DoCmd.OpenRepor t strReportName, acViewPreview, , strWhere
Reports(strRepo rtName)("Search CriteriaBox").V alue = strWhere

How can I get it to work on the first page too, please? There are other,
regular text boxes in the page header that contain regular
Control Sources in them, like =Now(), that are working fine. The
report's Page Header setting is "All Pages," as it should be.

Thanks,

--Dan
--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming

http://www.AnalysisAndSolutions.com/

4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409

[lyle fairfield]

Re: set text box value in page header via vb

I can never figure out how General Joseph Stilwell (the father not the
son) could have been an American.

What has this got to do with this question? Well, in your mind
probably nothing. But in my mind, everything, and my mind is the only
mind I have available right now.

If I were doing this I guess I would take a clue from the "Now()"
thingme and create a public function in a standard module called
CriteriaBoxValu e() that returned strWhere and set the value of the
SearchCriteriaB ox Source to CriteriaBoxValu e().
But who knows if that provides what's wanted. Only a direct test will
tell for sure.


On May 12, 10:17 pm, i...@analysisan dsolutions.com
(Analysis&Solut ions) wrote:

Hi:
>
I'm using the following Visual Basic code to open a report and set the
value of an unbound text box in its page header.  It works... but only on
pages other than the first one.
>
   DoCmd.OpenRepor t strReportName, acViewPreview, , strWhere
   Reports(strRepo rtName)("Search CriteriaBox").V alue = strWhere
>
How can I get it to work on the first page too, please?  There are other,
regular text boxes in the page header that contain regular
Control Sources in them, like =Now(), that are working fine.  The
report's Page Header setting is "All Pages," as it should be.
>
Thanks,
>
--Dan
--
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P AN Y
            data intensive web and database programming
               http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409

[fredg]

Re: set text box value in page header via vb

On Tue, 13 May 2008 02:17:04 +0000 (UTC), Analysis&Soluti ons wrote:

Hi:
>
I'm using the following Visual Basic code to open a report and set the
value of an unbound text box in its page header. It works... but only on
pages other than the first one.
>
DoCmd.OpenRepor t strReportName, acViewPreview, , strWhere
Reports(strRepo rtName)("Search CriteriaBox").V alue = strWhere
>
How can I get it to work on the first page too, please? There are other,
regular text boxes in the page header that contain regular
Control Sources in them, like =Now(), that are working fine. The
report's Page Header setting is "All Pages," as it should be.
>
Thanks,
>
--Dan

There are several ways to do this.
Here is one.

On the form, open the report, set the criteria, and pass the criteria
to the report as the OpenArgs argument.

DoCmd.OpenRepor t strReportName, acViewPreview, , strWhere, , strWhere

In the report, assign the OpenArgs to the Page Header unbound control
in the Report's Report Header Format event (the Open event is too
early).
Code the report's ReportHeader format event:

Me.[SearchCriteriaB ox] = Me.OpenArgs

This will display your strWhere clause in the report page header, i.e.
[LastName] = 'Jones'

Is that what you want?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Analysis&Solutions]

Re: set text box value in page header via vb

In <1ns08kpv60bsp$ .z2yenm6304i7.d lg@40tude.netfr edg <fgutkind@examp le.invalidwrite s:

>On the form, open the report, set the criteria, and pass the criteria
>to the report as the OpenArgs argument.

>DoCmd.OpenRepo rt strReportName, acViewPreview, , strWhere, , strWhere

Oh, that's neat. Alas, I'm using the 2000 version, which doesn't have
that. I found another way to do it, which I'll post in the other
response.

Thanks,

--Dan
--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming

http://www.AnalysisAndSolutions.com/

4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409

[Analysis&Solutions]

Re: set text box value in page header via vb

In <8025b659-fbc6-472f-b89e-a148efee037b@59 g2000hsb.google groups.comlyle fairfield <lyle.fairfield @gmail.comwrite s:

>I can never figure out how General Joseph Stilwell (the father not the
>son) could have been an American.

>What has this got to do with this question?

You'll have to clarify. :)

>If I were doing this I guess I would take a clue from the "Now()"
>thingme and create a public function in a standard module...

That's kind of what I did. I created a hidden field on the form where
the criteria are entered and report opened. Then I have the text box on
the report call a function that, in essence, does this (naming changed
for brevity):

If SysCmd(acSysCmd GetObjectState, acForm, "frmName") <0 Then
getCriteria = "Criteria: " & Forms("frmName" )("aName").Valu e
Else
getCriteria = "Criteria: none"
End If

Perhaps not the best, most elegant, way, but it works.

Thanks,

--Dan
--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming

http://www.AnalysisAndSolutions.com/

4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409

[CDMAPoster@fortunejames.com]

Re: set text box value in page header via vb

On May 12, 10:17 pm, i...@analysisan dsolutions.com
(Analysis&Solut ions) wrote:

Hi:
>
I'm using the following Visual Basic code to open a report and set the
value of an unbound text box in its page header.  It works... but only on
pages other than the first one.
>
   DoCmd.OpenRepor t strReportName, acViewPreview, , strWhere
   Reports(strRepo rtName)("Search CriteriaBox").V alue = strWhere
>
How can I get it to work on the first page too, please?  There are other,
regular text boxes in the page header that contain regular
Control Sources in them, like =Now(), that are working fine.  The
report's Page Header setting is "All Pages," as it should be.
>
Thanks,
>
--Dan
--
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P AN Y
            data intensive web and database programming
               http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409

From the A97 Help File: OpenReport Action

The filter and WHERE condition you apply become the setting of the
report's Filter property.

Private Sub Report_Activate ()
Me!txtPageHeade rTextbox.Value = Me.Filter
End Sub

If you don't want parentheses around the string, you can use the
Replace function on "(" and ")" or use (within an Access module):

Public Function RemoveParenthes es(strIn As String) As String
Dim strTemp As String
Dim lngI As Long
Dim strChar As String

RemoveParenthes es = ""
If IsNull(strIn) Or IsEmpty(strIn) Then Exit Function
strTemp = ""
For lngI = 1 To Len(strIn)
strChar = Mid(strIn, lngI, 1)
If strChar <"(" And strChar <")" Then
strTemp = strTemp & strChar
End If
Next lngI
RemoveParenthes es = strTemp
End Function

In the test I tried, strWhere appeared on every page.

James A. Fortune
CDMAPoster@Fort uneJames.com