Displaying an image conditional on an Access Report

[Corey Smith]

Hello, I am fairly novice with Access in general ... but do have a decent amount of ability with VBA (mostly through Excel, though).

I'm working on a project that is producing a product catalog through an Access Report, and here is what I am stuck on:

There are advertising banners that they would like to display beneath the main group headers for each product category. We plan on storing these image banners into their own table, and connect to the correct one for the category through there.

The thing is, though, that not all categories will have a banner to display.

I'm assuming a simple check to see if an image exists for the current category would take care of whether the image shows or not ... and adjust the group header's height based on that also.

But which event should I put this code in for it to function correctly?

[NeoPa]

I don't use images much in my work, but wouldn't the most sensible approach be to include the image item in the query by linking the image table in?

Handling the size of the header could be done using code, but I would suggest that is a separate question and should be approached only when the first problem is resolved.

[Corey Smith]

Definitely worth looking into. Let me give that a run.

[NeoPa]

Good attitude. Go for it.

Let us know how you get on with it and we can see about helping if any is still required by then.

[Corey Smith]

Did as you suggested and added the image field to the query. Worked great.

What I need to figure out now is how to get the section that I have used the image in for the report to not be visible if that image field is NULL.

How do I reference the field so that I can us IsNull on it?

[NeoPa]

I'm very pleased that helped.

To answer your follow-up question though, I'd need :

1. The SQL used for the query.
2. The name of the field in there which relates to the image.
3. The control on the report which displays the image.
4. The name of the section in the report that it's on.
5. The name of the report itself.

With that I may be able to help you find a way to react to the presence and absence of the picture.

[Corey Smith]

1. SELECT [Catalog Import].Code
        , [Catalog Import].Description
        , [Catalog Import].[Item No]
        , [Catalog Import].ID
        , [Advertising Images].Image
    
   FROM   [Catalog Import]
          INNER JOIN
          [Advertising Images]
     ON   [Catalog Import].Code = [Advertising Images].[Catalog Group Codes];

2. The field is "Image".
3. I used the Image control from the report design tab. Please let me know if there's a better way to do this.
4. Name of the section is GroupHeader1.
5. Name of the report is "Grocery Section".

Thanks Again!

[NeoPa]

Very well answered. Unfortunately I left question #3 a little unclear. Can you tell me the name of the control? I'm sure the type is fine for what you're doing.

[sierra7]

Hi,
I did not see the question of 'null' image being resolved.
Usually, making the image height in design view minimal, then making its property Can Grow = Yes, takes care of this.
S7

[sierra7]

Hi again,
My apologies. Just checked and now confirm that an image control does not have Can Grow/Can Shrink properties. How unfortunate.

That means back to Plan A and changing the height programmatically. I would change the height of the image control not the section height (that can Grow or Shrink automatically).

I would try it with the On_Paint event first (they seem to have re-named the On_Format event since Access 2007)

S7

[Corey Smith]

NeoPa - Sorry about that. The name of the image control is "Image39".

Sierra - The thing that I dont' know how to do is ... at least can't get to work ... is to check for a null value for the image field it'd be based on.

[Corey Smith]

I'm stumped on how to refer to the field I need to check for null. Am I supposed to create a variable in order to use an IF statement to check for null?

Because when I just refer to it as it's name, I get nothing.

[TheSmileyCoder]

What I would do is to place the image itself in a supreport. Place the supreport into your group header, and link it the same way you have in the query
[Catalog Import].Code = [Advertising Images].[Catalog Group Codes]
but using the controls Master/Child property instead. Since the supreport will be empty (Now I am assuming that if there is no image, there will also be no record in the Advertising Images table), there will be no record in the supreport, and it will automatically not display. If the supreport control is shrinked as far as possible (height wise) and set to CanGrow, access will do the rest for you.

[Corey Smith]

There actually will be a record for every Code. That is why I need to be able to check that field to see if it has an image, or not.

It's this way because the categories that will have an image attached varies from version to version.

[TheSmileyCoder]

Try basing the supreport on a query in which you have a Is Not Null clause on the image field. That should work as well.

[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(autonumber), 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.

[sierra7]

Corey,
I missed that you said the images were stored as ATTACHMENTS. I think our posts crossed.

I'll try that shortly and 'report back'!
S7

[Corey Smith]

I don't have an issue attaching the database for you guys to look at.

But first, over the last night I was thinking that maybe our approach to accessing the images is what is giving the headaches. Right now we have a separate table linked in that has the category code and the images are stored in an attachment field ... which for whatever reason, we cannot figure out how to check whether it is empty or not using VBA code.

Would the better approach be for us to go the route where we store the paths to where the images are in a text field? Then just check whether that text field is empty, or not?

In other words, if you guys were setting this up ... what approach would you take?

Thanks again for all the help.

[sierra7]

Hi again
Now that I have added the image as an ATTACHMENT my modified test-bed reports that there is an image present for every record (i.e. the control content is not evaluation to Null) even though there is only an image attached to one.

IsEmpty() did not work either. At least this is now consistant with Corey's findings so are on the same song-sheet as it were.

I want to try a few more things yet

S7

[sierra7]

Some success!

You can test the AttachmentCount property to see is an image is attached.

If Me.P2.AttachmentCount = 0 Then
    MsgBox "Image is blank"
    Me.P2.Height = 0
Else
    MsgBox "Image is present"
    Me.P2.Height = 5 * 567
End If
 

It seems that you can add multiple attachements to one record.

Interestingly the CurrentAttachment property seems to return zero whether or not a file is attached (possibly because the control did not have focus)

MSDN details the Attachument properties herehttp://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Corey, in answer to your question "How would you guys do it?", last time I think I embedded the path to the external image, in the image control. The object was always to keep the images outside the database.

I think I have read about Access-2010 being able to store Binary Large Objects (BLOB's) efficiently but I could not find any references when I looked the other night. When I built the test system I ended up using OLE objects bnecause I could not find BLOBs. When I first used OLE (15 yrs ago) it was VERY inefficient, I was told because it embedded bits of the executable required to interprete the linked object. I don't know if is still true (or ever was)

S7

[NeoPa]

I would go along with S7's thinking. Storage space is at less of a premium nowadays, but Access databases still have a 2GB limit last I heard. I expect there is still a performance hit with linked images, but the space reduction is quite heavy too. The only real way to tell what suits you best is to suck it and see. I would expect the issues to be performance and size though.