Solved

Access report

Posted on 2014-10-11
19
415 Views
Last Modified: 2014-10-13
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
0
Comment
Question by:clock1
19 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40374788
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
0
 

Author Comment

by:clock1
ID: 40374793
0
 

Author Comment

by:clock1
ID: 40374794
Believe I have attached PDF now.  Thanks.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40374844
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
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40375137
Why do you need to adjust the section's height?

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

Author Comment

by:clock1
ID: 40375174
hnasr because a text box needs to be included under the image and the section needs to be high enough to accommodate each.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40375189
clock1,
Thanks for the clarification.
Can you reproduce the issue with a simple report using 2 dummy images?
0
 

Author Comment

by:clock1
ID: 40376122
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
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40377236
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:clock1
ID: 40377264
Still trying from this end too.  Thanks
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40377514
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
0
 

Author Comment

by:clock1
ID: 40377760
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40377828
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.
0
 

Author Comment

by:clock1
ID: 40377969
Without question, will do as you suggest.  Request only that you comment last code for my benefit.  Thanks
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40378353
Here you are: Hope that helps and good luck.
code as image
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

0
 

Author Comment

by:clock1
ID: 40378397
Thank you for hanging in there with me - the points are yours.
0
 

Author Comment

by:clock1
ID: 40378529
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
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 40378530
Misunderstanding issue in close of question.

Author commented in http:#a40378397
"clock12014-10-14 at 00:48:39ID: 40378397
Thank you for hanging in there with me - the points are yours."

This contradicts with the mentioned reason.
0
 

Author Closing Comment

by:clock1
ID: 40378660
The points should have gone to solution.   Second attempt.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now