Help with drawing horizontal line in Access subreport 2003

J

jeremy.wy.chong

Hi,
Ok in a nutshell I need to draw a horizontal line at the bottom of
each page. Now I followed this thread
http://groups.google.co.nz/group/mi...reports/browse_thread/thread/78f430839b0c12a6
but the problem is that this is used in a subreport so the page footer
is not executed!

The ways of thought I have gone about (but not known how to execute)
are as follows:
1. If detail row is last detail row on page then draw a horizontal
line. PROBLEM: how can I tell if it is the last detail row on the
page?
OR
2. Use the above thread example PROBLEM: report_page event does not
fire in a subreport so need to find alternative event????

I have already trawled the net for answers with no real luck so this
is my last resort! Any help would be greatly appreciated.
 
B

Brendan Reynolds

Hi,
Ok in a nutshell I need to draw a horizontal line at the bottom of
each page. Now I followed this thread
http://groups.google.co.nz/group/mi...reports/browse_thread/thread/78f430839b0c12a6
but the problem is that this is used in a subreport so the page footer
is not executed!

The ways of thought I have gone about (but not known how to execute)
are as follows:
1. If detail row is last detail row on page then draw a horizontal
line. PROBLEM: how can I tell if it is the last detail row on the
page?
OR
2. Use the above thread example PROBLEM: report_page event does not
fire in a subreport so need to find alternative event????

I have already trawled the net for answers with no real luck so this
is my last resort! Any help would be greatly appreciated.
Click to expand...


A subreport doesn't have pages, so you can't do anything for each page in a
subreport. They just don't exist. It's the containing report that has pages.
What is it that you're trying to do that can't be done by placing the line
in the page footer of the containing report?
 
M

Marshall Barton

Ok in a nutshell I need to draw a horizontal line at the bottom of
each page. Now I followed this thread
http://groups.google.co.nz/group/mi...reports/browse_thread/thread/78f430839b0c12a6
but the problem is that this is used in a subreport so the page footer
is not executed!

The ways of thought I have gone about (but not known how to execute)
are as follows:
1. If detail row is last detail row on page then draw a horizontal
line. PROBLEM: how can I tell if it is the last detail row on the
page?
OR
2. Use the above thread example PROBLEM: report_page event does not
fire in a subreport so need to find alternative event????
Click to expand...


Subreports are unaware of page boundaries because the main
report is "in charge" of pages.

If you really want a line at the bottom of **every** page,
put a line control in the main report's page footer section.

If you only want a line at the bottom of the subreport's
last detail, out a text box (named txtDtlCnt) in the
subreport's report header section and set it's control
source to =Count(*)

Add a line control (named linLast) to the bottom of the
detail section.

Then add a text box (named txtDetail) to the subreport's
detail section. Set its control source to =1 and RunningSum
to Over All. Then you can use code in the detail section
Format event procedure to make the line control visible or
not with this kind of code:

Me.linLast.Visible = (txtDetail=txtDtlCnt)
 
J

jeremy.wy.chong

Hi Brendan and Marsh, thank you for your input. I just want a line at
the bottom of each page in the subreport, not all pages in the main
report! The reason why I need to do this is that in the subreport I
have drawn borders around detail rows based on cells not being
repeated (ie. some columns in my dataset have lots of repeating rows
so I turn hideduplicates on and draw borders accordingly). The problem
with this is that it doesn't draw a border on the last detail row on
the page because the columns are still repeating data (and therefore
not drawing a horizontal line). This is my code, perhaps it could be
modified to be a bit smarter???

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

On Error Resume Next
Dim lngCounter As Long, dblMaxHeight As Double
dblMaxHeight = 0
ReDim strcontrol(6)
'SET CONTROLS
strcontrol(0) = "Team_Title"
strcontrol(1) = "LoS_Description"
strcontrol(2) = "action_description"
strcontrol(3) = "measure_description"
strcontrol(4) = "Source_Name"
'GET HEIGHT OF TALLEST CONTROL
For lngCounter = 0 To UBound(strcontrol)
If Me(strcontrol(lngCounter)).Height > dblMaxHeight Then dblMaxHeight
= Me(strcontrol(lngCounter)).Height
Next

For lngCounter = 0 To UBound(strcontrol) - 1
If strcontrol(lngCounter) = "measure_description" Then
'DRAW BOX BECAUSE THIS IS NEVER REPEATED
Me.Line (Me(strcontrol(lngCounter)).Left,
Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width,
dblMaxHeight), , B
Else
'DRAW HORIZONTAL LINE
Me.Line (Me(strcontrol(lngCounter)).Left,
Me(strcontrol(lngCounter)).Top)-Step(5, dblMaxHeight), , B
End If
Next


If previous_los.Caption <> LoS_Description Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![LoS_Description].Left, Me![LoS_Description].Top)-
Step(Me![LoS_Description].Width, 5), , B
End If
previous_los.Caption = LoS_Description

If previous_action.Caption <> Action_Description Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![Action_Description].Left, Me!
[Action_Description].Top)-Step(Me![Action_Description].Width, 5), , B
End If
previous_action.Caption = Action_Description

If previous_source_name.Caption <> Source_Name Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![Source_Name].Left, Me![Source_Name].Top)-Step(Me!
[Source_Name].Width, 5), , B
End If
previous_source_name.Caption = Source_Name

If previous_team_title.Caption <> Team_Title Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![Team_Title].Left, Me![Team_Title].Top)-Step(Me!
[Team_Title].Width, 5), , B
End If
previous_team_title.Caption = Team_Title

Me.Line (Me![Source_Name].Left + Me![Source_Name].Width, Me!
[Source_Name].Top)-Step(5, dblMaxHeight), , B

Me.txtbottom = Me.Top + Me.Height - MARGIN

End Sub
 
J

jeremy.wy.chong

Hi Brendan and Marsh, thank you for your input. I just want a line at
the bottom of each page in the subreport, not all pages in the main
report! The reason why I need to do this is that in the subreport I
have drawn borders around detail rows based on cells not being
repeated (ie. some columns in my dataset have lots of repeating rows
so I turn hideduplicates on and draw horizonal lines accordingly). The
problem
with this is that it doesn't draw a horizontal line on the last detail
row on
the page because the columns are still repeating data (and therefore
not drawing a horizontal line). This is my code, perhaps it could be
modified to be a bit smarter???

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)


On Error Resume Next
Dim lngCounter As Long, dblMaxHeight As Double
dblMaxHeight = 0
ReDim strcontrol(6)
'SET CONTROLS
strcontrol(0) = "Team_Title"
strcontrol(1) = "LoS_Description"
strcontrol(2) = "action_description"
strcontrol(3) = "measure_description"
strcontrol(4) = "Source_Name"
'GET HEIGHT OF TALLEST CONTROL
For lngCounter = 0 To UBound(strcontrol)
If Me(strcontrol(lngCounter)).Height > dblMaxHeight Then dblMaxHeight
= Me(strcontrol(lngCounter)).Height
Next


For lngCounter = 0 To UBound(strcontrol) - 1
If strcontrol(lngCounter) = "measure_description" Then
'DRAW BOX BECAUSE THIS IS NEVER REPEATED
Me.Line (Me(strcontrol(lngCounter)).Left,
Me(strcontrol(lngCounter)).Top)-
Step(Me(strcontrol(lngCounter)).Width,
dblMaxHeight), , B
Else
'DRAW HORIZONTAL LINE
Me.Line (Me(strcontrol(lngCounter)).Left,
Me(strcontrol(lngCounter)).Top)-Step(5, dblMaxHeight), , B
End If
Next


If previous_los.Caption <> LoS_Description Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![LoS_Description].Left, Me![LoS_Description].Top)-
Step(Me![LoS_Description].Width, 5), , B
End If
previous_los.Caption = LoS_Description


If previous_action.Caption <> Action_Description Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![Action_Description].Left, Me!
[Action_Description].Top)-Step(Me![Action_Description].Width, 5), , B
End If
previous_action.Caption = Action_Description


If previous_source_name.Caption <> Source_Name Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![Source_Name].Left, Me![Source_Name].Top)-Step(Me!
[Source_Name].Width, 5), , B
End If
previous_source_name.Caption = Source_Name


If previous_team_title.Caption <> Team_Title Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![Team_Title].Left, Me![Team_Title].Top)-Step(Me!
[Team_Title].Width, 5), , B
End If
previous_team_title.Caption = Team_Title


Me.Line (Me![Source_Name].Left + Me![Source_Name].Width, Me!
[Source_Name].Top)-Step(5, dblMaxHeight), , B



End Sub
 
M

Marshall Barton

Hi Brendan and Marsh, thank you for your input. I just want a line at
the bottom of each page in the subreport, not all pages in the main
report! The reason why I need to do this is that in the subreport I
have drawn borders around detail rows based on cells not being
repeated (ie. some columns in my dataset have lots of repeating rows
so I turn hideduplicates on and draw borders accordingly). The problem
with this is that it doesn't draw a border on the last detail row on
the page because the columns are still repeating data (and therefore
not drawing a horizontal line). This is my code, perhaps it could be
modified to be a bit smarter???

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

On Error Resume Next
Dim lngCounter As Long, dblMaxHeight As Double
dblMaxHeight = 0
ReDim strcontrol(6)
'SET CONTROLS
strcontrol(0) = "Team_Title"
strcontrol(1) = "LoS_Description"
strcontrol(2) = "action_description"
strcontrol(3) = "measure_description"
strcontrol(4) = "Source_Name"
'GET HEIGHT OF TALLEST CONTROL
For lngCounter = 0 To UBound(strcontrol)
If Me(strcontrol(lngCounter)).Height > dblMaxHeight Then dblMaxHeight
= Me(strcontrol(lngCounter)).Height
Next

For lngCounter = 0 To UBound(strcontrol) - 1
If strcontrol(lngCounter) = "measure_description" Then
'DRAW BOX BECAUSE THIS IS NEVER REPEATED
Me.Line (Me(strcontrol(lngCounter)).Left,
Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width,
dblMaxHeight), , B
Else
'DRAW HORIZONTAL LINE
Me.Line (Me(strcontrol(lngCounter)).Left,
Me(strcontrol(lngCounter)).Top)-Step(5, dblMaxHeight), , B
End If
Next


If previous_los.Caption <> LoS_Description Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![LoS_Description].Left, Me![LoS_Description].Top)-
Step(Me![LoS_Description].Width, 5), , B
End If
previous_los.Caption = LoS_Description

If previous_action.Caption <> Action_Description Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![Action_Description].Left, Me!
[Action_Description].Top)-Step(Me![Action_Description].Width, 5), , B
End If
previous_action.Caption = Action_Description

If previous_source_name.Caption <> Source_Name Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![Source_Name].Left, Me![Source_Name].Top)-Step(Me!
[Source_Name].Width, 5), , B
End If
previous_source_name.Caption = Source_Name

If previous_team_title.Caption <> Team_Title Then
'CONTROL TEXT IS NOT DUPLICATE SO DRAW HORIZONTAL LINE TO SIGNIFY BOX
Me.Line (Me![Team_Title].Left, Me![Team_Title].Top)-Step(Me!
[Team_Title].Width, 5), , B
End If
previous_team_title.Caption = Team_Title

Me.Line (Me![Source_Name].Left + Me![Source_Name].Width, Me!
[Source_Name].Top)-Step(5, dblMaxHeight), , B

Me.txtbottom = Me.Top + Me.Height - MARGIN

End Sub
Click to expand...


Saving a field's value from a previous and the comparing it
to the field's value in the current record is not a safe
approach to anything in a report. The reason it's not safe
is because a report may have to retreat and reformat any
number of sections in any order to deal with various
combinations of options such as Pages, the different kinds
of KeepTogether, forceNewPage, etc. This may or may not be
related to your question, but it will cause you problems
sometime/somewhere.

In your situation, I think you can use the IsVisible
property to determine if a text box is a hidden duplicate or
not. Using it should also make your code a lot easier to
write and understand.

It's also possible that the line that's not showing is just
below the bottom of the section. Try subtracting 10 or 20
from the max height part of the Line method.
 
J

jeremy.wy.chong

Hi Marshall,
Thank you for your input about the IsVisible. It makes my code much
easier and efficient!

As for the the horizontal line, I tried changing the max height but it
doesn't work. The fact is that the line is not drawn because the last
detail row on the page is a duplicate! So therefore I really need some
sort of workaround to draw a line at the bottom of each subreport page
OR I need to rework my entire method of drawing the lines (which I do
not want to do).
 
M

Marshall Barton

Thank you for your input about the IsVisible. It makes my code much
easier and efficient!

As for the the horizontal line, I tried changing the max height but it
doesn't work. The fact is that the line is not drawn because the last
detail row on the page is a duplicate! So therefore I really need some
sort of workaround to draw a line at the bottom of each subreport page
OR I need to rework my entire method of drawing the lines (which I do
not want to do).
Click to expand...


Did you try using the =Count(*) and =1 text boxes I
suggested earlier? The general idea should still apply even
if you need to draw the line in code.
 
J

jeremy.wy.chong

Hi Marshall,
I tried this code but it just draws the horizontal line for the last
detail for the subreport rather than the last detail on each page of
the subreport. Is there a way around this? I tried putting the
=count(*) in the page header but it just comes up with #ERROR.
 
M

Marshall Barton

I tried this code but it just draws the horizontal line for the last
detail for the subreport rather than the last detail on each page of
the subreport. Is there a way around this? I tried putting the
=count(*) in the page header but it just comes up with #ERROR.
Click to expand...


Sorry, I thought that what's you wanted.

The last detail on each page can be a problem when the
detail can grow because you can not tell if the next detail
will fit on the same page.

If this were in the main report, you could save the bottom
of each detail section in a module level variable or a
hidden text box. Then the Page event could draw the line at
the saved position. Maybe(?) there's some way for the
subreport to trigger the main report to take care of it??

If the detail were not allowed to grow. You could then use
code something like this:

If pageheight - bottommargin - Me.Top < 2 * Me.Height Then

to check for the last detail on a page
 
J

jeremy.wy.chong

Hi Marsh,
Thanks for your input but unfortunately I really need the textboxes to
be able to grow! See I have absolutely no problems doing this if it
was the main report but the real problem is that I need this as a
subreport! Do you have any more ideas?

Much appreciated for your time and effort so far.
 
M

Marshall Barton

Thanks for your input but unfortunately I really need the textboxes to
be able to grow! See I have absolutely no problems doing this if it
was the main report but the real problem is that I need this as a
subreport! Do you have any more ideas?
Click to expand...

Have you tried getting the main report's Page event to draw
the line?

If you don't have any idea how to go about that, please post
all the details about the line: start position; end
position or length; and vertical position within the detail
section. With that info, I'll see what, if anything I can
figure out.
 
J

jeremy.wy.chong

Hi Marsh,
I will give it a go with the main report drawing the line in the page
event. I assume I can check with code which subform is showing and
therefore draw the line only when that subform is showing. I'll give
it a go and let you know how I go, thanks
 
J

jeremy.wy.chong

I gave it a go but I have no idea what sort of syntax to use to check
if that subreport is showing on the current page of the main
report..... it also makes it very tricky that the subreport I am
checking is actually a subreport in a subreport of the main report...
doh!
 
M

Marshall Barton

I gave it a go but I have no idea what sort of syntax to use to check
if that subreport is showing on the current page of the main
report..... it also makes it very tricky that the subreport I am
checking is actually a subreport in a subreport of the main report...
Click to expand...


Hoo Boy, that does make it complicated indeed. Give me a
day or two to find time to investigate all this.
 
M

Marshall Barton

Thanks heaps for your help Marshall, it is much appreciated.
Click to expand...


Does that mean that you have found a way to get what you
want? If so, please explain what you did so we can all
learn from it.

If you have not suceeded, then I still need to know how to
determine the vertical position of the line in the detail
section (at the very bottom?) along with the line's
horizontal starting point and either its end point or
length.

I am still struggling to find a block of time to investigate
an idea. Tomorrow morning may be a possibility.
 
M

Marshall Barton

I gave it a go but I have no idea what sort of syntax to use to check
if that subreport is showing on the current page of the main
report..... it also makes it very tricky that the subreport I am
checking is actually a subreport in a subreport of the main report...
Click to expand...


Well, this seems to work in my quicky tests:

Main report code:
---------------------------------------------------------
Public glngTopMargin As Long
Public glngY As Long, glngX As Long, glngW As Long

Private Sub Report_Open(Cancel As Integer)
glngTopMargin = Me.Printer.TOPMARGIN
End Sub

Private Sub Report_Page()
If glngW > 0 Then
Me.Line (glngX, glngY - glngTopMargin)-Step(glngW,
0), vbRed
End If
End Sub
---------------------------------------------------------

Subreport code:
---------------------------------------------------------
Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)
With Reports!report11
.glngY = IIf(.Top > 0, .Top, .glngTopMargin) _
+ Me.Top + Me.FreeText.Top + Me.FreeText.Height
.glngX = Me.Left + Me.FreeText.Left
.glngW = Me.FreeText.Width
End With
End Sub

Private Sub ReportFooter_Format(Cancel As Integer,
FormatCount As Integer)
Reports!report11.glngW = 0
End Sub
---------------------------------------------------------

Watch out for line wrapping.

In that code, the main report is named Report11

Since you never did get back to me with how you determine
the line's starting point and its width, I just used the
bottom of the FreeText text box in my test subreport.

I did not use a third level of subreport so you'll have to
add another another Top into the messy expression. I think
it could be something like:
.glngY = IIf(.Top > 0, .Top, .glngTopMargin) _
+ Parent.Top + Parent.firstsubreport.Top _
+ Me.Top + Me.FreeText.Top + Me.FreeText.Height

The subsubreport's detail section must gave its KeepTogether
property set to Yes.
 
J

jeremy.wy.chong

Sorry I wasn't able to answer your previous question quickly enough
but the solution you have given works perfectly! Thank you so much
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report/Subreport Problems with Sorting/Grouping, Access 2003 2
Subreport is not showing horizontal lines 11
Vertical Line Help 2
place horizontal line under detail section 5
Subreport page break in label controls or textbox controls 6
Subreport with no records 8
horizontal line is creeping down the page 1
Vertical Line Issues 2

Top