VBA question for adding blank lines to reports

[Jaron]

Hi,


awhile back I asked a question about how to add blank lines to a report so that they print out on the paper copy. Dal Jeanis was kind enough to give me the following code, which works very well by adding alternating blank and shaded rows that match the report defaults:

Private LinePos As Long
Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
     LinePos = Me.Top + 0.1 * Me.Section(0).Height
End Sub
Private Sub Report_Page()
Dim bolWhite As Boolean
Dim LineBot As Long
Dim colGrey As Long
Const BoxPercent = 0.95
    
    bolWhite = False
    colGrey = RGB(240, 240, 240)
    For LinePos = LinePos To 7.3 * 1440 _
                                        Step Me.Section(0).Height
         LineBot = LinePos + (BoxPercent * Me.Section(0).Height)
         bolWhite = Not (bolWhite)
         If bolWhite Then
             Me.Line (0, LinePos)-(Me.Width, LinePos)
         Else
             Me.Line (0, LinePos)-(Me.Width, LineBot), colGrey, BF
         End If
    Next LinePos
End Sub

The only problem is that rather than a continuous set of rows, there is a solitary black line that separates the report data taken from the query from the blank lines added by the VBA. At first I didn't see this as a problem, but after I set some field properties to "can grow", I found that the black line goes through the text of the lowermost field....not optimal. I don't understand this code well enough to see where I might tweak it. Does anyone else know how to get rid of that line?

As always, thanks for your help.

[Dal Jeanis]

Hi again!

I note two possibilities in that code, moving the first line, or futzing with the first line's properties.

1A) in Detail_Format, try changing the 0.1 to a larger number, and see if the line moves down farther. (It should)
or
1B) in Report_Page, after you set colGrey, add a line like LinePos = LinePos + 0.1 * Me.Section(0).Height, and see if the line moves down farther. (It should.)
or
2) in Report_Page, try starting off with bolWhite = True, and see if that helps in any way. (It will cause the first box to be grey. Might help, but probably not)

[June7]

I am trying your code and I am not getting lines at all with the Detail_Format event.

This code worked on first try http://office.microsoft.com/en-us/ac...001219146.aspx
However, won't carry the shading to end of page if records don't fill page and this can be accomplished with report Alternate Back Color property setting.

I would like to understand how you accomplish this. Do you want to provide db for analysis? Follow instructions at bottom of my post.

[Jaron]

Thanks for responding. Here's what happens:
1A: This works well, but is it a constant, or does it change with the field that grows? Further experimentation to come...
1B: I can get the line to move down further incrementally with this method by tweaking the "0.1". As with 1A, what I'm afraid of is since the lowest field will grow to a different size each time depending on the data, will the line position change as needed, or is it a constant? I'll do some further experimenting with this.
2: This method looks nice, as it gets rid of the black line, but the first grey row obscures the lower half of the field that has the grow property.

I'll report back if my experimentation produces any concrete answers. Thanks again!

Hi again!

I note two possibilities in that code, moving the first line, or futzing with the first line's properties.

1A) in Detail_Format, try changing the 0.1 to a larger number, and see if the line moves down farther. (It should)
or
1B) in Report_Page, after you set colGrey, add a line like LinePos = LinePos + 0.1 * Me.Section(0).Height, and see if the line moves down farther. (It should.)
or
2) in Report_Page, try starting off with bolWhite = True, and see if that helps in any way. (It will cause the first box to be grey. Might help, but probably not)

[Jaron]

DIRECCIONES_1.3.zip

I didn't see June7's response until late, but here is a stripped-down version of the DB. The code only works in the print preview of the report, although I'm sure you gathered that already. Thanks for taking a look.

[June7]

Right now the code uses a calculation that depends on a constant textbox height. Allowing the textbox to grow makes this value dynamic. The problem is how to determine how much the textbox has grown for the last record, if at all, or how much the end of Detail section has been pushed. Even though the textbox and Detail section 'grow', value of Height property does not change.

However, although not really sure why, following seems to work:

LinePos = Me.Top + Me.Section(0).Height - Me.NOTAS.Height
(reference to any textbox on the record row in Detail section will serve)

Also, change bolWhite to start out True. (Note: Boolean variable default value is False)

Sidenote: if not already aware, the 1440 used in the code is twips. 1440 twips per inch. VBA converts most (Left property is an exception) dimension properties into twips. Example: the textbox Height property set at 0.1667 inches = 240 twips. The calcs for placement of the shaded lines are in twips.

Now to figure out how to get this to work in my db.

[Jaron]

June7,
Looks promising, and I think I understand that what we're doing is telling it to choose the position of the first line based on the top of the page plus the section height, but minus the detail height. That should work, and it sounds like you got it to work. What's happening for me is the grey rows are now overlapping the grey rows of the detail section all the way to the top of the page and partly into the header, and offset enough to cover over most of what is in the white rows of the detail section. I haven't been able to find some combination of dimensions so far that works. Perhaps I did not implement your suggestion correctly? Here is what I have:

Private Sub Report_Page()
Dim bolWhite As Boolean
Dim LineBot As Long
Dim colGrey As Long
Const BoxPercent = 0.95
    
    bolWhite = True
    colGrey = RGB(240, 240, 240)
       LinePos = Me.Top + Me.Section(0).Height - Me.NOTAS.Height

    For LinePos = LinePos To 7.5 * 1440 _
                                        Step Me.Section(0).Height
         LineBot = LinePos + (BoxPercent * Me.Section(0).Height)
         bolWhite = Not (bolWhite)
         If bolWhite Then
             Me.Line (0, LinePos)-(Me.Width, LinePos)
         Else
             Me.Line (0, LinePos)-(Me.Width, LineBot), colGrey, BF
         End If
    Next LinePos
End Sub

Thank you

[Jaron]

Ok, disregard the previous post. the overlapping problem has gone away with the following adjustment, however the bottom of the 'grown' field is still obscured:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
      LinePos = Me.Top + Me.Section(0).Height - Me.NOTAS.Height
End Sub
Private Sub Report_Page()
Dim bolWhite As Boolean
Dim LineBot As Long
Dim colGrey As Long
Const BoxPercent = 0.95
    
    bolWhite = True
    colGrey = RGB(240, 240, 240)

    For LinePos = LinePos To 7.5 * 1440 _
                                        Step Me.Section(0).Height
         LineBot = LinePos + (BoxPercent * Me.Section(0).Height)
         bolWhite = Not (bolWhite)
         If bolWhite Then
             Me.Line (0, LinePos)-(Me.Width, LinePos)
         Else
             Me.Line (0, LinePos)-(Me.Width, LineBot), colGrey, BF
         End If
    Next LinePos
End Sub

[Jaron]

And for a minute I thought I had it with this one, but the variable quality went away:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
      LinePos = Me.Top - Me.NOTAS.Height + ReportHeader.Height
    
End Sub
Private Sub Report_Page()
Dim bolWhite As Boolean
Dim LineBot As Long
Dim colGrey As Long
Const BoxPercent = 0.95
    
    bolWhite = True
    colGrey = RGB(240, 240, 240)

    For LinePos = LinePos To 7.5 * 1440 _
                                        Step Me.Section(0).Height
         LineBot = LinePos + (BoxPercent * Me.Section(0).Height)
         bolWhite = Not (bolWhite)
         If bolWhite Then
             Me.Line (0, LinePos)-(Me.Width, LinePos)
         Else
             Me.Line (0, LinePos)-(Me.Width, LineBot), colGrey, BF
         End If
    Next LinePos
End Sub

[June7]

Don't know what to say. The code from post 8 works for me in your db under Access 2007 and 2010.

However, discovered another issue. I removed filter from query so all 6 records display. Whereas an odd number of records displayed before, this is an even number of records. Now I see the last record is a grey line and a grey line follows that record so there is a doubling of the grey line. This seems to handle that but the black line is back:

bolWhite = IIf(Me.Count Mod 2 = 0, False, True)

This gets rid of black line:

         If bolWhite Then
             Me.Line (0, LinePos)-(Me.Width, LinePos), colGrey
         Else
             Me.Line (0, LinePos)-(Me.Width, LineBot), colGrey, BF
         End If

Not seeing the field obscured.

[Jaron]

Ok, well I appreciate your help. I'll keep messing with it. I'm sure the solution is right around the corner.

[June7]

I may have edited my previous post while you were posting. Has more info.

Did another test. Set the report to sort by ID field (had to add field to the query). Now the record that 'grows' is first and the thin line shows up again.

[Jaron]

Oops, I didn't see the rest of your post. With the changes you describe, I've got my grow property back. Seems to work with both odd and even number of records, as you say. Still a slight sliver of a line of whatever the alternating color is at the detail section end, but I bet some tweaking with the dimensions will make that go away. Even if not, it's so minor that I can ignore it. Thanks again for your help. For your info, here's what I've ended up with:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
      LinePos = Me.Top + Me.Section(0).Height - Me.NOTAS.Height
End Sub
Private Sub Report_Page()
Dim bolWhite As Boolean
Dim LineBot As Long
Dim colGrey As Long
Const BoxPercent = 0.95
    
    bolWhite = IIf(Me.Count Mod 2 = 0, False, True)
    colGrey = RGB(240, 240, 240)
    For LinePos = LinePos To 7.5 * 1440 _
                                        Step Me.Section(0).Height
         LineBot = LinePos + (BoxPercent * Me.Section(0).Height)
         bolWhite = Not (bolWhite)
          If bolWhite Then
             Me.Line (0, LinePos)-(Me.Width, LinePos), colGrey
         Else
             Me.Line (0, LinePos)-(Me.Width, LineBot), colGrey, BF
         End If
    Next LinePos
End Sub

[June7]

Oops, looks like I posted exactly same time as you. See previous for the best I can do. I give up.

[Jaron]

Oops, looks like I posted exactly same time as you. See previous for the best I can do. I give up.

Well, at least the line is not black, so it is not extremely noticeable. Thank you very much for your help, I'm farther ahead than I ever would have been on my own.