Displaying an image conditional on an Access Report

[NeoPa]

I have Format and Print events in my 2003 reoprt, but S7 indicates that post 2003 the Format event has been renamed to Paint instead. You may need to look into this in more detail.

Option Compare Database
Option Explicit

Private dblHeight As Double

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
    With Me
        If dblHeight = 0 Then dblHeight = .Image39.Height
        .Image39.Height = IIf(IsNull(.Image39), 0, dblHeight)
    End With
End Sub

The rest of what is required is included in S7's post #10. I would try (test) this approach before resorting to subreports (which may also work, but may be more than you need).

[Corey Smith]

Thanks NeoPa.

We have 2010 here, and it has both Format and Paint. I've been trying through Format thus far.

I'm about to give what you have above a run and report back!

[sierra7]

Hi
My mistake! Format is still there. They moved it! (That's my excuse)

If you come to specifying heights directly they must be in TWIPS. 1 cm = 567 TWIP

I generally specify sizes in cms then let Access do the multiplication

Me.Box0.Height = 3 * 567

S7

[NeoPa]

That's very useful info S7, but shouldn't be used in this scenario (certainly not if my code forms the basis of the solution). The only values used are 0 and the original value it was set to by the designer, which is saved the first time the event procedure is triggered. This removes the burden of determining the height from the code.

[Corey Smith]

I'm not having success getting that code to work either. I'm beginning to think that I have set it up incorrectly, initially. Whatever code I put in the On_Format section, I get the space there for the image whether there's an image or not.

How should I have the image control set up? Should I have it's size set initially to 0x0, or set it to the size that all of the images will be (they will all be the same)?

Also, If I'm trying to see if the actual image field (Image) is empty or not ... would referencing the Image Control alone (Image39) tell me that? It seems like if I'm looking at the Height property of Image39, then I'm just looking at the control it self, rather than the image.

I am so confused on this.

[sierra7]

As it seems that the field from your query is called 'Image', I would test whether 'Image' is null and then set the height of Image39 to zero.

NeoPa's code is elegant and as he say will auto-compensate if you change the size of the image box by stretching it in the graphical user interface.

My head-banger approach would be to set box to the size you require, inspect it's Height in it's Properties, lets say it is 5.301cms then in the On_Format event I would have something like

If IsNull([Image]) Then
   Me.Image39.Height = 5.301 * 567
Else
   Me.Image39.Height = 0
End If

Once you get that working you can enhance the code.

[Corey Smith]

S7 ... that is pretty much exactly how I thought I would tackle this when I was very first looking at it, Using IsNull ... except instead of adjusting the height I was going to just make the section not visible if it was Null.

But when I just reference the Image field as [Image] ... I get the error message of:
"Run-time error '2465':

Microsoft Access can't find the field 'Image' referred to in your expression"

Which is what has me frustrated. It seems like I am not able to reference the field that way. Do I need to do something differently since the query that produces this report links two tables together?

[sierra7]

Hi Corey
I have never stored images in the database as you seem to be doing and therefore do not have direct experience of how Access responds in these circumstances.

If the 'item' was numeric data rather than an image I would be fairly confident this was the right track. Instead I might try

If IsEmpty([Image]]) then ...

but have no idea if this will work.

As you are struggling so hard I will try and setup a test database so I know what I am talking about next time!

S7

[Corey Smith]

It has been a struggle ... really only with this one little thing, though. haha

The good thing is ... they said that if it won't work, then they can just make a supplemental brochure type of thing to put the advertising it ... I was just hoping to come through with this piece for them.

I'm gonna give the IsEmpty a try, though.

[sierra7]

Corey
I don't think anyone has asked but how do you get your images into Access?

I know you can store them as OLE objects but I also understand that 2010 allows you to store them as BLOBS but will find out more.
S7

[Corey Smith]

The more I think about it, and after doing some looking into that table ... they are stored in there as attachments.

Which could be why things are not working as expected.

[sierra7]

Corey,
I have just created a small table PicID(autonumbe r), PicDesc (Text20), PicImage (OLE), added 3 records and an image into record 1 only.

I have then created a form to display all three fields. In the forms on current event;-

Private Sub Form_Current()
If IsNull(Me.PicImage) Then
    MsgBox "Image is blank"
    Me.PicImage.Height = 0
Else
    MsgBox "Image is present"
    Me.PicImage.Height = 5 * 567
End If

This behaves as expected; if no image then "Image is blank" message.

I also tested IsEmpty, and although it did not error it said an image was present for all three records, so that was a waste of time!

S7

[Corey Smith]

Now I'm pretty convinced that I don't have things set up correctly. I have tried to do something very similar to that, and it kept giving me the error about not being able to find the field.

Would it work any differently using the On_Format event, as opposed to the Form_Current one you have used?

Let me look at a few things and I will report back.

[sierra7]

I should not think there would be any difference between On_Current in a Form and On_Format in a Report (but life is full of surprises)

You might make the images control in your report the same name as your field. This should not make any difference if things are working OK, but something seems squiffy.

I'm in the UK at present so off to bed shortly.
S7

[NeoPa]

Corey,

If you can and want to, why not follow the instructions in Attach Database (or other work) and attach a copy of your database for us to look at. I'm hoping converting it to 2003 format won't be a problem, but I'll leave it with you.

PS. The benefit of using the approach where you leave the designer to design the layout is that you don't have to worry about updating the code every time design changes are made. That said, the main problem is the fundamentals and getting the situation to be recognised and handled correctly. I would certainly expect the image control, rather than the field, to be what needs to be looked at and tested in this scenarion, but hands-on is so much easier to work with.

If your database is behaving differently from S7's, then that's a strong indication that there's some other problem somewhere you're simply not aware of. Having sight of your actual project could prove important in that situation.