CODEWITHSUNDEEP
ms-accessms-access-2010
user3155123
user3155123

Reputation: 11

Creating a report that will display a number of images from filenames in a table

I have a VBScript that I use to read an XML file and populate a couple of tables in Access 2010. The VBScript also copies jpg files from a card reader to a location on the server. The VBScript creates a unique folder based on date/time...the folder location of each image name is written to the database as one of the fields in UNC path\filename.jpg notation.

I have a primary database and the transaction (images) are referenced in the second database. I have created a relationship as well... I am able to build my report in Access from data from both the tables, however, I'd like to automate a print job that grabs the last entry to the primary database, it's associated images (up to 10...but not 10 in every case) and print them out. When I try to build the report I cannot figure out how to use a query result of the field with the filenames to display on the report.

Upvotes: 1

Views: 4083

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

You can do what you describe using an Image control and a line of VBA code. For a sample table named [ImageLocations]

ImageLocation                           
-----------------------------------
\\SERVER\Public\Pictures\image1.jpg        
\\SERVER\Public\Pictures\image2.jpg

I created a report with that table as the Record Source, dropped the [ImageLocation] field onto the Detail band, and added an Image control. I used the following as the On Format event of the report's Detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.ImageControl.Picture = Me.ImageLocation.Value
End Sub

When I print the report I see the images associated with the filenames in the table.

Edit re: comment

Newer versions of Access (since at least Access 2010) do not need the On Format VBA code. We can simply place an Image control on the report and set its Control Source property to the (text) field containing the path to the image file. Thanks to Albert D. Kallal for the tip!

Upvotes: 2

Related Questions