My earlier post was Horizontal Line

[ChoonBoy]

I had alot of help from my earlier posting on Horizontal Line from Marshall
Barton which I am truely grateful.

The issue now is Vertical Line:
I managed to get the code below from Access Report help. The code will draw
vertical lines in relation to textbox growth and for multiple records.

Me.ScaleMode = 1
Me.ForeColor = 0

Me.Line (0 * 1440, 0)-(0.08 * 1440, 14400) 'Draws line at Left Margin
Me.Line (1.04 * 1440, 0)-(1.04 * 1440, 14400) 'At 1 inch

The Problem:
How do I improve on the above code to draw a Veritcal Fixed-height line.
This line should always be drawn whether there is one item in the list or
many.

Any help appreciated. Thanks in advance.

Regards

 

[Marshall Barton]

I had alot of help from my earlier posting on Horizontal Line from Marshall
Barton which I am truely grateful.

The issue now is Vertical Line:
I managed to get the code below from Access Report help. The code will draw
vertical lines in relation to textbox growth and for multiple records.

Me.ScaleMode = 1
Me.ForeColor = 0

Me.Line (0 * 1440, 0)-(0.08 * 1440, 14400) 'Draws line at Left Margin
Me.Line (1.04 * 1440, 0)-(1.04 * 1440, 14400) 'At 1 inch

The Problem:
How do I improve on the above code to draw a Veritcal Fixed-height line.
This line should always be drawn whether there is one item in the list or
many.

If you were using A97 or A2007, I would tell you to RTFM
before asking such a question. Unfortunately, the Help
topic for the Line, Circle and PSet methods is garbled in
versions A2000 through A2003 so I can't do that. Instead,
here's a copy of the A97 Help topic:

object.Line [[Step](x1, y1)]–[Step](x2, y2)[,[color][,B[F]]]

You can use this method only in an event procedure or a
macro specified by the OnPrint or OnFormat event property
for a report section, or the OnPage event property for a
report.

The Line method has the following arguments.

Argument Description
object The Report object on which the line or
rectangle is to be drawn.
Step A keyword that indicates the starting point
coordinates are relative to the current graphics
position given by the settings for the CurrentX
and CurrentY properties of the object argument.
x1, y1 Single values indicating the coordinates of the
starting point for the line or rectangle. The
Scale properties (ScaleMode, ScaleLeft,
ScaleTop, ScaleHeight, and ScaleWidth) of the
Report object specified by the object argument
determine the unit of measure used. If this
argument is omitted, the line begins at the
position indicated by the CurrentX and CurrentY
properties.
Step A keyword that indicates the end-point
coordinates are relative to the line's starting
point.
x2, y2 Single values indicating the coordinates of the
end point for the line to draw. These arguments
are required.
color A Long value indicating the RGB (red-green-blue)
color used to draw the line. If this argument is
omitted, the value of the ForeColor property is
used. You can also use the RGB function or
QBColor function to specify the color.
B An option that creates a rectangle by using the
coordinates as opposite corners of the
rectangle.
F F cannot be used without B. If the B option is
used, the F option specifies that the rectangle
is filled with the same color used to draw the
rectangle. If B is used without F, the rectangle
is filled with the color specified by the
current settings of the FillColor and BackStyle
properties. The default value for the BackStyle
property is Normal for rectangles and lines.

Now to your question.

For a vertical line, you can use the syntax:
Me.Line (X1,Y1)-(X2,Y2)
A vertical line is any line where X1 and X2 are equal (note
that your left margin line is not quite vertical). The
height of the line will be Y2 - Y1.

Or you can use the syntax:
Me.Line (X1,Y1)-Step(0,height)

Based on all that stuff, the trick is to specify the
coordinates of the line's end points. I can not do that for
you unless you can describe them in sufficient detail and if
you can do that, you might not even need to ask the
question. I suggest that you try to describe where the top
and bottom of the line are in relation to one or two text
boxes or maybe within the report section and I'll try to
come up with an expression for the coordinates.

 

[ChoonBoy]

Thanks for helping me on this difficult problem again.

After reading thru your reply, I am still quite loss, please understand that
English is not my first language.

I am currently using A2003.

The objective is to develop an invoice with a fixed-sized table. This table
should be similar in size regardless of the number of record in the A4 page.
If the records are many and fall into the next page, the size of the table
should remain similar as the first page even if it takes only one or 2 rows
of data.

The code below is the exact of my Report module. It can draw the table
according to the number of records in the page. This is not good because the
table is smaller if there are few records and bigger if there are more data.
I prefer it to be standardized.

Option Compare Database
Private lngPos As Long 'from Marsh MVP [MS Access]
_________________________________________________________________________________
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

'draw a horizontal line after the last record
lngPos = Me.Top + Me.Height - Me.Printer.TopMargin 'from Marsh MVP [MS
Access]


'draw 7 vertical lines to separate | Cat | Desc | Class | Qty | Price |
Amount |
'codes from Access Report help

Me.ScaleMode = 1
Me.ForeColor = 0

Me.Line (0 * 1440, 0)-(0.08 * 1440, 14400)
Me.Line (1.04 * 1440, 0)-(1.04 * 1440, 14400)
Me.Line (4.33 * 1440, 0)-(4.33 * 1440, 14400)
Me.Line (4.87 * 1440, 0)-(4.87 * 1440, 14400)
Me.Line (5.42 * 1440, 0)-(5.42 * 1440, 14400)
Me.Line (6.21 * 1440, 0)-(6.21 * 1440, 14400)
Me.Line (7.16 * 1440, 0)-(7.16 * 1440, 14400)

End Sub
__________________________________________________________________________________
Private Sub Report_Page()

Me.Line (0, lngPos)-Step(Me.Width, 0) 'from Marsh MVP [MS Access]

End Sub
_________________________________________________________________________________

Thanks and regards

I had alot of help from my earlier posting on Horizontal Line from Marshall
Barton which I am truely grateful.

The issue now is Vertical Line:
I managed to get the code below from Access Report help. The code will draw
vertical lines in relation to textbox growth and for multiple records.

Me.ScaleMode = 1
Me.ForeColor = 0

Me.Line (0 * 1440, 0)-(0.08 * 1440, 14400) 'Draws line at Left Margin
Me.Line (1.04 * 1440, 0)-(1.04 * 1440, 14400) 'At 1 inch

The Problem:
How do I improve on the above code to draw a Veritcal Fixed-height line.
This line should always be drawn whether there is one item in the list or
many.

If you were using A97 or A2007, I would tell you to RTFM
before asking such a question. Unfortunately, the Help
topic for the Line, Circle and PSet methods is garbled in
versions A2000 through A2003 so I can't do that. Instead,
here's a copy of the A97 Help topic:

object.Line [[Step](x1, y1)]–[Step](x2, y2)[,[color][,B[F]]]

You can use this method only in an event procedure or a
macro specified by the OnPrint or OnFormat event property
for a report section, or the OnPage event property for a
report.

The Line method has the following arguments.

Argument Description
object The Report object on which the line or
rectangle is to be drawn.
Step A keyword that indicates the starting point
coordinates are relative to the current graphics
position given by the settings for the CurrentX
and CurrentY properties of the object argument.
x1, y1 Single values indicating the coordinates of the
starting point for the line or rectangle. The
Scale properties (ScaleMode, ScaleLeft,
ScaleTop, ScaleHeight, and ScaleWidth) of the
Report object specified by the object argument
determine the unit of measure used. If this
argument is omitted, the line begins at the
position indicated by the CurrentX and CurrentY
properties.
Step A keyword that indicates the end-point
coordinates are relative to the line's starting
point.
x2, y2 Single values indicating the coordinates of the
end point for the line to draw. These arguments
are required.
color A Long value indicating the RGB (red-green-blue)
color used to draw the line. If this argument is
omitted, the value of the ForeColor property is
used. You can also use the RGB function or
QBColor function to specify the color.
B An option that creates a rectangle by using the
coordinates as opposite corners of the
rectangle.
F F cannot be used without B. If the B option is
used, the F option specifies that the rectangle
is filled with the same color used to draw the
rectangle. If B is used without F, the rectangle
is filled with the color specified by the
current settings of the FillColor and BackStyle
properties. The default value for the BackStyle
property is Normal for rectangles and lines.

Now to your question.

For a vertical line, you can use the syntax:
Me.Line (X1,Y1)-(X2,Y2)
A vertical line is any line where X1 and X2 are equal (note
that your left margin line is not quite vertical). The
height of the line will be Y2 - Y1.

Or you can use the syntax:
Me.Line (X1,Y1)-Step(0,height)

Based on all that stuff, the trick is to specify the
coordinates of the line's end points. I can not do that for
you unless you can describe them in sufficient detail and if
you can do that, you might not even need to ask the
question. I suggest that you try to describe where the top
and bottom of the line are in relation to one or two text
boxes or maybe within the report section and I'll try to
come up with an expression for the coordinates.

 

[Marshall Barton]

After reading thru your reply, I am still quite loss, please understand that
English is not my first language.

I am currently using A2003.

The objective is to develop an invoice with a fixed-sized table. This table
should be similar in size regardless of the number of record in the A4 page.
If the records are many and fall into the next page, the size of the table
should remain similar as the first page even if it takes only one or 2 rows
of data.

The code below is the exact of my Report module. It can draw the table
according to the number of records in the page. This is not good because the
table is smaller if there are few records and bigger if there are more data.
I prefer it to be standardized.

Option Compare Database
Private lngPos As Long 'from Marsh MVP [MS Access]
_________________________________________________________________________________
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

'draw a horizontal line after the last record
lngPos = Me.Top + Me.Height - Me.Printer.TopMargin 'from Marsh MVP [MS
Access]


'draw 7 vertical lines to separate | Cat | Desc | Class | Qty | Price |
Amount |
'codes from Access Report help

Me.ScaleMode = 1
Me.ForeColor = 0

Me.Line (0 * 1440, 0)-(0.08 * 1440, 14400)
Me.Line (1.04 * 1440, 0)-(1.04 * 1440, 14400)
Me.Line (4.33 * 1440, 0)-(4.33 * 1440, 14400)
Me.Line (4.87 * 1440, 0)-(4.87 * 1440, 14400)
Me.Line (5.42 * 1440, 0)-(5.42 * 1440, 14400)
Me.Line (6.21 * 1440, 0)-(6.21 * 1440, 14400)
Me.Line (7.16 * 1440, 0)-(7.16 * 1440, 14400)

End Sub
__________________________________________________________________________________
Private Sub Report_Page()

Me.Line (0, lngPos)-Step(Me.Width, 0) 'from Marsh MVP [MS Access]

End Sub
_________________________________________________________________________________

Not your native language?? I never would have guessed that.

If you want a grid of lines all the way down the page
regardless of how many details are on the page, then you can
not do it all in the detail section's event procedures.

If the report's Detail section can not grow, then get rid of
all the lines and related code that you've tried and start
over. Use the Page event to draw the entire grid of lines:

'Draw the horizontal lines
GridTop = Me.Printer.TopMargin
GridBottom = {paper height in inches) * 1440 _
- Me.Printer.TopMargin - Me.Printer.BottomMargin
For y = GridTop To GridBottom Step Me.Detail.Height
Me.Line (0, y)-Step(Me.Width, 0) 'horizontal lines
Next y
'Draw the vertical lines
Me.Line (0 * 1440, GridTop)-(0 * 1440, GridBottom)
Me.Line (1.04 * 1440, GridTop)-(1.04 * 1440, GridBottom)
Me.Line (4.33 * 1440, GridTop)-(4.33 * 1440, GridBottom)
Me.Line (4.87 * 1440, GridTop)-(4.87 * 1440, GridBottom)
Me.Line (5.42 * 1440, GridTop)-(5.42 * 1440, GridBottom)
Me.Line (6.21 * 1440, GridTop)-(6.21 * 1440, GridBottom)
Me.Line (7.16 * 1440, GridTop)-(7.16 * 1440, GridBottom)

See how that looks and post back with details about what is
not the way you want it.

 

[ChoonBoy]

Thanks for your reply,

I have done the following

1) Removed all the codes behind the Report except "Option Compare Database"
2) Added this

Private Sub Report_Page()

GridTop = Me.Printer.TopMargin
GridBottom = 11.5 * 1440 - Me.Printer.TopMargin - Me.Printer.BottomMargin
For Y = GridTop To GridBottom Step Me.Detail.Height
Me.Line (0, Y)-Step(Me.Width, 0) 'horizontal lines
Next Y
'Draw the vertical lines
Me.Line (0 * 1440, GridTop)-(0 * 1440, GridBottom)
Me.Line (1.04 * 1440, GridTop)-(1.04 * 1440, GridBottom)
Me.Line (4.33 * 1440, GridTop)-(4.33 * 1440, GridBottom)
Me.Line (4.87 * 1440, GridTop)-(4.87 * 1440, GridBottom)
Me.Line (5.42 * 1440, GridTop)-(5.42 * 1440, GridBottom)
Me.Line (6.21 * 1440, GridTop)-(6.21 * 1440, GridBottom)
Me.Line (7.16 * 1440, GridTop)-(7.16 * 1440, GridBottom)

End Sub

Result:
Not too good, I get lines all over the page. These lines appeared in Report
Header/Footer, Page Header/Footer as well as the Detail section. They stricke
thru all page information (Logo as well as text). In fact it looks like a
Spreadsheet. I only need these lines in the Detail section.

Thanks & regards

 

[Marshall Barton]

I have done the following

1) Removed all the codes behind the Report except "Option Compare Database"
2) Added this [Snip the code]
Result:
Not too good, I get lines all over the page. These lines appeared in Report
Header/Footer, Page Header/Footer as well as the Detail section. They stricke
thru all page information (Logo as well as text). In fact it looks like a
Spreadsheet.

I only need these lines in the Detail section.

No, you want lines in the blank area below the details on
every page. That is why we are using the Page event. I
believe you were in fact trying to get a spreadsheet
appearance, just not over the entire page.

What you got is what I expected and I was waiting for you to
tell me what other sections you have (page header/footer,
group headers/footers and explain any sections that can
grow/shrink or if they are always the same height. Then I
could add some terms to the GridStart and GridEnd
expressions.

I just had another thought. The only thing that really
matters is that the detail section does not grow or shrink.
If that's the case, I think we can change the code to
capture the top of the first detail and the bottom of the
last detail on the page. Try this variation.

Option Compare Database
Option Explicit

Dim GridTop As Long
Dim GridBottom As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)
If GridTop = 0 Then GridTop = Me.Top
GridBottom = Me.Top + Me.Height
End Sub

Private Sub Report_Page()
Dim Y as Long

For Y = GridTop To GridBottom Step Me.Detail.Height
Me.Line (0, Y)-Step(7.16 * 1440, 0) 'horizontal lines
Next Y
'Draw the vertical lines
Me.Line (0 * 1440, GridTop)-(0 * 1440, GridBottom)
Me.Line (1.04 * 1440, GridTop)-(1.04 * 1440, GridBottom)
Me.Line (4.33 * 1440, GridTop)-(4.33 * 1440, GridBottom)
Me.Line (4.87 * 1440, GridTop)-(4.87 * 1440, GridBottom)
Me.Line (5.42 * 1440, GridTop)-(5.42 * 1440, GridBottom)
Me.Line (6.21 * 1440, GridTop)-(6.21 * 1440, GridBottom)
Me.Line (7.16 * 1440, GridTop)-(7.16 * 1440, GridBottom)

GridTop = 0
End Sub

Make sure that the report's OnPage property and the detail
section's OnPrint property contain:
[Event Procedure]

I think that should be close to what you want. Maybe need
to add/subtract a Me.Printer.TopMargin to the Grid
Top/Bottom expressions.

 

[ChoonBoy]

Yes , I am getting closer with the new code.

Now the lines are only in the detail section.

Only issue is I do not need it to look like a spreadsheet. I only want the
Vertical lines (same size irrespective of data) and a horizontal line at the
very top and another one after the last record. It should look line a table.

Thanks for everything and your patients too.

Regards

I have done the following

1) Removed all the codes behind the Report except "Option Compare Database"
2) Added this [Snip the code]
Result:
Not too good, I get lines all over the page. These lines appeared in Report
Header/Footer, Page Header/Footer as well as the Detail section. They stricke
thru all page information (Logo as well as text). In fact it looks like a
Spreadsheet.

I only need these lines in the Detail section.

No, you want lines in the blank area below the details on
every page. That is why we are using the Page event. I
believe you were in fact trying to get a spreadsheet
appearance, just not over the entire page.

What you got is what I expected and I was waiting for you to
tell me what other sections you have (page header/footer,
group headers/footers and explain any sections that can
grow/shrink or if they are always the same height. Then I
could add some terms to the GridStart and GridEnd
expressions.

I just had another thought. The only thing that really
matters is that the detail section does not grow or shrink.
If that's the case, I think we can change the code to
capture the top of the first detail and the bottom of the
last detail on the page. Try this variation.

Option Compare Database
Option Explicit

Dim GridTop As Long
Dim GridBottom As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)
If GridTop = 0 Then GridTop = Me.Top
GridBottom = Me.Top + Me.Height
End Sub

Private Sub Report_Page()
Dim Y as Long

For Y = GridTop To GridBottom Step Me.Detail.Height
Me.Line (0, Y)-Step(7.16 * 1440, 0) 'horizontal lines
Next Y
'Draw the vertical lines
Me.Line (0 * 1440, GridTop)-(0 * 1440, GridBottom)
Me.Line (1.04 * 1440, GridTop)-(1.04 * 1440, GridBottom)
Me.Line (4.33 * 1440, GridTop)-(4.33 * 1440, GridBottom)
Me.Line (4.87 * 1440, GridTop)-(4.87 * 1440, GridBottom)
Me.Line (5.42 * 1440, GridTop)-(5.42 * 1440, GridBottom)
Me.Line (6.21 * 1440, GridTop)-(6.21 * 1440, GridBottom)
Me.Line (7.16 * 1440, GridTop)-(7.16 * 1440, GridBottom)

GridTop = 0
End Sub

Make sure that the report's OnPage property and the detail
section's OnPrint property contain:
[Event Procedure]

I think that should be close to what you want. Maybe need
to add/subtract a Me.Printer.TopMargin to the Grid
Top/Bottom expressions.

 

[Marshall Barton]

Now the lines are only in the detail section.

Only issue is I do not need it to look like a spreadsheet. I only want the
Vertical lines (same size irrespective of data) and a horizontal line at the
very top and another one after the last record. It should look line a table.

But, I thought that's what you had when we started all this.
Earlier in this thread, you said the "table should be
similar in size regardless of the number of records", so I
conclude that you want the lines to extend down the page
beyond the details. What I need to know is where the bottom
of the "table" should be. I still don't know what sections
you have in the report. At one point you said there are
report header, page header and page footer sections, but
what about group headers and footers?

If there are no group footer sections, do you want the
"table" to go all the way to the top of the Page footer
section?

If so, the code could be more like:

Option Compare Database
Option Explicit

Dim GridTop As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)
If GridTop = 0 Then GridTop = Me.Top
End Sub

Private Sub Report_Page()
Dim GridBottom As Long

GridBottom = {paper height in inches} * 1440 _
- Me.Printer.TopMargin _
- Me.Printer.BottomMargin _
- Me.Section(4).Height

'Draw the border rectangle
Me.Line (0, GridTop)-(7.16 * 1440, GridBottom), , B

'Draw the vertical lines inside the border
Me.Line (1.04 * 1440, GridTop)-(1.04 * 1440, GridBottom)
Me.Line (4.33 * 1440, GridTop)-(4.33 * 1440, GridBottom)
Me.Line (4.87 * 1440, GridTop)-(4.87 * 1440, GridBottom)
Me.Line (5.42 * 1440, GridTop)-(5.42 * 1440, GridBottom)
Me.Line (6.21 * 1440, GridTop)-(6.21 * 1440, GridBottom)

GridTop = 0
End Sub

 

[ChoonBoy]

Thank you and sorry for not communicating sufficiently. I am so focused to
have the table in the detail section that I do not see the important of the
other sections. Obviously this is a silly assumption.

I have:

- Report Header
- Page Header
- Detail section
- CustomerID Footer
- Page Footer

I pasted the codes from your last reply and it actually gave me most of what
I wanted. I get the table with vertical lines ending at exactly the same spot
on the next page.

Some minor improvement is needed:

- There are 2 records that is outside the table. This is above the table.
- There are 4 records that is outside the table. This is below the table.

Thanks and regards

 

[ChoonBoy]

My apologies, there are only 2 records that is outside the table. This is
above the table.

Regards

 

[Marshall Barton]

[ However there are 2 records that are ]

above the table.

Change the one line to:

If GridTop = 0 Then GridTop = Me.Top - Me.Printer.TopMargin

What about the CustomerID footer? Is it supposed to be
inside or outside the "table"?

 

[ChoonBoy]

Yes, got it and it is perfect everytime the report is called. Thank you so
much for this.

What about the CustomerID footer? Is it supposed to be
inside or outside the "table"?

Currently this is inside the table.
It would be better to have it outside and joining the bottom of the table.
How to do this?

Thanks and regards

[ However there are 2 records that are ]

above the table.

Change the one line to:

If GridTop = 0 Then GridTop = Me.Top - Me.Printer.TopMargin

What about the CustomerID footer? Is it supposed to be
inside or outside the "table"?

 

[Marshall Barton]

Yes, got it and it is perfect everytime the report is called. Thank you so
much for this.


Currently this is inside the table.
It would be better to have it outside and joining the bottom of the table.

Do you realize that the group footer appears immediately
after the last detail for each CustomerID. If you draw the
lines to the top of the group footer, they can not go down
the page to somewhere near the bottom of the page? This
seems to me to be a contradiction of the problem you are
trying to solve. If it is not a contradiction, please
explain why not and what is going on that makes it ok to
stop the lines at the bottom of the last detail.

If you really want to pursue this, I will need a lot more
details about the group footer section.

Is the report limited to a single CustomerID or can there be
many CustomerIDs?

If there can be more than one CustomerID, do you do
something to start each CustomerID on a new page?

Is the group footer section guaranteed to appear on every
page or is there a chance that there may be more details for
a CustomerID than can fit on a single page?

Can the group footer section Grow or Shrink?

 

[ChoonBoy]

The group footer is actually the total of each CustomerID. I use the code
behind the button to filter only a particular CustomerID. Sometimes a
customerID only has an item for the invoice, occasionally there will be many
items that can spread to Page1, 2, 3 or more pages. It does not matter
whether the invoice has one or many items, the project I am working on
requires that the Invoice be standardized ie. the total below the table and
the table must end about 3 inches above the edge of an A4 size.

The codes you have provided me is fantastic and achieve 80% of my task, I
get the standardized feel except the total (customerID footer) is floating
inside the table according to the rows of item items for each invoice. I
really want the total to be outside and attached below the table.

Code behind button

Dim stDocName As String
Dim stDocNameConsi As String
Dim stLinkCriteria As String
stDocName = "invoice"
stDocNameConsi = "consignment"
If Me!cboPO = "consignment" Then

stLinkCriteria = "[orderid]=" & Forms![Add an Order and Details]![OrderID]
DoCmd.OpenReport stDocNameConsi, acPreview, , stLinkCriteria, WindowNormal

Else

stLinkCriteria = "[orderid]=" & Forms![Add an Order and Details]![OrderID]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria, acWindowNormal

End If

I understand that it must be very difficult from your end to imagine what I
am trying to accomplish with no clear description from my part, but I am
grateful with your effort.

Thanks and regards

 

[Marshall Barton]

What do want to happen if the last detail is below the 3
inches from the bottom of the page?

What about the pages before the last page, do you require
the details to skip to the next page if they reach 3 inches
from the bottom? If so, maybe you can use the Page footer
to display the group totals??
--
Marsh
MVP [MS Access]

The group footer is actually the total of each CustomerID. I use the code
behind the button to filter only a particular CustomerID. Sometimes a
customerID only has an item for the invoice, occasionally there will be many
items that can spread to Page1, 2, 3 or more pages. It does not matter
whether the invoice has one or many items, the project I am working on
requires that the Invoice be standardized ie. the total below the table and
the table must end about 3 inches above the edge of an A4 size.

The codes you have provided me is fantastic and achieve 80% of my task, I
get the standardized feel except the total (customerID footer) is floating
inside the table according to the rows of item items for each invoice. I
really want the total to be outside and attached below the table.

Code behind button

Dim stDocName As String
Dim stDocNameConsi As String
Dim stLinkCriteria As String
stDocName = "invoice"
stDocNameConsi = "consignment"
If Me!cboPO = "consignment" Then

stLinkCriteria = "[orderid]=" & Forms![Add an Order and Details]![OrderID]
DoCmd.OpenReport stDocNameConsi, acPreview, , stLinkCriteria, WindowNormal

Else

stLinkCriteria = "[orderid]=" & Forms![Add an Order and Details]![OrderID]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria, acWindowNormal

End If

Do you realize that the group footer appears immediately
after the last detail for each CustomerID. If you draw the
lines to the top of the group footer, they can not go down
the page to somewhere near the bottom of the page? This
seems to me to be a contradiction of the problem you are
trying to solve. If it is not a contradiction, please
explain why not and what is going on that makes it ok to
stop the lines at the bottom of the last detail.

If you really want to pursue this, I will need a lot more
details about the group footer section.

Is the report limited to a single CustomerID or can there be
many CustomerIDs?

If there can be more than one CustomerID, do you do
something to start each CustomerID on a new page?

Is the group footer section guaranteed to appear on every
page or is there a chance that there may be more details for
a CustomerID than can fit on a single page?

Can the group footer section Grow or Shrink?

 

[ChoonBoy]

Actually the 3" allowance between the bottom of the table and the A4 page is
for signatory, company chop, date printed and count of pages (Placed in Page
footer).

I had on an earlier stage tried putting the CustomerID total in the Page
Footer and the following was observed:
1) The apperance of the invoice is 100% acceptable. I get the table created
with your codes and the total attached to the bottom.
2) The problem is the total appear in every pages if the Invoice has many
items. It should only appear on the last page.
3) Some how the formula does not work, I get #error
(=Sum([quantity]*[unitprice]))

If Nos 2 & 3 can be overcome, than this is the perfect solution.

Thanks again for your assistant. It really is difficult for me.

What do want to happen if the last detail is below the 3
inches from the bottom of the page?

What about the pages before the last page, do you require
the details to skip to the next page if they reach 3 inches
from the bottom? If so, maybe you can use the Page footer
to display the group totals??
--
Marsh
MVP [MS Access]

The group footer is actually the total of each CustomerID. I use the code
behind the button to filter only a particular CustomerID. Sometimes a
customerID only has an item for the invoice, occasionally there will be many
items that can spread to Page1, 2, 3 or more pages. It does not matter
whether the invoice has one or many items, the project I am working on
requires that the Invoice be standardized ie. the total below the table and
the table must end about 3 inches above the edge of an A4 size.

The codes you have provided me is fantastic and achieve 80% of my task, I
get the standardized feel except the total (customerID footer) is floating
inside the table according to the rows of item items for each invoice. I
really want the total to be outside and attached below the table.

Code behind button

Dim stDocName As String
Dim stDocNameConsi As String
Dim stLinkCriteria As String
stDocName = "invoice"
stDocNameConsi = "consignment"
If Me!cboPO = "consignment" Then

stLinkCriteria = "[orderid]=" & Forms![Add an Order and Details]![OrderID]
DoCmd.OpenReport stDocNameConsi, acPreview, , stLinkCriteria, WindowNormal

Else

stLinkCriteria = "[orderid]=" & Forms![Add an Order and Details]![OrderID]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria, acWindowNormal

End If

ChoonBoy wrote:

Yes, got it and it is perfect everytime the report is called. Thank you so
much for this.

What about the CustomerID footer? Is it supposed to be
inside or outside the "table"?

Currently this is inside the table.
It would be better to have it outside and joining the bottom of the table.


Do you realize that the group footer appears immediately
after the last detail for each CustomerID. If you draw the
lines to the top of the group footer, they can not go down
the page to somewhere near the bottom of the page? This
seems to me to be a contradiction of the problem you are
trying to solve. If it is not a contradiction, please
explain why not and what is going on that makes it ok to
stop the lines at the bottom of the last detail.

If you really want to pursue this, I will need a lot more
details about the group footer section.

Is the report limited to a single CustomerID or can there be
many CustomerIDs?

If there can be more than one CustomerID, do you do
something to start each CustomerID on a new page?

Is the group footer section guaranteed to appear on every
page or is there a chance that there may be more details for
a CustomerID than can fit on a single page?

Can the group footer section Grow or Shrink?

 

[Marshall Barton]

Actually the 3" allowance between the bottom of the table and the A4 page is
for signatory, company chop, date printed and count of pages (Placed in Page
footer).

I had on an earlier stage tried putting the CustomerID total in the Page
Footer and the following was observed:
1) The apperance of the invoice is 100% acceptable. I get the table created
with your codes and the total attached to the bottom.
2) The problem is the total appear in every pages if the Invoice has many
items. It should only appear on the last page.
3) Some how the formula does not work, I get #error
(=Sum([quantity]*[unitprice]))

If Nos 2 & 3 can be overcome, than this is the perfect solution.

#3 is not much of a problem. The calculation must be done
in the group footer, but the page footer can display it by
just using a text box with an expression that references the
text box in the group footer:
=groupfootertotaltextbox

Make the group footer invisible, because it is only used to
calculate the total.

For #2, you can make the page footer total text box
invisible so it won't show until the group footer makes it
visible. This only needs a line of code in the group footer
section's Print event:
If Me.Pages > 0 Then
Me.pagefootertextbox.Visible = True
End If

Or, the same thing in fewer lines:
Me.pagefootertextbox.Visible = (Me.Pages > 0)

 

[ChoonBoy]

Thank you, this is the result of the added codes

What is perfect:

- I get what I want ie. the total below and attached to the table
- The table is the same size everytime irrespective of the number of records.

What need to be improved:

- The total appear in all pages with continuing data (invoice with many
items that fill more than one page). Should appear only on the last page.

Thanks for all you help. Regarda

What do want to happen if the last detail is below the 3
inches from the bottom of the page?

What about the pages before the last page, do you require
the details to skip to the next page if they reach 3 inches
from the bottom? If so, maybe you can use the Page footer
to display the group totals??
--
Marsh
MVP [MS Access]

The group footer is actually the total of each CustomerID. I use the code
behind the button to filter only a particular CustomerID. Sometimes a
customerID only has an item for the invoice, occasionally there will be many
items that can spread to Page1, 2, 3 or more pages. It does not matter
whether the invoice has one or many items, the project I am working on
requires that the Invoice be standardized ie. the total below the table and
the table must end about 3 inches above the edge of an A4 size.

The codes you have provided me is fantastic and achieve 80% of my task, I
get the standardized feel except the total (customerID footer) is floating
inside the table according to the rows of item items for each invoice. I
really want the total to be outside and attached below the table.

Code behind button

Dim stDocName As String
Dim stDocNameConsi As String
Dim stLinkCriteria As String
stDocName = "invoice"
stDocNameConsi = "consignment"
If Me!cboPO = "consignment" Then

stLinkCriteria = "[orderid]=" & Forms![Add an Order and Details]![OrderID]
DoCmd.OpenReport stDocNameConsi, acPreview, , stLinkCriteria, WindowNormal

Else

stLinkCriteria = "[orderid]=" & Forms![Add an Order and Details]![OrderID]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria, acWindowNormal

End If

ChoonBoy wrote:

Yes, got it and it is perfect everytime the report is called. Thank you so
much for this.

What about the CustomerID footer? Is it supposed to be
inside or outside the "table"?

Currently this is inside the table.
It would be better to have it outside and joining the bottom of the table.


Do you realize that the group footer appears immediately
after the last detail for each CustomerID. If you draw the
lines to the top of the group footer, they can not go down
the page to somewhere near the bottom of the page? This
seems to me to be a contradiction of the problem you are
trying to solve. If it is not a contradiction, please
explain why not and what is going on that makes it ok to
stop the lines at the bottom of the last detail.

If you really want to pursue this, I will need a lot more
details about the group footer section.

Is the report limited to a single CustomerID or can there be
many CustomerIDs?

If there can be more than one CustomerID, do you do
something to start each CustomerID on a new page?

Is the group footer section guaranteed to appear on every
page or is there a chance that there may be more details for
a CustomerID than can fit on a single page?

Can the group footer section Grow or Shrink?

 

[Marshall Barton]

Thank you, this is the result of the added codes

What is perfect:

- I get what I want ie. the total below and attached to the table
- The table is the same size everytime irrespective of the number of records.

What need to be improved:

- The total appear in all pages with continuing data (invoice with many
items that fill more than one page). Should appear only on the last page.

You're right. I should not try to answer questions so early
in the morning ;-)

The code is a little more complicated than just checking
Pages. Let's change that to something more like:

In the detail section's Print event:
Me.pagefootertextbox.Visible = False
and in the group footer section's Print event, just this one
line:
Me.pagefootertextbox.Visible = True

 

[ChoonBoy]

The result is fantastic, thank you for your help thru the end, even during
the weekend.

Regards