# Thread: Automatically Displaying Staff Photo on Form?

1. Registered User
Join Date
May 2008
Location
London, UK
Posts
46

## Unanswered: Automatically Displaying Staff Photo on Form?

Hi,

This is my first post here so please forgive me if this question has been asked before.

I have a very simple Access Database with staff members First Names, Surnames, Departments & Current Employment Status. This database contains over 6,000 individual records so adding the individual images to records would be a pain!

Our images are formated by surname (space) firstname.jpg, eg. John Smith = "Smith John.jpg"

I would like to display the images on the individual records if possible, but clicking a button to open a pop up or otherwise would fine also.

Just to add to the confusion - if there was any way of printing the details and photo as a report that would be very useful?! I have a script that will print an individual record from a report if that helps?

Regards,

Mike

2. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Ok, so you can use an Unbound Object Frame on your form or report and use VBA to set it whenever you change records (On Current).

You can do the same thing for reports

3. Registered User
Join Date
May 2008
Location
London, UK
Posts
46
Thanks for the response StarTrekker, please forgive me I am really only starting out with access - I am a little unsure how to impliment your suggestion?

I can create an unbound object frame using the appropriate tool, but it then offers me a wizard, I am uncertain of what to choose here?

As for the VB code I guess something like:

FileName = Me!Surname & Me!FirstName & ".jpg"

Would probably give the file name I need but again I don't really know how to impliment this either! I am familiar with using the visual basic code window to insert and modify items if that helps.

("Newbies"!!)

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
store the URL to you image within your database... don't make it up on the fly from the surname & forename, as you cannot guarantee these to be unique, they may be right now, but you cant' guaranteee that.

insert a image control on your form.

place some code in the forms on current event, and the on lost focus event for the control you use to define the URL to the image. Id suggest you use a function to actually add the image.. the reason.. you can then insert an "image not found" image

to pale code behind the events switch to the VB/code window or rightclick to pull up the properties on the selected element

your funciton coudl be something like
if isnull(pathtoimage)=true ot strlen(pathtoimage)<5 then
mycontrolname.image = "C:\a\path\to\an\image\imagenotfound.jpg"
else
mycontrolname.image = pathtoimage
endif
end sub

you could extend the sub to test to se if the file exists, and if not display the imagenotfound picture

you will ned to select appropriate values for mycontrolname, the imagenotfound imahe, the path to the imagenotfound and so on.

if you are considering printing the image then you need to make sure you images on the report is at most 1/3rd the size on the screen.. if the image looks OK on a screen at around 9 cm wide, it should be printed at no more than 3cm wide on paper (however bear in mind that Access can and does scale images.. in which case as a rough guide make sure the printed mage is nor more than 1/3rd the raw pixel size. printers tend to use 300dpi or better, screens tend to be 60..70dpi

5. Registered User
Join Date
May 2008
Location
London, UK
Posts
46
Hi healdem,

The names and information being used in this database are taken from another database which will not allow 2 users with the same name, if there are for example 2 John Smiths, one will be "John Smith" the other will be "John Smith1". This is a function of the program used to create the data base.

I do like the "image not found" option you mention if that can be used?

The images are not very large anyway maybe a max of 300x375 px so image quality is not a major concern.

The other thing is that I have over 6,000 (6,960) individual records to deal with, so adding the images manually is not really a viable option for me, I belive this is what the above intails?

Thanks very much! Don't mean to be a pain!

6. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Originally Posted by setab
Thanks for the response StarTrekker, please forgive me I am really only starting out with access - I am a little unsure how to impliment your suggestion?
Nothing to forgive

Originally Posted by setab
I can create an unbound object frame using the appropriate tool, but it then offers me a wizard, I am uncertain of what to choose here?
Ok, you don't even need that. Choose Image from the toolbox. During the wizard, pick any .JPG and OK.

Delete the contents of the Picture property from the object.

Resize the picture object to the size you want.

Set the Size Mode property to Zoom.

Give the object a name like imgPhoto.

Originally Posted by setab
FileName = Me!Surname & Me!FirstName & ".jpg"
Yes, but don't forget the space:

strFileName = Me.Surname & " " & Me.Firstname & ".jpg"

That will be part of the code.

Then, set your code for your form's On Current event procedure to something like:

strPhotoPath = "C:\PhotoFolder\" ' set this to wherever your photos are.
strFileName = Me.Surname & " " & Me.Firstname & ".jpg"
Me.imgPhoto.Picture = strPhotoPath & strFileName

Of course, you'll need to handle the error when the photo may be missing.

Originally Posted by setab
...another database which will not allow 2 users with the same name, if there are for example 2 John Smiths, one will be "John Smith" the other will be "John Smith1". This is a function of the program used to create the data base.
OMG that's horrible design So what happens there? Will there be a "Smith1 John.jpg" file?

Originally Posted by setab
I do like the "image not found" option you mention if that can be used?
Yes, you can have error trapping alter the strFileName to that of an image that shows these words or whatever else you want to show.

So, to complete the code, with error trapping:

Code:
On Error Goto OC_ERR
strPhotoPath = "C:\PhotoFolder\" ' set this to wherever your photos are.
strFileName = Me.Surname & " " & Me.Firstname & ".jpg"
Me.imgPhoto.Picture = strPhotoPath & strFileName

Exit Sub

OC_ERR:
If Err.Number = 2220 Then ' couldn't set the file
strFileName = "NotFound.jpg"
Resume
End If
Just make sure you have a picture called NotFound.jpg in your Photo's folder!

Cheers

ST
Last edited by StarTrekker; 05-06-08 at 09:21.

7. Registered User
Join Date
May 2008
Location
London, UK
Posts
46
Hi Startrekker,

Sorry for the lack of response I have been off work for a few days.

I tried to impliment the code above but I'm having some issues in that access crashes (freezes) when you try to open the form containing the picture (have to exit via task manager) ?

I belive I have followed your insructions properly but I just can't get it to work?

The Code for "Form_CardholderDetails" is currently:
Code:
Option Compare Database
Private Sub Form_Current()
On Error GoTo OC_ERR
strPhotoPath = "C:\images\"
strFileName = Me.Surname & " " & Me.FirstName & ".JPG"
Me.imgPhoto.Picture = strPhotoPath & srtFileName
Exit Sub
OC_ERR:
If Err.Number = 2220 Then ' couldn't set the file
strFileName = "NotFound.JPG"
Resume
End If
End Sub
I have the Image frame properties set to:
Name: imgPhoto
Picture: (none)
Picture Type: Linked (have tried embedded also)
Size Mode: Zoom

I have tried this with all 6960 records and just 1-3, the images are in C:\images and there is a NotFound.JPG but I'm still having problems?

I also tried making the background of the image box a solid color and making it the same size as the image it is trying to open.

Thanks in advance for any assistance you may be able to offer.

Regards,

Mike

8. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049

Change the code to:

Code:
Option Compare Database
Private Sub Form_Current()
On Error GoTo OC_ERR
strPhotoPath = "C:\images\"
strFileName = Me.Surname & " " & Me.FirstName & ".JPG"
Me.imgPhoto.Picture = strPhotoPath & srtFileName
Exit Sub

OC_ERR:
If Err.Number = 2220 Then ' couldn't set the file
strFileName = "NotFound.JPG"
Resume
Else
MsgBox Err.Number & " - " & Err.Description
Resume Next
End If
End Sub
Then try again. This time is should give you an error message and then quit.

9. Registered User
Join Date
May 2008
Location
London, UK
Posts
46

I'm afraid its still freezing, would it be ok for me to post a blank copy of the database for you to have a quick look at?

Thanks again!

Mike

10. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
By all means

11. Registered User
Join Date
May 2008
Location
London, UK
Posts
46
Here we go, hope this is ok - obviously its in its earliest stage so please forgive the very basic layout!

Thanks!

12. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
D'OH!

Another error... str =/= srt

Me.imgPhoto.Picture = strPhotoPath & srtFileName

...should be...

Me.imgPhoto.Picture = strPhotoPath & strFileName

If you add Option Explicit to your code then errors like this will become all to obvious.

Code:
Option Compare Database
Option Explicit

Private Sub Form_Current()
On Error GoTo OC_ERR
Dim strPhotoPath As String
Dim strFileName As String
strPhotoPath = "C:\images\"
strFileName = Me.Surname & " " & Me.FirstName & ".JPG"
Me.imgPhoto.Picture = strPhotoPath & strFileName
Exit Sub

OC_ERR:
If Err.Number = 2220 Then ' couldn't set the file
strFileName = "NotFound.JPG"
Resume
Else
MsgBox Err.Number & " - " & Err.Description
Resume Next
End If
End Sub
Again, just make sure you have a picture called NotFound.jpg in your picture path!

BTW, this crash can be corrected (the loop can be stopped) by pressing CTRL-BREAK.
Last edited by StarTrekker; 05-09-08 at 03:51.

13. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
I edited it to Dim the variables too

14. Registered User
Join Date
May 2008
Location
London, UK
Posts
46
I have implimented the above and it has stopped it freezing up but it is still not displaying an image for me?

I'm getting a message "Compile Error: Variable not defined" and strPhotoPath is highlighted grey and Private Sub Form_Current() in yellow, when I open the form?

15. Registered User
Join Date
May 2008
Location
London, UK
Posts
46
Realised I had typed something wrong! You are an Access Lord!

Thank you very very much!

I'm sure you'll hear from me in the near future regarding something else! I really appreciate all your help!

Regards,

Mike

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•