Make field with Image disappear if empty

[JudyK]

=IIf(IsNull([tbl3MGR1.OrgName]),"",[Image_tbl3MGR1] & [tbl3MGR1.Title])

I have a field that contains an image of each employee. In my report, I would like that image to not show up at all if there is no image in the record.

Preferably, I'd like the image field to be a separate box on my report, but even when the field is empty, my report shows a gap where the box is sitting.

I've applied the IsNull function to all the other sections of my report so when those fields are empty, they do not show up. Works fine. Just can't make the box with the OLE image in it disappear and lessen the space left behind.

I cannot send you a file -- it's montrous in size.

Any help you can provide is greatly appreciated.

Thank you,

Judy

 

[vbaInet]

JudyK,

Instead of trying to hide the control, you can remove those records from the Record Source of your report by setting criteria in a query.

 

[JudyK]

Thank you. I'll try that. I'm not that well versed in Access, but am trying.

Great idea - I appreciate your help. Thanks.

Judy

 

[vbaInet]

Let us know how you get on

 

[JudyK]

Am struggling with this one. I know how to do a simple IsNull, but not sure the right function to use here to make that box disappear if the field is empty.

My Image is field: Image_tbl2DR
Label for it is: OLEBOund202

Any idea how I can get this to work?

Thanks much.

Judy

 

[vbaInet]

What happened to our idea of filtering out the Null records Judy?

 

[JudyK]

Let me try it again.

 

[vbaInet]

That's the best way to do it. Again, if you're struggling with that, let us know

 

[nanscombe]

Hi JudyK,

I have attached an example for you to have a look at.

I have formatted the two reports in the attached database so that the picture is set to one side of the text which is longer than the picture.

In the first report, repEmployees01, the Bound Object Frame has Back Style: Normal and Border Style: Solid. This means that a box still shows up where a photo would go (if there was one).

In the second report, repEmployees02, the Bound Object Frame has Back Style: Transparent and Border Style: Transparent. This means that no box is visible if there is no photo.

I hope this helps.

 

[JudyK]

Nigel -- I can't get this to work. If I could get the image box to disappear and not leave a gap on my report where the box is initially placed, that would be great.

What I want to show is a picture (Image) of the manager on the left, then just to the right of the image, 3 lines of information on that person (name of dept, etc). When there is no image in the box, I want the text to go to the far left margin as if there were no image box at all.

It would be great if I could include all this info one statement, but when I do that, the image shows up as a text icon. Figured if I could do that, and have it force a new line of information, that it would work.

What's happening is that the image box is like a solid place-holder and it leaves a big gap even when there is no image in it. I attached a screen print of what I have so far, but the image box is taking up a lot of space. By putting all the IsNull commands in this, everything disappears nicely except the image box info.

 

[vbaInet]

This is why I keep saying, sort out the data you won't need to worry about the layout. Apply some criteria in the record source to remove all records where the field is null.

 

[Simon_MT]

I would be preference not store images in a database. With 16,000 images, I would break the maximum file size.

For a start on the Section with the Image you need to set:
Can Shrink = Yes
Can Grow = Yes (optional)

In the relevant Section property sheet I have used the Detail>On Format

This is the sub routine 1440 twips = 1 inch so I 'm using 2 inches

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

You will have to work out your measurements.

Simon

 

[vbaInet]

But why slow down your report with code when this can be much more effeciently done in the Record Source?

A report or form that has it's Has Module property set to No runs faster than that set to Yes. This can be done in the query.

 

[JudyK]

I am simply doing to take out the image boxes. My knowledge of Access code is limited -- I get very little time to read and understand it. Will try later tonight when I'm home from work (I support over 200 people here, so my focus on this is just as much of a struggle).

I appreciate the time you took to try and help, but without being able to send you the database, it's just over my head.

No worries, but many thanks.

Thank you,

Judy

 

[vbaInet]

It's simple enough. Is the Record Source property of your report a query?

And I need to clarify something, are you hiding the entire record when that field is null or you are hiding individual fields?

 

[JudyK]

Pretty much -- it's a table I selected (has all the info I need), which was queried.

 

[vbaInet]

Just to clarify, do you want to supress the entire row in the report if OrgNam is null?

 

[JudyK]

Yes.

I had 3 rows plus the Image. Top row was for the name of the Org, 2nd row was for the Org Mgr's name and title, 3rd row was actually separate fields showing that managers phone, mobile#, and location (I needed to line those up so the report looked ok. Wanted the Manager's image to the left of those text field rows, but not all my managers have a picture.

I put in the IsNull so the rows of text commands would disappear, which is working fine. It's just getting the OLE image box to do the same thing.

 

[vbaInet]

To get a better picture, let me see a screenshot of the report in Print Preview and Design View. Then I would like to see the SQL statement.

I don't need to see confidential info (including people's pictures).

 

[JudyK]

I can't zip them small enough. Doc is 468K.