By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,149 Members | 885 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,149 IT Pros & Developers. It's quick & easy.

Report Module Problems

P: n/a
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?

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" 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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Jim Fox wrote:
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


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]
Nov 12 '05 #2

P: n/a
"Jim Fox" <ji****@chartermi.net> wrote in message
news:vl************@corp.supernews.com...
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?

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" 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


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(Cancel 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.OpenReport "MyReport"
Nov 12 '05 #3

P: n/a
"Marshall Barton" <ma*********@wowway.com> wrote in message
news:a4********************************@4ax.com...
Jim Fox wrote:
I have a report that I created that has no data, just boxes, lines and onedate 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 arange 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


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]


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
Nov 12 '05 #4

P: n/a
>> Jim Fox wrote:
>I have a report that I created that has no data, just boxes, lines andone >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 [snip repetitive code] > End If
>
> DDate = DDate + 1
>
>Loop
>End Sub

"Marshall Barton" wrote
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.


Fletcher Arnold wrote: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)


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=False 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
Nov 12 '05 #5

P: n/a
"Marshall Barton" <ma*********@wowway.com> wrote in message

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=False 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).

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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.