How can I hide an attachment (image) object on a report if there are zero attachments?

Question

I am building a report for an MS Access database. The report contains a subform that lists information about items, including an attachment field that holds images.

If there is no attachment, the row space is still used by the object, resulting in a lot of unnecessary empty space in the report.

I need to resize the object to 0x0 and also resize the Details section of the subreport if there is no image.

1. I have found that I could use the following command to determine if there is an image present but do not know which Event to put the statement in or what additional syntax will be required:

   Me.attachPhoto.AttachmentCount
   

2. I found the following example code for resizing the attachment/image object that I think I could modify to get working, but do not know which Event to place it in:

   Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
       If IsNull(Me!Image20) Then
           Me!Image20.Visible = False
           Me!Image20.Height = 0
           Me!Image20.Width = 0
       ElseIf Not IsNull(Me!Image20) Then
           Me!Image20.Visible = True
           Me!Image20.Height = 2880
           Me!Image20.Width = 2880
       End If
   End Sub 
   

3. Assuming I can figure out where to place the code and can get it working, I still don't know how to change the size of the Details section of the subreport.

Answer 1

A few things:

First up, you don’t need code to do this.

The problem of course is that picture control does not have a built can grow, and can shrink setting.

However, most controls do, and especially a sub-report.

In fact, I recommend the following trick not only for pictures, but often you have a “group” of controls to shrink if they don’t have values. The problem is while “one” box can be collapsed very easy by the report rendering system, several controls on a line often can’t, because with multiple controls, then Access can’t figure out how to move up multiple controls.

Worse, picture controls can’t shrink. But text boxes, AND ALSO sub-reports do shrink rather nice.

Given the above?

Move the offering "items" or group or picture into a sub-report.

Try this approach: It takes no code. It is an easy and FAST drag + drop approach.

Simply create blank new report. Base the report on the same table as existing report.

Launch the query builder on this report – yes to create a query.

Then in the query builder do this:

Now, this sub report will ONLY return records if there is a picture.

Cut the picture control from your main report (in design mode). Paste it into this report. Now you have a report with JUST the picture control. And ONLY records with a picture will appear (because of above condition).

Now, save this sub report – CLOSE it.

Now, with your main report in design mode, simply drag + drop in the sub report from the nav pane.

The result will should look like this:

Access should (will) set the master link child as the PK by default (this is fine). (just in case - do check the link master child settings)

Make sure the sub report on main is can grow/can shrink. Size it to ONE line like above.

So a few simple mouse clicks, and some drag + drop, and you off to the races.

Edit

I also missed that on the main report - you want the keep together for the main detail section set = no. You also need to use print preview - this does not seem to work with report view.