asked on

Access report

Using an event procedure, How can I adjust the height of this report's detail section at the time that the detail section is formatted based on the height of the image that it contains?

See attached PDF. Image ID#9647 is taller than Image ID#9645.

The Image Frame control named "Image34" is in the Detail section of this report.
Image34 has following attributes: Control Source = Location, Picture Type = Embedded, and Size Mode = Zoom.

Here is the Detail On Format event procedure:
Private Sub Detail Format(Cancel As Integer, FormatCount As Integer)
If Len(Me.location.Value) > 0 Then
Me.Detail.Height = 2800
Me.Image34.Height = 2700
Me.Image34.Width = 2700
Me.T1.Top = 2750
Me.T1.TextAlign = 2
Me.T1.FontBold = False
Me.T1.ForeColor = RGB(255, 255, 255)
Me.T1.BackColor = RGB(192, 192, 192)
Else
Me.Detail.Height = 0
Me.Image34.Height = 0
Me.Image34.Width = 0
Me.T1.Top = 0
Me.T1.TextAlign = 1
Me.T1.FontBold = False
Me.T1.ForeColor = RGB(0, 0, 0)
Me.T1.BackColor = RGB(255, 255, 255)
End If
Me.Detail.Height = 0
End Sub

IrogSinta

You forgot your attachment. Looking at your code above, I'm not sure why you have Me.Detail.Height = 0 at the end of your procedure. You should remove that. Secondly, in your ELSE section, you need to set the Image height to 0 first before you set the Detail height to 0. Otherwise, the detail section will only resize to right under the lowest control you have in the detail section.

Ron

clock1

ASKER

Report.pdf

clock1

ASKER

Believe I have attached PDF now. Thanks.

Dale Fye

I did the following in the Detail sections Format event to resize an image, reposition the textbox accordingly, and then set the detail sections height

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

Me.Image0.Height = Val(Me.Text1) * 200
Me.Image0.Width = Val(Me.Text1) * 200
Me.Text1.Top = Me.Image0.Top + Me.Image0.Height + 50
Me.Detail.Height = Me.Text1.Top + Me.Text1.Height + 50

End Sub

Hamed Nasr

Why do you need to adjust the section's height?

Just increase/decrease the image control size, and the report expands or collapses accordingly.

clock1

ASKER

hnasr because a text box needs to be included under the image and the section needs to be high enough to accommodate each.

Hamed Nasr

clock1,
Thanks for the clarification.
Can you reproduce the issue with a simple report using 2 dummy images?

clock1

ASKER

Here is the entire challenge:
Attached are (a) an accdb file containing the table & the report, and (b) 2 jpeg images.
In order for this to work as is, you must place the images in C:\<root>.
Run report.
On Format, the image height sizes to 2700 twips according to code.
Note image #9647 is taller than #9645.
Objective is to size the image frame to the actual ImageHeight.
That way, shorter images don't inherit all the blank space caused by setting a stationary height in code.
Tried using ImageHeight in the code, but Access formats the entire page at once, and there are challenges if there are multiple images on the same page.
Example.accdb
Image1.jpg
Image2.jpg

Hamed Nasr

Downloaded the database sample. I spent quite a good time investigating the issue. It looks like an issue with Access.

I agree with you saying "and there are challenges if there are multiple images on the same page. ".
It looks interesting, and therefor It deserves another try. Hold on for a while.

clock1

ASKER

Still trying from this end too. Thanks

Hamed Nasr

Here you are. Try this.
Modified you code in the detail_format section , modification ends with Exit Sub. Added Public p As Integer.

Public p As Integer
Private Sub Detailbereich_Format(Cancel As Integer, FormatCount As Integer)
pp:
    If Not IsNull(Me.location.Value) Then
        Me.Image34.Picture = Me.location.Value
        Me.Image34.Height = Me.Image34.ImageHeight / 1.5
        Me.Image34.Width = Me.Image34.ImageWidth / 1.5
        Me.Image34.Top = Me.T1.Top + Me.T1.Height
    Else
        Me.Detailbereich.Height = 0
        Me.Image34.Height = 0
    End If
    If p = 0 Then
        p = 1
        Me.Detailbereich.Height = 0
        GoTo pp
    Else
        p = 0
    End If
Exit Sub

Open in new window

Example--2.accdb

clock1

ASKER

Almost there. Can see the change right away. Think I need to tweek just a bit.
* Request that you please comment your code, so that I can follow along better with what's occurring.

I did this: pasted this code into my app.
Because this report is a sub report within the detail section of a multi-column report, using 1.5 exceeds the width of a single column, so I modified to 4.5.
Now seeing errors that several jpeg images are too large. Didn't see that before probably because code is examining actual image height.
Also noticed that Me.T1 now appears above Me.Image34, originally Me.T1 appeared below Me.Image34.
Modified code Me.T1.Top = Me.Image34.Top + Me.Image34.Height, but this then is inherited by subsequent Me.T1 text.

Hamed Nasr

For the Me.T1 to appear below Me.Image34, modification in lines 9, 10, and 12:

Option Compare Database
Public p As Integer
Private Sub Detailbereich_Format(Cancel As Integer, FormatCount As Integer)
pp:
    If Not IsNull(Me.location.Value) Then
        Me.Image34.Picture = Me.location.Value
        Me.Image34.Height = Me.Image34.ImageHeight / 1.5
        Me.Image34.Width = Me.Image34.ImageWidth / 1.5
       Me.T1.Top = Me.Image34.Top + Me.Image34.Height
        'Me.Image34.Top = Me.T1.Top + Me.T1.Height
    Else
        Me.T1.Top = 0
        Me.Detailbereich.Height = 0
        Me.Image34.Height = 0
    End If
    If p = 0 Then
        p = 1
        Me.Detailbereich.Height = 0
        GoTo pp
    Else
        p = 0
    End If
Exit Sub

Open in new window

For the issue with Sub report, you need to start a new question. Include in the new question, a copy of the database that demonstrates the issue.

clock1

ASKER

Without question, will do as you suggest. Request only that you comment last code for my benefit. Thanks

Hamed Nasr

Here you are: Hope that helps and good luck.

Option Compare Database
Public p As Integer
' I found that with iamges, the detail section height inherits the previous height
' only for one record, so I did a 2 pass check.
Private Sub Detailbereich_Format(Cancel As Integer, FormatCount As Integer)
pp: ' this is a label to restart the code
    If Not IsNull(Me.location.Value) Then ' the location contains a link to image on the disk
        Me.Image34.Picture = Me.location.Value 'sets the Picture for the Image34 control.
        Me.Image34.Height = Me.Image34.ImageHeight / 1.5 'reduces the image height to a fraction of the original image height
        Me.Image34.Width = Me.Image34.ImageWidth / 1.5 'reduces the image width to a fraction of the original image width
        Me.T1.Top = Me.Image34.Top + Me.Image34.Height ' sets the top of T1 to the bottom of Image34
        'Me.Image34.Top = Me.T1.Top + Me.T1.Height ' sets the top of Image34 to the bottom of T1
    Else ' no url for image
        Me.T1.Top = 0 'sets top of T1 to 0
        Me.Detailbereich.Height = 0 'sets detal height to 0 allowing access to set new height
        Me.Image34.Height = 0 'sets Image34 height to 0
    End If
    If p = 0 Then 'check if first pass through code, set Detail height to 0, and set flag p=1 and go to label pp
        p = 1
        Me.Detailbereich.Height = 0
        GoTo pp
    Else 'if second pass, set p to 0 for next record
        p = 0
    End If
Exit Sub ' to exit the code, you may replace with End Sub, if removing other extra code.

Open in new window

clock1

ASKER

Thank you for hanging in there with me - the points are yours.

clock1

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for clock1's comment #a40378397

for the following reason:

Workable solution

ASKER CERTIFIED SOLUTION

Hamed Nasr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clock1

ASKER

The points should have gone to solution. Second attempt.