INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...It is good to know that there are groups such as this willing to share knowledge in this money driven economy..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Reports Forum

Resize controls based on largest text box

CODE

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
     Me.imgJobStep.Height = 1470
     Me.txtHazardAvoidance.Height = 1570
     Me.txtHazards.Height = 1570
     Me.txtJobStep.Height = 1570
     Me.imgJobStep.Top = Me.txtHazardAvoidance.Top + 50
End Sub

Thanks,
SoggyCashew.....

dhookom (Programmer)

28 Feb 12 18:21

You can't grab the height of "Can Grow" controls until the On Print event. At that point, it is too late to set the height (or other size) properties of the controls.

What is your bottom line reason for doing this? Are you worried about the borders looking out of alignment? If this is your real issue then you can find the tallest control and use the Line Method to draw lines around controls. This assumes you have removed the borders from the controls.

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

29 Feb 12 4:31

Yes all four controls have borders and yes that would be a very good reason. They all got to line up all the way across. If I removed the borders how can I use this line method your talking about. But I still don't want it to go over 1570 if those controls are no bigger than that height. Thanks!

Thanks,
SoggyCashew.....

oxicottin (Programmer)

29 Feb 12 7:00

Ok, I did a search and found the code below.

CODE

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intMaxHeight As Integer
Dim ctl As Control
'Find highest control in Detail section _
that has a tag property of "Border"
For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
If ctl.Height > intMaxHeight Then
intMaxHeight = ctl.Height
End If
End If
Next
'Draw a box around each control in Detail _
that has a tag property of "Border"
For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
Me.Line (ctl.Left, ctl.Top)- _
Step(ctl.Width, intMaxHeight), vbBlack, B
End If
Next

End Sub

It changes the size to the largest text box but the borders look like the image below.

With code from above...
http://i54.photobucket.com/albums/g94/oxicottin/withcode.jpg

With origional code and the way I need it to look....
http://i54.photobucket.com/albums/g94/oxicottin/withoutcode.jpg

Thanks,
SoggyCashew.....

dhookom (Programmer)

29 Feb 12 11:00

I am fairly sure that code was something I had posted. Make sure your controls are set to transparent.

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

29 Feb 12 11:47

Yes it is yours :) .... the controls borders are set to transparent.

Thanks,
SoggyCashew.....

dhookom (Programmer)

29 Feb 12 14:05

How about the backgrounds? Are they white or transparent?

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

29 Feb 12 14:33

They were white I set to transparent and now the lines show as they should :). Now for the second part of my question. I have a hidden text box in the background called (txtImagePath) that gives the path of the image that is in the visible image box called (imgJobStep) so if there is an image for that record I wanted the image to be .Height = 1570 can I do something like:

CODE

If DCount("[JobStepID]", "tblJobSteps", "[ImagePath]=""" & txtImagePath & """") > 0 Then

or what could I do? Thanks!

Thanks,
SoggyCashew.....

dhookom (Programmer)

29 Feb 12 14:51

You can set the height in the On Format event. I'm not sure why you don't just set and keep the height at 1570.

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

29 Feb 12 15:37

Ok, I think Im not explaining this correctly. In this image;
http://i54.photobucket.com/albums/g94/oxicottin/withoutcode.jpg it shows two images in rows 1 and 2 and the entire row is that size (1570) and outlined "Besides the image". Then in row 3 and 4 there is no image but the row is still (1570). What I wanted was if there is no image for a row using;

CODE

If DCount("[JobStepID]", "tblJobSteps", "[ImagePath]=""" & txtImagePath & """") > 0 Then

Then have that row the size of the largest text field and if there is an image then have it 1570 UNLESS there is a text field that is larger then have it that size and the image still would be 1570. I hpe Im explaing this better. I did however try the On Format event using the code below but what it did was made every row 1570 and outlined the text boxes to the highest one even if it was smaller than the image so there was gaps everywhere. Thanks!

CODE

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If DCount("[JobStepID]", "tblJobSteps", "[ImagePath]=""" & txtImagePath & """") > 0 Then
        Me.imgJobStep.Top = 0
        Me.imgJobStep.Height = 1570
    Else
        Me.imgJobStep.Height = 0
        Me.Detail.Height = 0
    End If

End Sub

Thanks,
SoggyCashew.....

dhookom (Programmer)

29 Feb 12 15:47

I believe you would need to set the detail section height back down to the smaller height after setting the image height. BTW: I can't view your image from a work laptop since the domain is blocked.

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

7 Mar 12 19:53

Duane, im still trying to figure it out and I cant get anything to work. I tried your sugestion and if there is an image then every record is the 1470. Im doing something wrong....

Thanks,
SoggyCashew.....

dhookom (Programmer)

7 Mar 12 20:26

Share your current code.

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

7 Mar 12 22:38

Its the same as above I just dont see why it doesnt work. What I think its doing is the report is continious so if the first record has an image it sets all the records to 1570 but then it outlines all the text boxes to the height of the tallest control for each record. I need it to if the record has an image then it sizes the image to 1570 and the controls outlines to that size as well but if the text is larger than the image it sizes it to the largest control and if there is no image then it just sizes to the largest control. I have a hidden control in the detail section that gives a image path if there is an image and is blank or no is is avalable for that record. Im using the DCount to find that but im not doing something right obviously. This is out of my skill knowledge.

CODE

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
     Dim intMaxHeight As Integer
     Dim ctl As Control

For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
If DCount("[JobStepID]", "tblJobSteps", "[ImagePath]=""" & txtImagePath & """") > 0 Then
If ctl.Height > intMaxHeight Then
intMaxHeight = ctl.Height
  End If
End If
End If
Next
'Draw a box around each control in Detail
'that has a tag property of "Border"
For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
Me.Line (ctl.Left, ctl.Top)- _
Step(ctl.Width, intMaxHeight), vbBlack, B
End If
Next

End Sub

Thanks,
SoggyCashew.....

dhookom (Programmer)

7 Mar 12 22:47

Do you know which lines of code are being run? I would limit the report to two records, one with an image and another without. Then try code like:

CODE

CODE
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If DCount("[JobStepID]", "tblJobSteps", "[ImagePath]=""" & txtImagePath & """") > 0 Then
        Me.imgJobStep.Top = 0
        Me.imgJobStep.Height = 1570
        Debug.Print "Dcount()>0"
    Else
        Me.imgJobStep.Height = 0
        Me.Detail.Height = 0
        Debug.Print "Dcount()=0"
    End If
End Sub

See what happens.
Are there really no other controls in the detail section so you can set the height to 0? How about try setting the height to something other than 0?

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

7 Mar 12 23:09

Duane, Would it be alright if I upload the DB to my dropbox or somewhere perfered so you could have a look at it? I would have to cut it down due to its size so it might take a day or so.

Thanks,
Chad

Thanks,
SoggyCashew.....

oxicottin (Programmer)

8 Mar 12 0:42

duane, by adding the code you posted (Debug.Print "Dcount()>0") it now does what I was looking for BUT between/below SOME records there is a space and it could be large or small. I can post an image but not sure what your work allows. here is the full code thats in my report.

CODE

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If DCount("[JobStepID]", "tblJobSteps", "[ImagePath]=""" & txtImagePath & """") > 0 Then
        Me.imgJobStep.Top = 0
        Me.imgJobStep.Height = 1570
        Debug.Print "Dcount()>0"
    Else
        Me.imgJobStep.Height = 0
        Me.Detail.Height = 0
        Debug.Print "Dcount()=0"
    End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intMaxHeight As Integer
     Dim ctl As Control

For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
If ctl.Height > intMaxHeight Then
intMaxHeight = ctl.Height
  End If
End If
Next
'Draw a box around each control in Detail
'that has a tag property of "Border"
For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
Me.Line (ctl.Left, ctl.Top)- _
Step(ctl.Width, intMaxHeight), vbBlack, B
End If
Next
End Sub

Thanks,
SoggyCashew.....

dhookom (Programmer)

8 Mar 12 11:36

I would color the backgrounds of every section and control to determine where the space is coming from.

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

8 Mar 12 17:14

Ok Duane, I changed the background color of the controls and they were correct (Image Gaps_1) and then I changed the background color of the detail section
to red and its alternate background color to yellow and I got what in (Image Gaps_3). And in image 2 its the origional no color/transparent.

1) http://i54.photobucket.com/albums/g94/oxicottin/Gaps_1.jpg

2) http://i54.photobucket.com/albums/g94/oxicottin/Gap_2.jpg

3) http://i54.photobucket.com/albums/g94/oxicottin/Gaps_3.jpg

Thanks,
SoggyCashew.....

oxicottin (Programmer)

8 Mar 12 20:27

Ok, I found something interesting. the more lines of text I have the bigger the gap is so if the text fits on the first row then I dont have a gap but if It runs onto the second row then there is a small gap and then if it run onto the third row then the gap is larger. Am I missing a setting?

Thanks,
SoggyCashew.....

dhookom (Programmer)

9 Mar 12 1:09

I'm not sure what is happening. I would set a bunch of debug.print statements and set some breakpoints.

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

13 Mar 12 13:19

Duane, I tried lebans printlinesVer19.mdb example and I basically get the same thing. I think it has something to do with the SubReports Detail_Format VBA. If I remove that everything works but my image is super tiny then if I add the code then my image is the size needed but then I have spaces. I figured out where to move the text boxes so it didnt cause dbl horizontal lines. Here is what I have, is there any other sugestions for the Detail_Format section? Thanks!

CODE

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If DCount("[JobStepID]", "tblJobSteps", "[ImagePath]=""" & txtImagePath & """") > 0 Then
        Me.imgJobStep.Top = 50
        Me.imgJobStep.Height = 1655
        'Debug.Print "Dcount()>0"
    Else
         Me.imgJobStep.Height = 600
         Me.Detail.Height = 0
         'Debug.Print "Dcount()=0"
    End If
End Sub

CODE

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim CtlDetail As Control
Dim intLineMargin As Integer

' This is the spacing between the right edge of the
' control and the Vertical Seperation Line
intLineMargin = 60

' OK lets draw a vertical line to seperate each field
' for each control in details control
' If your last control on the right does not end on the edge of the section
' you will get a second vertical line. If you need to get around this then you
' can skip drawing this last Vertical Seperation Line in a couple of ways.
' We'll use the control name method Here. Our right most control is named
' TestMemo. IF current control is TestMemo - Do not print Vertical Line

For Each CtlDetail In Me.Section(acDetail).Controls
   If CtlDetail.Visible = True Then 'Added
    With CtlDetail
        'If CtlDetail.Name <> "txtHazardAvoidance" Then
        Me.Line ((.Left + .Width + intLineMargin), 0)-(.Left + .Width + _
intLineMargin, Me.Height)
        'End If
    End With 'Added
    End If
Next

        'While we are here lets draw a box around the Detail section
    With Me
        Me.Line (0, 0)-Step(.Width, .Height), 0, B
    End With

Set CtlDetail = Nothing

End Sub

Thanks,
SoggyCashew.....

dhookom (Programmer)

13 Mar 12 14:28

I did some review but wasn't able to get rid of some of the spacing. This is the code I used. Note, I got rid of the DCount() since it shouldn't be necessary with the txtUnboundImagePath in the report.

CODE

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    'If DCount("[JobStepID]", "tblJobSteps", "[ImagePath]=""" & txtImagePath & """") > 0 Then
    Me.imgJobStep.Height = 1
    Me.Detail.Height = 1
    If Not IsNull(Me.txtUnboundImagePath) Then
        Me.imgJobStep.Top = 0
        Me.imgJobStep.Height = 1655
    Else
        Me.imgJobStep.Height = 1
        Me.Detail.Height = 1
    End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    Dim intMaxHeight As Integer
    Dim ctl As Control

    For Each ctl In Me.Section(0).Controls
        If ctl.Tag = "Border" Then
            If ctl.Height > intMaxHeight Then
                intMaxHeight = ctl.Height
            End If
        End If
    Next
        'Draw a box around each control in Detail
        'that has a tag property of "Border"
    For Each ctl In Me.Section(0).Controls
        If ctl.Tag = "Border" Then
            Me.Line (ctl.Left, ctl.Top)- _
                Step(ctl.Width, intMaxHeight), vbBlack, B
        End If
    Next

End Sub

Duane
Hook'D on Access
MS Access MVP

oxicottin (Programmer)

14 Mar 12 6:02

Duane, all these spaces has to do with the image VBA line;

CODE

Me.imgJobStep.Height = 1655

If you drop the size down the gap gets smaller and smaller. I'll keep messing with it but I dont really know what else to try...... Thanks!

Thanks,
SoggyCashew.....

oxicottin (Programmer)

14 Mar 12 8:19

I finally got it with no gaps..... Here is the code I used..... :) :) If your courious I can send an example. Thanks a million Duane for sticking it out with me!

CODE

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

       Me.Detail.Height = 50
         Me.imgJobStep.Height = 50
          Me.txtJobStep.Height = 50
           Me.txtHazards.Height = 50
            Me.txtHazardAvoidance.Height = 50

    If Not IsNull(Me.txtUnboundImagePath) Then
        Me.imgJobStep.Top = 50
         Me.imgJobStep.Height = 1665
          Me.txtJobStep.Height = 1665
           Me.txtHazards.Height = 1665
            Me.txtHazardAvoidance.Height = 1665
    Else
        Me.Detail.Height = 50
         Me.imgJobStep.Height = 50
          Me.txtJobStep.Height = 50
           Me.txtHazards.Height = 50
            Me.txtHazardAvoidance.Height = 50
    End If

End Sub

CODE

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim CtlDetail As Control
Dim intLineMargin As Integer

' This is the spacing between the right edge of the control and the Vertical Seperation Line
intLineMargin = 60

' OK lets draw a vertical line to seperate each field
' for each control in details control
' If your last control on the right does not end on the edge of the section
' you will get a second vertical line. If you need to get around this then you
' can skip drawing this last Vertical Seperation Line in a couple of ways.
' We'll use the control name method Here. Our right most control is named
' TestMemo. IF current control is TestMemo - Do not print Vertical Line

For Each CtlDetail In Me.Section(acDetail).Controls
   If CtlDetail.Visible = True Then 'Added
    With CtlDetail
        'If CtlDetail.Name <> "txtHazardAvoidance" Then
        Me.Line ((.Left + .Width + intLineMargin), 0)-(.Left + .Width + _
intLineMargin, Me.Height)
        'End If
    End With 'Added
    End If
Next

        'While we are here lets draw a box around the Detail section
    With Me
        Me.Line (0, 0)-Step(.Width, .Height), 0, B
    End With

Set CtlDetail = Nothing

End Sub

Thanks,
SoggyCashew.....

LeoJBG (Programmer)

17 Jun 12 22:20

Hi there I found the code posted by oxicottin on the 29th of Feb to do what I wanted with a few adjustments. My only other problem is how do I get the lines to be 1pt wide like I am getting for the header row that I have set up using the properties window since that row does not need to grow. My settings for that row are:
Border Width:1pt
Border Style:Solid

The lines your code draws are thinner. The difference is only noticeable when I print the report.

dhookom (Programmer)

17 Jun 12 22:57

You can use the DrawWidth. Create a new blank report with a detail
section of about 3" in height. Add this code and then preview the
report:

CODE --> vba

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intY As Integer
For intY = 1 To 10
Me.DrawWidth = intY
Me.Line (0, intY * 50 + 100)-Step(Me.Width, 0)
Next
End Sub

Duane
Hook'D on Access
MS Access MVP

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Reports

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Resize controls based on largest text box

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE --> vba

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!