How to know the last record at the Detail_Format event

Dear all,

At the Detail_Format event, can I know the last record of each page ?
After the last record, I want to add something.
Please let me know the code.
Thanks.

Regards,
Vensia

Vensia wrote:
At the Detail_Format event, can I know the last record of each page ?
After the last record, I want to add something.


The page huh? That's a tricky one that I don't think can be
done in the general case.

If you detail section can not grow or shrink, then it's not
too bad. The last detail will be the one where the Report's
Top property is a fixed position on the page. E.g

If Me.Top 8*1440 Then
'do your thing
End If

The 8 above is the number of inches from the top of the page
where there is only enough room for one more detail.

Note that CanGrow and KeepTogether can cause the Format
event to be fired before it is determined that the section
won't fit on the current page.
--
Marsh
MVP [MS Access]

Actually I want to add a horizontal line after the last record for every
page except the last page.
Is there any idea ?

Vensia

"Marshall Barton" wrote in message
...
Vensia wrote:
At the Detail_Format event, can I know the last record of each page ?
After the last record, I want to add something.


The page huh? That's a tricky one that I don't think can be
done in the general case.

If you detail section can not grow or shrink, then it's not
too bad. The last detail will be the one where the Report's
Top property is a fixed position on the page. E.g

If Me.Top 8*1440 Then
'do your thing
End If

The 8 above is the number of inches from the top of the page
where there is only enough room for one more detail.

Note that CanGrow and KeepTogether can cause the Format
event to be fired before it is determined that the section
won't fit on the current page.
--
Marsh
MVP [MS Access]

Vensia wrote:
Actually I want to add a horizontal line after the last record for every
page except the last page.

Oh good, that's a completely different problem, well, at
least it allows for a different kind of approach. In this
case, the detail doesn't need to know it's the last one on
the page.

Add a textbox named txtBottom to the Page Footer section.
Then add some code to the Detail's Print event:

Const MARGIN As Integer = 0.5 * 1440 '1/2 inch top margin

Private Sub Detail_Print(Cancel As Integer, _
PrintCount As Integer)
Me.txtBottom = Me.Top + Me.Section(0).Height - MARGIN
End Sub

To determine when you are on the last page, you need to have
a text box somewhere on the report that refers to Pages.
The usual expression =Page & " of " & Pages will take care
of it.

With all that in place, you can use the report's Page event
to draw the line using the Line method:

Private Sub Report_Page()
If Me.Page Me.Pages Then
Me.Line (0,Me.txtDetailBottom)-Step(Me.Width,0), vbBlack
End If
End Sub

--
Marsh
MVP [MS Access]

I have tried the code. It doesn't work well.
The problem is there is a subreport (cangrow=true) in detail section.
Detail section just has one record but the subreport has many records.
Thanks.

"Marshall Barton" wrote in message
...
Vensia wrote:
Actually I want to add a horizontal line after the last record for every
page except the last page.

Oh good, that's a completely different problem, well, at
least it allows for a different kind of approach. In this
case, the detail doesn't need to know it's the last one on
the page.

Add a textbox named txtBottom to the Page Footer section.
Then add some code to the Detail's Print event:

Const MARGIN As Integer = 0.5 * 1440 '1/2 inch top margin

Private Sub Detail_Print(Cancel As Integer, _
PrintCount As Integer)
Me.txtBottom = Me.Top + Me.Section(0).Height - MARGIN
End Sub

To determine when you are on the last page, you need to have
a text box somewhere on the report that refers to Pages.
The usual expression =Page & " of " & Pages will take care
of it.

With all that in place, you can use the report's Page event
to draw the line using the Line method:

Private Sub Report_Page()
If Me.Page Me.Pages Then
Me.Line (0,Me.txtDetailBottom)-Step(Me.Width,0), vbBlack
End If
End Sub

--
Marsh
MVP [MS Access]

That should only be a problem if part of the last detail on
a page is not on the page. In that case, I'm not sure if
your question makes sense.

Wait a minute, which detail are you talking about? None of
this will work for details in the subreport because
subreports have no awareness of Page activities (the main
report is in charge of laying out the pages).
--
Marsh
MVP [MS Access]


Vensia wrote:
I have tried the code. It doesn't work well.
The problem is there is a subreport (cangrow=true) in detail section.
Detail section just has one record but the subreport has many records.


Vensia wrote:
Actually I want to add a horizontal line after the last record for every
page except the last page.

"Marshall Barton"wrote
Oh good, that's a completely different problem, well, at
least it allows for a different kind of approach. In this
case, the detail doesn't need to know it's the last one on
the page.

Add a textbox named txtBottom to the Page Footer section.
Then add some code to the Detail's Print event:

Const MARGIN As Integer = 0.5 * 1440 '1/2 inch top margin

Private Sub Detail_Print(Cancel As Integer, _
PrintCount As Integer)
Me.txtBottom = Me.Top + Me.Section(0).Height - MARGIN
End Sub

To determine when you are on the last page, you need to have
a text box somewhere on the report that refers to Pages.
The usual expression =Page & " of " & Pages will take care
of it.

With all that in place, you can use the report's Page event
to draw the line using the Line method:

Private Sub Report_Page()
If Me.Page Me.Pages Then
Me.Line (0,Me.txtDetailBottom)-Step(Me.Width,0), vbBlack
End If
End Sub

I'm talking about the detail section of main report.
I put all of your code in the main report.
The main report has a subreport (can grow) at its detail section.
There is only one record in the detail of main report.
So I find that the height of detail section is fixed when the report is
running.

Vensia


"Marshall Barton" wrote in message
...
That should only be a problem if part of the last detail on
a page is not on the page. In that case, I'm not sure if
your question makes sense.

Wait a minute, which detail are you talking about? None of
this will work for details in the subreport because
subreports have no awareness of Page activities (the main
report is in charge of laying out the pages).
--
Marsh
MVP [MS Access]


Vensia wrote:
I have tried the code. It doesn't work well.
The problem is there is a subreport (cangrow=true) in detail section.
Detail section just has one record but the subreport has many records.


Vensia wrote:
Actually I want to add a horizontal line after the last record for
every
page except the last page.

"Marshall Barton"wrote
Oh good, that's a completely different problem, well, at
least it allows for a different kind of approach. In this
case, the detail doesn't need to know it's the last one on
the page.

Add a textbox named txtBottom to the Page Footer section.
Then add some code to the Detail's Print event:

Const MARGIN As Integer = 0.5 * 1440 '1/2 inch top margin

Private Sub Detail_Print(Cancel As Integer, _
PrintCount As Integer)
Me.txtBottom = Me.Top + Me.Section(0).Height - MARGIN
End Sub

To determine when you are on the last page, you need to have
a text box somewhere on the report that refers to Pages.
The usual expression =Page & " of " & Pages will take care
of it.

With all that in place, you can use the report's Page event
to draw the line using the Line method:

Private Sub Report_Page()
If Me.Page Me.Pages Then
Me.Line (0,Me.txtDetailBottom)-Step(Me.Width,0), vbBlack
End If
End Sub

I thought you said the subreport can grow?? If so, how can
the main report detail section be a fixed size.

Let's go back to the previous attempt, where you said
"it doesn't work well". I think I need you to expand on
that cryptic symptom.

You did say the subreport was the problem, but at this
point, the only problem that comes to my mind is if the
subreport spills over a page boundary. If it does, you
should get a line at the bottom of the first part. I don't
think you've mentioned this scenario so I don't know if you
want a line here or not.
--
Marsh
MVP [MS Access]


Vensia wrote:
I'm talking about the detail section of main report.
I put all of your code in the main report.
The main report has a subreport (can grow) at its detail section.
There is only one record in the detail of main report.
So I find that the height of detail section is fixed when the report is
running.


"Marshall Barton" wrote
That should only be a problem if part of the last detail on
a page is not on the page. In that case, I'm not sure if
your question makes sense.

Wait a minute, which detail are you talking about? None of
this will work for details in the subreport because
subreports have no awareness of Page activities (the main
report is in charge of laying out the pages).


Vensia wrote:
I have tried the code. It doesn't work well.
The problem is there is a subreport (cangrow=true) in detail section.
Detail section just has one record but the subreport has many records.


Vensia wrote:
Actually I want to add a horizontal line after the last record for
every
page except the last page.

"Marshall Barton"wrote
Oh good, that's a completely different problem, well, at
least it allows for a different kind of approach. In this
case, the detail doesn't need to know it's the last one on
the page.

Add a textbox named txtBottom to the Page Footer section.
Then add some code to the Detail's Print event:

Const MARGIN As Integer = 0.5 * 1440 '1/2 inch top margin

Private Sub Detail_Print(Cancel As Integer, _
PrintCount As Integer)
Me.txtBottom = Me.Top + Me.Section(0).Height - MARGIN
End Sub

To determine when you are on the last page, you need to have
a text box somewhere on the report that refers to Pages.
The usual expression =Page & " of " & Pages will take care
of it.

With all that in place, you can use the report's Page event
to draw the line using the Line method:

Private Sub Report_Page()
If Me.Page Me.Pages Then
Me.Line (0,Me.txtDetailBottom)-Step(Me.Width,0), vbBlack
End If
End Sub

At the detail section, there are several text boxes and 2 subreports.
The subreports can grow to several pages.
I have check Me.Section(0).Height at Detail_Print and find the height of
detail section is not changed although the subreport grow to several pages.
That's why the below code doesn't take effect.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.txtBottom = Me.Top + Me.Section(0).Height - MARGIN
End Sub

Thanks.

"Marshall Barton" wrote in message
...
I thought you said the subreport can grow?? If so, how can
the main report detail section be a fixed size.

Let's go back to the previous attempt, where you said
"it doesn't work well". I think I need you to expand on
that cryptic symptom.

You did say the subreport was the problem, but at this
point, the only problem that comes to my mind is if the
subreport spills over a page boundary. If it does, you
should get a line at the bottom of the first part. I don't
think you've mentioned this scenario so I don't know if you
want a line here or not.
--
Marsh
MVP [MS Access]


Vensia wrote:
I'm talking about the detail section of main report.
I put all of your code in the main report.
The main report has a subreport (can grow) at its detail section.
There is only one record in the detail of main report.
So I find that the height of detail section is fixed when the report is
running.


"Marshall Barton" wrote
That should only be a problem if part of the last detail on
a page is not on the page. In that case, I'm not sure if
your question makes sense.

Wait a minute, which detail are you talking about? None of
this will work for details in the subreport because
subreports have no awareness of Page activities (the main
report is in charge of laying out the pages).


Vensia wrote:
I have tried the code. It doesn't work well.
The problem is there is a subreport (cangrow=true) in detail section.
Detail section just has one record but the subreport has many records.


Vensia wrote:
Actually I want to add a horizontal line after the last record for
every
page except the last page.

"Marshall Barton"wrote
Oh good, that's a completely different problem, well, at
least it allows for a different kind of approach. In this
case, the detail doesn't need to know it's the last one on
the page.

Add a textbox named txtBottom to the Page Footer section.
Then add some code to the Detail's Print event:

Const MARGIN As Integer = 0.5 * 1440 '1/2 inch top margin

Private Sub Detail_Print(Cancel As Integer, _
PrintCount As Integer)
Me.txtBottom = Me.Top + Me.Section(0).Height - MARGIN
End Sub

To determine when you are on the last page, you need to have
a text box somewhere on the report that refers to Pages.
The usual expression =Page & " of " & Pages will take care
of it.

With all that in place, you can use the report's Page event
to draw the line using the Line method:

Private Sub Report_Page()
If Me.Page Me.Pages Then
Me.Line (0,Me.txtDetailBottom)-Step(Me.Width,0), vbBlack
End If
End Sub

Thank you for diagnosing my typo. I could have looked at it
forever without seeing it. Change that line to:

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

I'm still concerned about what to do when a detail is split
across a page boundary.
--
Marsh
MVP [MS Access]


Vensia wrote:
At the detail section, there are several text boxes and 2 subreports.
The subreports can grow to several pages.
I have check Me.Section(0).Height at Detail_Print and find the height of
detail section is not changed although the subreport grow to several pages.
That's why the below code doesn't take effect.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.txtBottom = Me.Top + Me.Section(0).Height - MARGIN
End Sub


Vensia wrote:
I'm talking about the detail section of main report.
I put all of your code in the main report.
The main report has a subreport (can grow) at its detail section.
There is only one record in the detail of main report.
So I find that the height of detail section is fixed when the report is
running.