Determining Detail Section Height for Line Method 3

[Haccess]

Hi. Today I discovered the Line method for report sections. Seems very powerful--I'm hoping I never have to physically draw another line in report Design View again.

However, I'm coming up short figuring out how to determine the height of the current report section so I can code the Y2 (ending vertical coordinate) parameter of vertical lines that need to grow with the section.

I've found a few posts that simply hard code a value in twips larger than the tallest anticipated section, but that seems pretty hokey to me.

What's the secret?

 

[dhookom]

You would generally have a control or controls that would cause the section to expand. Find the maximum value of Ctrl.Top+Ctrl.Height in the On Print event. Use this maximum value to get the grown height of the detail section.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheAceMan1]

How are ya Haccess . . .

Just be aware when you query widths & heights of sections & controls the [blue]unit of measure returned[/blue] will be in [blue]twips[/blue], so you'll need to convert:

[blue]twips = 1440 * inches
inches = twips / 1440[/blue]

or

[blue]twips = 1440 * cm / 2.54
cm = 2.54 * twips/1440[/blue]

See Ya! . . . . . .

 

[zevw]

Hi!

Can you elaborate a little more on this.

I have a report with 3 subreports where the can grow option is set. I have lines within the report that are the height of 1 line (0.125"). I would like to have the lines grow along with the report based on the height of the subreports.

Basically I guess there are 2 issues:

1. What is the code to set the line to the maximum height of this record (Detail section).
2. How will I figure out which subreport expanded the most.

I am sorry I'm little behind with this, but your help would be greatly appreciated.

 

[dhookom]

There is a calendar report at

http://www.access.hookom.net

in the samples which has code to loop through 5 subreports to determine which is the tallest. It then draws 5 rectangles matching the height of the tallest.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Duane!

Thanks so much for pointing me to your sample database.

I modified your code to fit what I am trying (not to get a modified rectangle / box, but rather a longer line) to do and its not changing the height.

I tried 3 methods based on your code:

Method 1 - Run-time Error '2191' - You can't change height property in print preview or after printing has started!

Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
    
    Dim lngMaxHeight As Long
    Dim i As Integer
    lngMaxHeight = Me.Line1.Height
    For i = 1 To 3
        If lngMaxHeight < Me.Line1.Height + Me("rptPREE" & i).Height Then
            lngMaxHeight = Me.Line1.Height + Me("rptPREE" & i).Height
        End If
    Next
    Me.Line1.Height = lngMaxHeight
    
End Sub

So I placed it in Format rather then in Print

Method 2 - Run-time Error '2100' - The control or subform ciontrol is to large for this location

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
    
    Dim lngMaxHeight As Long
    Dim i As Integer
    lngMaxHeight = Me.Line1.Height
    For i = 1 To 3
        If lngMaxHeight < Me.Line1.Height + Me("rptPREE" & i).Height Then
            lngMaxHeight = Me.Line1.Height + Me("rptPREE" & i).Height
        End If
    Next
    Me.Line1.Height = lngMaxHeight

End Sub

So I decided to remove "Me.Line1.Height + Me("rptPREE" & i).Height" and just write Me("rptPREE" & i).Height

Method 3 - Height does not change!

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
    Dim lngMaxHeight As Long
    Dim i As Integer
    
    lngMaxHeight = Me.Line1.Height
    For i = 1 To 3
        If lngMaxHeight < Me("rptPREE" & i).Height Then
            lngMaxHeight = Me("rptPREE" & i).Height
        End If
    Next
    Me.Line1.Height = lngMaxHeight

End Sub

I would really appreciate if you can help me with this.

Thanks

 

[dhookom]

You can't use code to change the height of the line. The grown height of your subreports can't be determined until the On Print event. At that point, it is too late to set the height of your line control. That is why the code in the sample uses the Line method to dynamically draw on the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Thanks so much for your response.

Okay!

Now what I understand is that it will draw the box as many times as I want, but they will always have the same width and height. I would like it to draw a line (box) between each control without a width. Is that possible?

 

[dhookom]

You can set the width of the drawn box/line by changing the second X value in the Line method. I sometimes use the "Step" argument

  'draw a vertical line 1" from the left margin, 
  ' 1/2" down in the section, and 1" long
  Me.Line(1440,720)-Step(0,1440)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Duane!

Sorry, I was not concentrating, your code works super.

I modified for my needs but works great!!!

Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
    
    Dim lngMaxHeight As Long
    Dim i As Integer
    lngMaxHeight = Me.Itm0.Height
    For i = 1 To 3
        If lngMaxHeight < Me.Itm0.Height + Me("rptPREE" & i).Height Then
            lngMaxHeight = Me.Itm0.Height + Me("rptPREE" & i).Height
        End If
    Next
    Me.Line (Me.Width, 0)-(Me.Width, lngMaxHeight)
    For i = 0 To 24
        Me.Line (Me("Itm" & i).Left, 0)-(Me("Itm" & i).Left, lngMaxHeight)
    Next
    
End Sub

A litle issue that arose and maybe you can help me with this

I changed the border width from "Hairline" to "1 pt"

Now it extends all the boxes to as much data there is in the subreports. The only problem is the added box height is hairline border width, and the first line has the 1 pt width. What can I do about this issue.

 

[dhookom]

If I understand correctly, you can set the DrawWidth in the report to a higher value. I'm not sure why you are mixing line/border controls with the drawn lines.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Well, your right!

I am making something simple complicated.

What is the value of 1 pt when I use

Me.DrawWidth = x

I apologize for being so confused, It is the first time in many years that I am customizing reports with code. and I did not understand some of these properties.

Thanks for all your patience.

William

 

[zevw]

I found it

1 pt = 12
2 pt = 24
3 pt = 36
4 pt = 48


Thanks again

If got you I have a different question.

I was asked to create a Earnings report per employee, and if the page has records up to 3/4 of the page, they want the totals to be on the bottom of the page. They would like me to fill the rest of the page with a continuation of the lines between control in the empty space. Is this possible in Access

 

[dhookom]

I would first try to create a report based on only the employee and totals information. Then use a subreport in the detail section of the main report to display the earnings or whatever. You can use the line method in the main report to draw as many lines as you need to fit the main report section. Try set the subreport back to transparent.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Do you mean that my detail section should be the height of the page and basically the supreport will grow according to how many records it has?

What would happen if the results in my subreport will have more then a page of records, I would not have the employee iinformation on the header of the next page.

 

[dhookom]

I don't know how this will all work since it's your reports and not mine ;-). You would need to experiment with this. Your first question seems that you have interpreted my possible solution correctly.

You can set the Repeat Section property of section to Yes to have them appear on multiple pages if required.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Going back to the line property.

I would like to create a box with the height of the supreport which in know how to get. but also to have the box width of the whole detail section which in my case is 10.5 inches (Landscape format).

Secondly based on a counter I would like to shade the box grey where the counter is a odd number.

   If Me.Cnt Mod 2 = 0 Then
       Me.ShdBox.BackColor = 11382189
   Else
       Me.ShdBox.BackColor = 16777215
   End If

How would I write this on the Line property?

 

[dhookom]

You can set the fill color of a drawn rectangle (Line method). You seem to have all the knowledge other than the drawn color of the rectangle. I'm not sure of the exact syntax and would be a little fearful that any color would obscure objects within the box.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Well that is why I wanted to send the box to the back so that it does not cover any objects.

Actually I am not fully clear in the sytax of where the Line width is stated.

Me.Line (Me("Itm" & i).Left, 0)-(Me("Itm" & i).Left, lngMaxHeight)

in which part would I state the 10.5 inches?

 

[dhookom]

The code might look like

Me.Line (Me("Itm" & i).Left, 0)-Step(10.5*1440, lngMaxHeight)

I don't think there is anything you can do about moving this drawn rectangle to the front or back. It isn't a control.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]