Mutiple images in report records

[Sonny Jim]

I am a total novice with MS Access (2007) coding and I am struggling with trying to come up with a way to enable an insurance adjuster to add multiple images to any record and to generate a report that includes however many images that are associated with a given record. Can anybody help me out with this by offering strategies, examples, or informational references?

Because there may 60+ pictures associated with a single claim record my design will need to involve links to pictures residing outside the database in a folder. Each computer on the network must be able to be directed to the link addresses properly. Also, the reports will need to have a caption under each picture.

 

[PaulO]

Create a Header table containing the details of the claim, including a field called "Claim Number"

Create a second table with two fields ("Claim Number"; "Picture") holding separate records for each JPG

Link the table in a query via the common field i.e. "Claim Number"

This should then bring in all related JPG's for use in a Report

 

[Sonny Jim]

Thanks Paul, that is a very good straight forward approach but I am concerned that my database may grow too fast using that strategy. I should rephrase my question to include this concern. The insurance adjusters are prone to taking a lot of pictures, maybe up to 60 or better on occasion. I will need to involve a more complicated design that utilizes links to an external folder that holds the pictures.


[FONT=&quot]Also, I will need to come up with a design scheme that allows a report to show all the pictures associated with a particular insurance claim with a description under each picture.[/FONT]

 

[PaulO]

The first element of your requirement is tricky but manageable ... I have seen threads relating to this very requirement on this site but it's not my expertise, sorry. However, even with this solution you need to be efficient and consistent with how you manage your photos i.e. keep them under a certain file size and keep consistent dimensions ... this will make for easier design and subsequent loading of what will be a large report.

The second element is easily managed with an additional field "Description" in the second table, which can also be brought into the Query and Report.

 

[Simon_MT]

The best way to deal with this problem, and certainly prior to Access 2007, is to store the images separately and referentially render the images to the database. Even after Access 2007, you will be creating blobs and everytime your database, is read, it has to sift through these images. On the basis that once a cliam has been settled the images are no longer current, it would be much better to render these images only when required. If the images are referenced to the ClaimID plus a number then it would be easy to recognise these images.

The second caveat, if these images are large Access will have a hernia, even html does like large images so you will have to exert so sort of control on images sizes i.e. low resolution and a sensible size.

The difference with Access 2007 is that images can be stored in their native format not bitmaps. The jpeg format whilst lossy as it degrades colour transitions slightly but not noticably at 60% of the original.

Simon

 

[PaulO]

The best way to deal with this problem, and certainly prior to Access 2007, is to store the images separately and referentially render the images to the database. Even after Access 2007, you will be creating blobs and everytime your database, is read, it has to sift through these images. On the basis that once a cliam has been settled the images are no longer current, it would be much better to render these images only when required. If the images are referenced to the ClaimID plus a number then it would be easy to recognise these images.

The second caveat, if these images are large Access will have a hernia, even html does like large images so you will have to exert so sort of control on images sizes i.e. low resolution and a sensible size.

The difference with Access 2007 is that images can be stored in their native format not bitmaps. The jpeg format whilst lossy as it degrades colour transitions slightly but not noticably at 60% of the original.

Simon

Agree totally about picture file size and resolution as even a single large image can cause Access a seizure!

What's the best technique to store the images and index/render them back to the database?

 

[Simon_MT]

Assuming you are using Access 2007+.

I store each image with the StockID reference. So to find the image it would be StockID & ".jpg" in your case ClaimNo & PictureID & ".jpg" so there are two methods

Access 2007:

In your underlying Query create an Expression:

Thumbnailfile: ClaimNo & PictureID & ".jpg"

Create a control on a Form or Report:

ImageFile as =[Forms]![Menu]![ImageDirectory] & [ThumbnailFile]

Use the Image Control with no ControlSource

Then in the ControlSource put =[ImageFile]

All you need to do is to specifiy where the images are located I use a control on the main Menu. You can do this in your Query or Form or Report.

The second method involves is exactly the same but instead of changing the Image Control's ControlSource you use a Function GetPicture

Function GetPicture()
    With CodeContextObject
        If Dir(.[ImageFile]) <> Empty Then
            .[ImageControl].Visible = True
            .[ImageControl].Picture = .[ImageFile]
        Else
            .[ImageControl].Visible = False
        End If
    End With
End Function

This is the basics. If you want to know what size the image is try this:

Function GetPictureSize()
Dim fs, f
        With CodeContextObject
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFile(.[ImageFile])
        GetPictureSize = f.Size
        Set f = Nothing
        Set fs = Nothing
    End With
End Function

If you create a field ImageSize then you ca do this:

Function SetPictureSize()
    With CodeContextObject
            .[ImageSize] = GetPictureSize
    End With
End Function

Simon

 

[PaulO]

Thanks ... I'll try this out asap

 

[Sonny Jim]

Thank you, gentlemen, for generously sharing your wisdom, experience, time, and concern! I hope you will accept my sincere apologies for my delayed response. I will give this a go and let you know how I make out with this.