Photo Retrieval Access 97

Hi
I have been given a database of product codes with responding jpg file locations in the 2nd field. I have also got the folder containing the raw jpg photos.

After adding further information fields to the file from another database, I want to print a catalogue with the pictures next to each item.

I have only ever created a report with pictures from embedded photos, is there a way I can create a report including the photos but based on the file link, WITHOUT having to embed each product line (I have 6,000 plus of these). See example of raw data below

Partcode Filename
AM111 "P:\AM111.jpg"
AM112 "P:\AM112.jpg"

 

PAULWKIRKMAN, I am not sure if this can be done in Access 97, it certainly can be done in Access 2000 with some very simple VBA.

 

I can put this table in Access 2000 no problem, how is it done in 2000?

 

If you can Import the table in to Access 2000 and then create a report it will be easy from there.
Create the report with the Product Code and the jpeg location Field, then "Embed" any one of the jpegs in the detail section next to the Product code.
Click on the bar that seperates the Header section from the detail section, and in the Properties Event Tab you will see the "On Format" event, click that and choose "Event Procedure" and then click on the 3 small dots on the right hand side. This will take you in to the VB Editor in the On Format Event.
You need to type in the following, (adjusting the names to suit your Report)
If Not Me!Photo = "" Or Not IsNull(Me!Photo) Then
Me!Picture.Picture = Me!Photo
Else
Me!Picture.Picture = ""
End If
In this example the Me!Picture is the name of the "Image" field that you have imbedded and the me!Photo is the name of the field containing the Full Path to the jpeg file.
once you have created this VBA you can go back to the Imbedded field and delete the name of the jpeg file that you used to create it.
The VBA will replace it with the "Me!Photo" field's location of the jpeg for each record in the table.
If you can't get it to work I can do it for you.

The Else section of the codes puts "nothing" in the Photo field if there is no Path in th me!Photo field.

 

Many thanks for your input. I tried this on a sample of five records, when the report opens it comes up with the importing (name of jpeg) ten times (!? Is this correct).
Also the report displays on one page but with only one picture displayed as a back ground, similar to a water mark, on top of which are my five white boxes next to my Partcode Field Names. Is it correct that I put the code in the Detail - On Format section?

 

For you information the code I used is as follows:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not Me!Photo = "" Or Not IsNull(Me!Photo) Then
Me.Picture = Me!Photo
Else
Me.Picture = ""
End If
End Sub

Regards
Paul

 

Paul, it has to go in the Detail On Format, because that is where the individual records are, can you post a zipped copy of the database, or email it to me?
I can send you a copy of the first one of these that I worked on, but it has the Report in "Label" style ie more than one item per line.

 

Is there a similar coding method for getting it to work on a form without having to embed each jpeg?

 

The ZIP file size is too large to attach. Is there another way? I'm new to this site can I email it to you direct?

 

Paul, yes you can do it on a form as well, using the same method and code.
I will private mail you my email address.

 

Paul, try the one that I have just sent you.