Can I adjust the VISIBLE and properti of ole control for each record on a subreport?

[stu]

Hi,



I have a report with a subreport. The records for the subreport may or may not include a photo embedded in an ole control. The number of subreport records per main report varies. I am trying to write code to assess on each record if a picture is present and reduce the height of the control to 0 and change the VISIBLE property to false if these's no photo. So on the same report, I want to end up with some subreport records with the ole control visible and some without depending on the presence of a photo in that particular sub-record. The reason for this is to remove the empty space left by a non-present photo.

I have tried the following in the subreport without any joy -

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Check if photos are present and hide if not
If (Not IsNull(oleItemPhotos1)) = True Then
    Me.oleItemPhotos1.Visible = True
    Me.oleItemPhotos1.Height = 5330
Else
    Me.oleItemPhotos1.Visible = False
    Me.oleItemPhotos1.Height = 0
End If

End Sub

Any help will be much appreciated.

Stu

[June7]

Why doesn't it work, what happens - error message, wrong results, nothing? Have you step debugged?

[stu]

Why doesn't it work, what happens - error message, wrong results, nothing? Have you step debugged?

Hi June7,

Thanks for replying. The code does not throw up any error messages or the like, it does nothing. The ole control remains full size and visible regardless of the presence of a photo.

Stu

[June7]

These are embedded images in an Attachment type field? I tested with Attachment field and Image Control and the code works but only with PrintPreview. Format event executes only for PrintPreview. However, the Detail Section height is unaffected. I've never used the OLE Object Control for dynamic image display.

[stu]

Thanks again June7.

May I ask what control you would use for dynamic image display?

Thanks,

Stu

[June7]

Access 2007 added ControlSource property to the Image Control so to offer dynamic display of images.

[stu]

I have this code working in the onload event of a main report, so maybe the fact that this is an image control is not too relevant. The question is how to adjust the height and visibility of a control on a sub-report to allow that control to vary with each record on the subreport (or maybe where to put this code to achieve that result).

Many thanks again.

[June7]

Seems to me you have the code in the correct event - Detail_Format. That is the event I tested. But I did not build subreport and I used the Image control. Do you want to provide db for analysis?

[stu]

Hi again,

Unfortunately I can't post the db for commercial reasons. I have looked at the report in "print preview" and exported as a PDF and the code does the job in those situations. I just need to find out where to put the code to be operational when viewing the report on screen. I have tried onload and oncurrent of the report that is used as the sub-report, but again no luck. Any ideas are very welcome.

Stu

[June7]

Code won't work in any other event and only for PrintPreview. That is the nature of reports. A form is different.

[stu]

So would you recommend using a read only form rather than a report to display this type of document?

[June7]

For on-screen viewing of data, yes, use form.

You can probably use the same form used for data entry, just open it with read only parameter:

DoCmd.OpenForm "form name", , , , acFormReadOnly

[stu]

Sorry to come back to this one. I've decided to stick with the report rather than form for now as there are some drawbacks to using the can grow/shrink property on forms' text / memo controls when in form view.

The problem I now have with the report in print preview mode is that the subreport details sections do not autoheight (even though this property is set to yes). So if the image control is removed with the above code the space remains at the bottom of that subreport details section. Do i need code to make this adjustment too? I have tried -

Me.Detail.AutoHeight = True

in the on format event with the above code, but this does nothing.

Any suggestions?

Thanks, Stu

[June7]

The image control isn't removed, just not visible. I don't know why the subreport Detail is not shrinking. I don't have anything set up to test this. If you can't provide db for analysis following the instructions at bottom of my post, I can't help.