Displaying an image on an Access Report, conditionally

[ctsmith84]

Hello all.

I'm having an issue trying to create a report using Access and VBA. The project is actually creating a product catalog ... and I have used a report to do so. I don't have a lot of knowledge of Access VBA (but do have experience with VB).

The problem that I am trying to tackle is of adding an image above each categories header that displays an advertising banner (from a different table that is linked to the product table) for that given category. The way I have it set up now is that I have a second group header that displays before the main (category) group header.

The tricky part is that not every category has an image that goes along with it ... and when there is not one, I need for that section to not show up. I've tried a few different things (mainly suggestions I've been given elsewhere), but have not gotten anything to work.

How can I test the "Image" field to see if it's null for each category that displays on the report?

Can I do something like:

If IsNull ([somehow access the 'Image' field here]) Then
GroupHeader1.Visible = False
Else
GroupHeader1.Visible = True
End IF

If I can ... how do I reference the field I need to reference in the IF?


Thanks in advance!

Name of report: "Grocery Section"
Section Name: GroupHeader1
Field needing to be tested: Image
Control holding the image: Image39
Table with the images: "Advertising Images"

 

[vbaInet]

How can I test the "Image" field to see if it's null for each category that displays on the report?

Can I do something like:

If IsNull ([somehow access the 'Image' field here]) Then
GroupHeader1.Visible = False
Else
GroupHeader1.Visible = True
End IF

It depends on how the image is saved? Is it saved in a table or the path to the image (that is located on the hard drive) is stored in a field?

If I can ... how do I reference the field I need to reference in the IF?

You mentioned that it's in a different table that is linked to the Products table, so why not join it that table and include it in the list of fields for that report. Or link it via a subreport.

 

[ctsmith84]

Right now we have it saved in a table under an attachment field ... which I'm thinking is the reason we are having the problems we are having (not being able to find a way to test if that field is empty, or not). And we already have the tables linked, and the field added ... it just is not working as expected.

Would the better solution be to store the paths to their locations?

 

[vbaInet]

I believe you can test whether an attachment is empty or not but storing the image in the table is not the best option. Have a read through this:

http://office.microsoft.com/en-us/access-help/store-images-in-a-database-HP005280225.aspx

Yes, that's correct. The link above discusses this approach.

 

[Privateer]

CTSmith84,
If I read you correctly, you have the advertizing banner in a group called groupheader1. In the design mode of the report, right click on the box in the upper left corner of that group and select properties. Locate the "Can Shrink" property and set it to yes. Then click on any and all text boxs in that group (Images) and set the shrink to yes. Now if that text box is empty, the report will not "print" that group, it will shrink up, which I think is what you want. Let me know.
And to give a complete answer, if you create a module for the report, you can check the value of something in the recordset and modify the report accordingly. As an example, I had a client who wanted the words Draft or Final to appear on the report. So on the report form, I gave them a check box, the code in the module tested for it and put the appropriate word up there. Good Luck.
Privateer

 

[getmore]

thank you for the links, working fine for me