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
        , [U][B][Advertising Images].Image[/B][/U]
   
   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 programmaticall y. 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.