Horizontal lines per column on page

[JoJo]

Good day everyone,

Thanks again for making this site available.

I used the VBA from the post "Un-numbered horizontal lines" except took out
the Me.Print part of the code and it worked great. The problem I'm having
now is I am using the same code in a different report but instead of using it
in the detail section I am using it in a groupheader0 section (Date Header)
and the lines aren't showing up. I tried changing the intDetailHeight As
Integer to intgroupheader0 As Integer throughout the report. It was the only
thing I could think of to do. I just don't understand the principles of code
enough to fix the problem. Also, I need the line broken up - the left line
to start at the left margin and stop at about 3.58 and the right line should
start at 3.85 and go through the rest of the section.
Thanks for helping me!!!

 

[JoJo]

Back again to be a little more clear - I used the horizontal line code in the
Reports On Page event and in the groupheaders On Print event to no avail.
Thought you might need to know that. Thanks!

 

[JoJo]

Okay, I figured out that I had to move the text boxes into the detail section
to get the horizontal lines - but I still would like to know how to get the
left line

 

[Marshall Barton]

Okay, I figured out that I had to move the text boxes into the detail section
to get the horizontal lines - but I still would like to know how to get the
left line

We need more specific information. Under what conditions do
want which line to appear? Exactly what is the code that
you are using?

From what you've said so far, all I can say is that the Line
method (in the page event) would look like:

Me.Line (0, ???) - (3.85*1440, ???)

Where ??? should be the vertical distance (in twips) from
the top of the page.

Actually, at this point, I don't see why you can not use a
line control to do this.

 

[JoJo]

Sorry I didn't make myself clear, and I hope I make myself more clear this
time.
What I want is a two column report (already made) that has horizontal lines
that start at the left margin and stop at 3.58 and then start again at 3.85
and continue to the end of the right margin. The code below is what I'm
using in the OnPage event in the Reports section for Horizontal Lines. Hope
that's a little clearer. Thanks!

Dim intNumLines As Integer
Dim intLineNum As Integer
Dim intDetailHeight As Integer
Dim intPageHeadHeight As Integer
On Error GoTo Report_Page_Error

intNumLines = 14
intDetailHeight = Me.Section(acDetail).Height
intPageHeadHeight = Me.Section(3).Height
For intLineNum = 1 To intNumLines
Me.CurrentY = (intLineNum) * intDetailHeight + intPageHeadHeight
Me.CurrentX = 0
Me.Line (0, intPageHeadHeight + intLineNum * intDetailHeight)- _
Step(Me.Width, 3.5)
Next
On Error GoTo 0
Exit Sub

Report_Page_Error:
Select Case Err
Case 2462 'no page header
intPageHeadHeight = 0
Resume Next
End Select
MsgBox "Error" & Err.Number & "(" & Err.Description & _
")in procedure Report_Page of VBA Document Report_Report1"

 

[Marshall Barton]

Sorry I didn't make myself clear, and I hope I make myself more clear this
time.
What I want is a two column report (already made) that has horizontal lines
that start at the left margin and stop at 3.58 and then start again at 3.85
and continue to the end of the right margin. The code below is what I'm
using in the OnPage event in the Reports section for Horizontal Lines. Hope
that's a little clearer. Thanks!

Dim intNumLines As Integer
Dim intLineNum As Integer
Dim intDetailHeight As Integer
Dim intPageHeadHeight As Integer
On Error GoTo Report_Page_Error

intNumLines = 14
intDetailHeight = Me.Section(acDetail).Height
intPageHeadHeight = Me.Section(3).Height
For intLineNum = 1 To intNumLines
Me.CurrentY = (intLineNum) * intDetailHeight + intPageHeadHeight
Me.CurrentX = 0
Me.Line (0, intPageHeadHeight + intLineNum * intDetailHeight)- _
Step(Me.Width, 3.5)
Next

[snip]

It looks like that code draws slanted lines all over the
page. Let's see if this does any better:

For intLineNum = 1 To intNumLines
Me.Line (0, intPageHeadHeight _
+ intLineNum * intDetailHeight)- _
Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, intPageHeadHeight _
+ intLineNum * intDetailHeight)- _
Step(3.58 * 1440, 0)
Next

 

[JoJo]

The code works great except can you make the lines start in the detail
section of the report instead of the top of the page? Also, the two columns
are for a payroll sheet. The left side of the column is suppose to have the
first week and the right side of the column the second but when I put the
columns setting to read across then down the dates show up like this
(1st column) (2nd column)
First Week Second Week
7/6/07 7/7/07
7/8/07 7/9/07
etc...
If I put the columns setting to read down then across all the dates end up
on the left side of the page. What I would like is this. (The dates are
text boxes)
(1st column) (2nd column)
First Week Second Week
7/9/07 7/16/07
7/10/07 7/17/07
7/11/07 7/18/07
7/12/07 7/19/07
7/13/07 7/20/07
Does what I'm saying make sense? Thanks so much for the help, it is
priceless!!!

--
MADBLover

Sorry I didn't make myself clear, and I hope I make myself more clear this
time.
What I want is a two column report (already made) that has horizontal lines
that start at the left margin and stop at 3.58 and then start again at 3.85
and continue to the end of the right margin. The code below is what I'm
using in the OnPage event in the Reports section for Horizontal Lines. Hope
that's a little clearer. Thanks!

Dim intNumLines As Integer
Dim intLineNum As Integer
Dim intDetailHeight As Integer
Dim intPageHeadHeight As Integer
On Error GoTo Report_Page_Error

intNumLines = 14
intDetailHeight = Me.Section(acDetail).Height
intPageHeadHeight = Me.Section(3).Height
For intLineNum = 1 To intNumLines
Me.CurrentY = (intLineNum) * intDetailHeight + intPageHeadHeight
Me.CurrentX = 0
Me.Line (0, intPageHeadHeight + intLineNum * intDetailHeight)- _
Step(Me.Width, 3.5)
Next

[snip]

It looks like that code draws slanted lines all over the
page. Let's see if this does any better:

For intLineNum = 1 To intNumLines
Me.Line (0, intPageHeadHeight _
+ intLineNum * intDetailHeight)- _
Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, intPageHeadHeight _
+ intLineNum * intDetailHeight)- _
Step(3.58 * 1440, 0)
Next

 

[Marshall Barton]

The code works great except can you make the lines start in the detail
section of the report instead of the top of the page? Also, the two columns
are for a payroll sheet. The left side of the column is suppose to have the
first week and the right side of the column the second but when I put the
columns setting to read across then down the dates show up like this
(1st column) (2nd column)
First Week Second Week
7/6/07 7/7/07
7/8/07 7/9/07
etc...

If I put the columns setting to read down then across all the dates end up
on the left side of the page. What I would like is this. (The dates are
text boxes)
(1st column) (2nd column)
First Week Second Week
7/9/07 7/16/07
7/10/07 7/17/07
7/11/07 7/18/07
7/12/07 7/19/07
7/13/07 7/20/07

Ahhh, I think you might be running down the wrong road.

As long as this is not in a subreport, set the columns to
Down then Across.

You can get a line under/over each detail, just by adding a
line control at the bottom/top of the detail section.

Now, the subtle point is to use Sorting and Grouping (View
menu) to group on a week. Group on an expression like:
=DatePart("ww", thedatefield)
And set the group header section's NewRowOrCol property to
Before Section.

Use a text box in the group header to display the
column/group "label". For starters, just use an expression
like:
="Week " & DatePart("ww", thedatefield)

 

[JoJo]

--
MADBLover

Ahhh, I think you might be running down the wrong road.

As long as this is not in a subreport, set the columns to
Down then Across.

You can get a line under/over each detail, just by adding a
line control at the bottom/top of the detail section.

Now, the subtle point is to use Sorting and Grouping (View
menu) to group on a week. Group on an expression like:
=DatePart("ww", thedatefield)
And set the group header section's NewRowOrCol property to
Before Section.

Use a text box in the group header to display the
column/group "label". For starters, just use an expression
like:
="Week " & DatePart("ww", thedatefield)

Thanks Marsh for your help!!! It looks like the ="Week" & DatePart is just
what is needed. As far as the horizontal lines I need fourteen so just
drawing them above the field in the detail section will only give me lines
until the section runs out. Below is a concoction from examining what you
gave me and some other line methods, it seems to be working pretty
good(learning is an exciting adventure ☺!!) The thing is, you may have been
trying to tell me this all along but I am still learning and don't really
understand the code being given me until it is applied a few times. (P.S.
If the code below looks like too much and there's a shorter way, I don't
doubt there is, then please teach me some more!!! Thank you!!!)
Dim intNumLines As Integer
Dim intLineNum As Integer
Dim intDetailHeight As Integer
Dim intPageHeadHeight As Integer
On Error GoTo Report_Page_Error

intNumLines = 14
intDetailHeight = Me.Section(acDetail).Height
intPageHeadHeight = Me.Section(3).Height
For intLineNum = 1 To intNumLines
Me.Line (0 * 1440, 3700)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 3700)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 3950)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 3950)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 4200)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 4200)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 4450)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 4450)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 4685)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 4685)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 4930)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 4930)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 5165)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 5165)-Step(3.85 * 1440, 0)
Next
On Error GoTo 0
Exit Sub

Report_Page_Error:
Select Case Err
Case 2462 'no page header
intPageHeadHeight = 0
Resume Next
End Select
MsgBox "Error" & Err.Number & "(" & Err.Description & _
")in procedure Report_Page of VBA Document Report_Report1"

 

[Marshall Barton]

Thanks Marsh for your help!!! It looks like the ="Week" & DatePart is just
what is needed. As far as the horizontal lines I need fourteen so just
drawing them above the field in the detail section will only give me lines
until the section runs out. Below is a concoction from examining what you
gave me and some other line methods, it seems to be working pretty
good(learning is an exciting adventure ?!!) The thing is, you may have been
trying to tell me this all along but I am still learning and don't really
understand the code being given me until it is applied a few times. (P.S.
If the code below looks like too much and there's a shorter way, I don't
doubt there is, then please teach me some more!!! Thank you!!!)
Dim intNumLines As Integer
Dim intLineNum As Integer
Dim intDetailHeight As Integer
Dim intPageHeadHeight As Integer
On Error GoTo Report_Page_Error

intNumLines = 14
intDetailHeight = Me.Section(acDetail).Height
intPageHeadHeight = Me.Section(3).Height
For intLineNum = 1 To intNumLines
Me.Line (0 * 1440, 3700)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 3700)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 3950)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 3950)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 4200)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 4200)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 4450)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 4450)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 4685)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 4685)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 4930)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 4930)-Step(3.85 * 1440, 0)
Me.Line (0 * 1440, 5165)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, 5165)-Step(3.85 * 1440, 0)
Next

This should be close to a shorter version:

Dim intNumLines As Integer
Dim intLineNum As Integer
Dim intDetailHeight As Integer
Dim intPageHeadHeight As Integer
Dim lng1stDetail As Long
Dim lngVPos As Long
On Error GoTo Report_Page_Error

intNumLines = 14
intDetailHeight = Me.Section(acDetail).Height
intPageHeadHeight = Me.Section(3).Height
lng1stDetail = 3700 ' intDetailHeight + intPageHeadHeight
For intLineNum = 0 To intNumLines - 1
lngVPos = lng1stDetail + intLineNum * intDetailHeight
Me.Line (0 * 1440, lngVPos)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, lngVPos)-Step(3.58 * 1440, 0)
Next

 

[JoJo]

--
MADBLover

This should be close to a shorter version:

Dim intNumLines As Integer
Dim intLineNum As Integer
Dim intDetailHeight As Integer
Dim intPageHeadHeight As Integer
Dim lng1stDetail As Long
Dim lngVPos As Long
On Error GoTo Report_Page_Error

intNumLines = 14
intDetailHeight = Me.Section(acDetail).Height
intPageHeadHeight = Me.Section(3).Height
lng1stDetail = 3700 ' intDetailHeight + intPageHeadHeight
For intLineNum = 0 To intNumLines - 1
lngVPos = lng1stDetail + intLineNum * intDetailHeight
Me.Line (0 * 1440, lngVPos)-Step(3.58 * 1440, 0)
Me.Line (3.85 * 1440, lngVPos)-Step(3.58 * 1440, 0)
Next

Oh yeah! The shortest route is a straight line (pun intended)!! That saved
me from writing about 14 or more lines of code. Thank you!! I'm still
having problems with the Week 1 in the 1st column and Week 2 in the second
column. What happened with the info you gave me about =DatePart etc... is
that Week 1 ends up in column two and Week 2 goes to the next page. I must
be doing something wrong but there are so many details I'm not sure what it
could be. If you think of something and I'm sure you will, please let me
know. I'm not giving you much to go on am I? I'll be glad when I can start
taking classes on this. Until then thanks for your patience and helping me
see this through.

 

[Marshall Barton]

Oh yeah! The shortest route is a straight line (pun intended)!! That saved
me from writing about 14 or more lines of code. Thank you!! I'm still
having problems with the Week 1 in the 1st column and Week 2 in the second
column. What happened with the info you gave me about =DatePart etc... is
that Week 1 ends up in column two and Week 2 goes to the next page.

Let's see if we can get the first week in the first column.

If you have the report header section, remove it, make its
Height 0, or make it invisible.

If that doesn't help, then try clearing the week group
header section's NewRowOrCol property and set the group
footer section's property to After Section.

 

[JoJo]

--
MADBLover

Let's see if we can get the first week in the first column.

If you have the report header section, remove it, make its
Height 0, or make it invisible.

If that doesn't help, then try clearing the week group
header section's NewRowOrCol property and set the group
footer section's property to After Section.

YeeHah!!! Yeah!!! Joy!!! Hurray!!!! That did it!!!! Thank you, thank you,
thank you. A million times thank you!!!!! It works perfectly, of course.
Until the next time, have a great day and thank you bunches.