Report Module Problems

**Marshall Barton** · Nov 12 '05, 02:03 PM

Re: Report Module Problems

Jim Fox wrote:
[color=blue]
>I have a report that I created that has no data, just boxes, lines and one
>date field not tied to anything. Basically this report will just print
>pages according to the dates you entered. Then according to the day, it
>will hide certain boxes.
>
>Here is the code (BELOW) I've drummed up, but how do I use it to print out a
>range of pages?
>
>Private Sub Report_Page()
>Dim DDate As Date, StartDay As Date, EndDay As Date
>
>StartDay = InputBox("What is the first day you'd like to print?", "START")
>EndDay = InputBox("What is the last day you'd like to print?", "FINISH")
>
>DDate = StartDay
>
>Do Until DDate = EndDay + 1
>
> Me![B1].Visible = True
> Me![B2].Visible = True
> Me![B3].Visible = True
> Me![B4].Visible = True
> Me![B10].Visible = True
> Me![B11].Visible = True
> If Format(DDate, "DDD") = "Sun" Then
> DDate = DDate + 1
> ElseIf Format(DDate, "DDD") = "Mon" Or Format(DDate, "DDD") = "Thu" Then
> Me![TheDate] = DDate
> Me![B10].Visible = False
> Me![B11].Visible = False
> Me![B1].Visible = False
> Me![B2].Visible = False
> Me![B3].Visible = False
> Me![B4].Visible = False
> ElseIf Format(DDate, "DDD") = "Tue" Then
> Me![TheDate] = DDate
> Me![B10].Visible = False
> Me![B2].Visible = False
> Me![B3].Visible = False
> Me![B4].Visible = False
> ElseIf Format(DDate, "DDD") = "Wed" Then
> Me![TheDate] = DDate
> Me![B1].Visible = False
> Me![B2].Visible = False
> Me![B3].Visible = False
> Me![B4].Visible = False
> Else
> Me![TheDate] = DDate
> End If
>
> DDate = DDate + 1
>
>Loop
>End Sub[/color]

You can not use the Page event to do this.

Move the Dim statement to the report module's declaration
section to make them module level variables.

Place the code to set the initial values of variables in the
report's Open event.

Then use the Detail section Format event to make the text
boxes visible, etc. Discard the Do loop and use this kind
of logic:

Sub Detail_Format( . . .
Me![B1].Visible = True
. . .
DDate = DDate + 1
If DDate < EndDay Then
Me.NextRecord = False
End If
End Sub

You should also beef up the code to get the dates by
checking to make sure the user really entered valid dates,
that the start date is earlier than the end date and that
the end date is not too far out in the future.

--
Marsh
MVP [MS Access]

**Fletcher Arnold** · Nov 12 '05, 02:04 PM

Re: Report Module Problems

"Jim Fox" <jimfox@charter mi.net> wrote in message
news:vlbtojg2ti qg50@corp.super news.com...[color=blue]
> I have a report that I created that has no data, just boxes, lines and one
> date field not tied to anything. Basically this report will just print
> pages according to the dates you entered. Then according to the day, it
> will hide certain boxes.
>
> Here is the code (BELOW) I've drummed up, but how do I use it to print out[/color]
a[color=blue]
> range of pages?
>
> THANKS
>
> Jim
>
> Private Sub Report_Page()
> Dim DDate As Date, StartDay As Date, EndDay As Date
>
> StartDay = InputBox("What is the first day you'd like to print?", "START")
> EndDay = InputBox("What is the last day you'd like to print?", "FINISH")
>
> DDate = StartDay
>
> Do Until DDate = EndDay + 1
>
> Me![B1].Visible = True
> Me![B2].Visible = True
> Me![B3].Visible = True
> Me![B4].Visible = True
> Me![B10].Visible = True
> Me![B11].Visible = True
> If Format(DDate, "DDD") = "Sun" Then
> DDate = DDate + 1
> ElseIf Format(DDate, "DDD") = "Mon" Or Format(DDate, "DDD") = "Thu"[/color]
Then[color=blue]
> Me![TheDate] = DDate
> Me![B10].Visible = False
> Me![B11].Visible = False
> Me![B1].Visible = False
> Me![B2].Visible = False
> Me![B3].Visible = False
> Me![B4].Visible = False
> ElseIf Format(DDate, "DDD") = "Tue" Then
> Me![TheDate] = DDate
> Me![B10].Visible = False
> Me![B2].Visible = False
> Me![B3].Visible = False
> Me![B4].Visible = False
> ElseIf Format(DDate, "DDD") = "Wed" Then
> Me![TheDate] = DDate
> Me![B1].Visible = False
> Me![B2].Visible = False
> Me![B3].Visible = False
> Me![B4].Visible = False
> Else
> Me![TheDate] = DDate
> End If
>
> DDate = DDate + 1
>
> Loop
> End Sub
>[/color]

If you are using A2K2 then you could use the reports OpenArgs, if not you
could define a global variable (g_dteDDate) which is processed in the
report's OpenEvent.

Private Sub Report_Open(Can cel As Integer)

Select Case DatePart("d", g_dteDDate, vbMonday)
Case 1
' Do Monday Stuff
Case 2
' Do Tuesday Stuff
Case 3
' Do Wednesday Stuff
' Etc
End Select

End Sub

Then in your loop, increment the date and print out the report:

g_dteDDate = g_dteDDate + 1
DoCmd.OpenRepor t "MyReport"

**Fletcher Arnold** · Nov 12 '05, 02:04 PM

Re: Report Module Problems

"Marshall Barton" <marshbarton@wo wway.com> wrote in message
news:a42clvgahj shpa41nkfm2pjmo n5sjpicnt@4ax.c om...[color=blue]
> Jim Fox wrote:
>[color=green]
> >I have a report that I created that has no data, just boxes, lines and[/color][/color]
one[color=blue][color=green]
> >date field not tied to anything. Basically this report will just print
> >pages according to the dates you entered. Then according to the day, it
> >will hide certain boxes.
> >
> >Here is the code (BELOW) I've drummed up, but how do I use it to print[/color][/color]
out a[color=blue][color=green]
> >range of pages?
> >
> >Private Sub Report_Page()
> >Dim DDate As Date, StartDay As Date, EndDay As Date
> >
> >StartDay = InputBox("What is the first day you'd like to print?",[/color][/color]
"START")[color=blue][color=green]
> >EndDay = InputBox("What is the last day you'd like to print?", "FINISH")
> >
> >DDate = StartDay
> >
> >Do Until DDate = EndDay + 1
> >
> > Me![B1].Visible = True
> > Me![B2].Visible = True
> > Me![B3].Visible = True
> > Me![B4].Visible = True
> > Me![B10].Visible = True
> > Me![B11].Visible = True
> > If Format(DDate, "DDD") = "Sun" Then
> > DDate = DDate + 1
> > ElseIf Format(DDate, "DDD") = "Mon" Or Format(DDate, "DDD") = "Thu"[/color][/color]
Then[color=blue][color=green]
> > Me![TheDate] = DDate
> > Me![B10].Visible = False
> > Me![B11].Visible = False
> > Me![B1].Visible = False
> > Me![B2].Visible = False
> > Me![B3].Visible = False
> > Me![B4].Visible = False
> > ElseIf Format(DDate, "DDD") = "Tue" Then
> > Me![TheDate] = DDate
> > Me![B10].Visible = False
> > Me![B2].Visible = False
> > Me![B3].Visible = False
> > Me![B4].Visible = False
> > ElseIf Format(DDate, "DDD") = "Wed" Then
> > Me![TheDate] = DDate
> > Me![B1].Visible = False
> > Me![B2].Visible = False
> > Me![B3].Visible = False
> > Me![B4].Visible = False
> > Else
> > Me![TheDate] = DDate
> > End If
> >
> > DDate = DDate + 1
> >
> >Loop
> >End Sub[/color]
>
> You can not use the Page event to do this.
>
> Move the Dim statement to the report module's declaration
> section to make them module level variables.
>
> Place the code to set the initial values of variables in the
> report's Open event.
>
> Then use the Detail section Format event to make the text
> boxes visible, etc. Discard the Do loop and use this kind
> of logic:
>
> Sub Detail_Format( . . .
> Me![B1].Visible = True
> . . .
> DDate = DDate + 1
> If DDate < EndDay Then
> Me.NextRecord = False
> End If
> End Sub
>
> You should also beef up the code to get the dates by
> checking to make sure the user really entered valid dates,
> that the start date is earlier than the end date and that
> the end date is not too far out in the future.
>
> --
> Marsh
> MVP [MS Access][/color]

Hi Marsh

Will this not be difficult if the report is not bound to any data at all?
One idea, he could try would be to use your suggestion but insert the
required dates into a temporary table before opening the report. At least
then the report would be bound and this might be a better solution than my
first suggestion (which would be difficult to print preview multiple days)

Fletcher

**Marshall Barton** · Nov 12 '05, 02:04 PM

Re: Report Module Problems

>> Jim Fox wrote:[color=blue][color=green]
>>[color=darkred]
>> >I have a report that I created that has no data, just boxes, lines and[/color][/color]
>one[color=green][color=darkred]
>> >date field not tied to anything. Basically this report will just print
>> >pages according to the dates you entered. Then according to the day, it
>> >will hide certain boxes.
>> >
>> >Here is the code (BELOW) I've drummed up, but how do I use it to print
>> >out a range of pages?
>> >
>> >Private Sub Report_Page()
>> >Dim DDate As Date, StartDay As Date, EndDay As Date
>> >
>> >StartDay = InputBox("What is the first day you'd like to print?",[/color][/color]
>"START")[color=green][color=darkred]
>> >EndDay = InputBox("What is the last day you'd like to print?", "FINISH")
>> >
>> >DDate = StartDay
>> >
>> >Do Until DDate = EndDay + 1
>> >
>> > Me![B1].Visible = True[/color][/color][/color]
[snip repetitive code][color=blue][color=green][color=darkred]
>> > End If
>> >
>> > DDate = DDate + 1
>> >
>> >Loop
>> >End Sub[/color]
>>[/color]
>"Marshall Barton" wrote[color=green]
>> You can not use the Page event to do this.
>>
>> Move the Dim statement to the report module's declaration
>> section to make them module level variables.
>>
>> Place the code to set the initial values of variables in the
>> report's Open event.
>>
>> Then use the Detail section Format event to make the text
>> boxes visible, etc. Discard the Do loop and use this kind
>> of logic:
>>
>> Sub Detail_Format( . . .
>> Me![B1].Visible = True
>> . . .
>> DDate = DDate + 1
>> If DDate < EndDay Then
>> Me.NextRecord = False
>> End If
>> End Sub
>>
>> You should also beef up the code to get the dates by
>> checking to make sure the user really entered valid dates,
>> that the start date is earlier than the end date and that
>> the end date is not too far out in the future.[/color][/color]

Fletcher Arnold wrote:[color=blue]
>Will this not be difficult if the report is not bound to any data at all?
>One idea, he could try would be to use your suggestion but insert the
>required dates into a temporary table before opening the report. At least
>then the report would be bound and this might be a better solution than my
>first suggestion (which would be difficult to print preview multiple days)[/color]

Having a table of dates and using a parameter query to
select the desired range is a good way to do this as long as
that table is not difficult to manage. However, an unbound
report as I described will work quite nicely without any
additional mechanisms.

An unbound report will print one detail section and the
NextRecord=Fals e will cause it to process that section
repeatedly until NextRecord is left in its default True
state. This can be a very powerful tool in those very rare
situations where you want to print a lot of calculated
values (including handling recordsets that can not be made
to live within the standard Access report limitations).

--
Marsh

**Fletcher Arnold** · Nov 12 '05, 02:04 PM

Re: Report Module Problems

"Marshall Barton" <marshbarton@wo wway.com> wrote in message[color=blue]
>
> Having a table of dates and using a parameter query to
> select the desired range is a good way to do this as long as
> that table is not difficult to manage. However, an unbound
> report as I described will work quite nicely without any
> additional mechanisms.
>
> An unbound report will print one detail section and the
> NextRecord=Fals e will cause it to process that section
> repeatedly until NextRecord is left in its default True
> state. This can be a very powerful tool in those very rare
> situations where you want to print a lot of calculated
> values (including handling recordsets that can not be made
> to live within the standard Access report limitations).[/color]

Thanks for the reply - I'm glad I asked. That's a technique I've never used
before but can imagine it being something useful to keep in my bag of
tricks. I did just try it to make sure I could print out 10 'records' by
using this code:

lng = lng + 1
If lng < 10 Then Me.NextRecord = False

But being the end of a hard day I had Dim lng as Long in the sub itself
(duhh) - so you can imagine the wait to see the last page!

Regards

Fletcher

Report Module Problems

Report Module Problems

Comment

Comment

Comment

Comment

Comment