Including Photos in Database

[gschimek]

I've got a database that tracks name/address/phone, etc for a large group of people. I've recently added the ability to include a person's photo on the forms and reports. I've done that by saving the photos with the naming convention of [firstname]_[lastname].jpg and then using VBA, the database checks to see if there is a file with the corresponding name of the record and displays the picture if so.

It works very well, but I can see where I would have some problems in the future. For example, if someone gets married and changes their name, the corresponding photo must also be renamed to display properly. And if there are any misspellings, that becomes a problem. Also, if James decides he wants to go by Jim, the photo would not be displayed, either.

I'm wondering if anyone has any other ideas on how I might accomplish my goal. I know I could name the photo based on the record ID, but that's not a good option, since I'll be handling the photos, while someone else will be inputting the contact details. So I won't know the recordID easily.

Is there some way to have another field in each record that points to the corresponding photo? Then if James goes by Jim, his first name can be changed, but the corresponding picture name could remain the same.

Is there some great idea that I'm missing that would make this process much simpler and scalable? Or do I just have to deal with the maintenance issues?

Thanks.

 

[KeithG]

I would go by the PK of the record. How do you not have easy access to the PK but you have access to the name? The name and PK are stored in the same record.

 

[gschimek]

It's because I create the database and manage the frontend, but someone else does the data entry and consequently manages the backend. I don't have easy access to the backend.

Right now I can know that I need a picture for the new record Jim_Smith, and so I can create the picture file for him to later be copied the the directory where all the pictures are stored, which is on another computer with the backend.

The other person who maintains the backend can be entering the new record into the database, so Jim_Smith gets recordID 12345. But I don't know what recordID he gets assigned until after the record has been entered and I can get access to the backend to look it up. But I already know his name, so it's much quicker for me to create a file called Jim_Smith.jpg, instead of waiting until the record has been entered, then looking up the recordID, and then calling the file 12345.jpg.

I think I may have the beginnings of an answer. I found some information about how to create a text field and enter the path of the file. I just now need to figure out how to automate that to check and see if there is an existing picture.

Wish me luck.

 

[CraigDolphin]

This is the general approach I use. Store the full path and filename in a field, then set the source of an image on the form (in the on_current event of the form), or if it is null set it to a default 'NoPhoto' image to use instead.

Private Sub Form_OnCurrent()
Me.Image65.Picture = Nz(Me.FilePathField,"c:\directory\subdir\NoPhoto.jpg")
End Sub

 

[DJNickWatt]

Why don't you add a button in the front end which you would then click, navigate to the picture you have saved with whatever filename you want, retrieve that path & filename and store that value in a field associated with the contact. That way the picture will always be associated with the record no matter what you change the persons name to.

 

[gschimek]

That's a good idea. But I don't really know how to do it. If you've got some sample code, I'd look into it.

For now, I just set up a field to store the location of the picture. Then when each record is opened, it checks to see if there's a value in that field. If not, it then checks to see if there's a corresponding picture in the pictures folder and then stores it's location in that text field. Then I can change the person's name all I want, but it's still linked to that original picture file. It's not a perfect solution, but it should work for now.

 

[DJNickWatt]

I'm at work now. Will sort you out a solution when I get home tonight.

 

[CraigDolphin]

Here's a quick version I cut out of one of my db's that shows how to do it (Access 2000). In this case, the jpegs are not photos of people but of water sampling sites (potentially many photos for one site) and one map per site.

Obviously, I haven't included any actual photos, just some small images (for filesize) to use instead to show there's no map or photo.

Or, better yet, have a look at OldSoftBoss's excellent example that is posted in the examples section http://www.access-programmers.co.uk/forums/showthread.php?t=120834