Combo Box & Photos

[maxx102]

Hello everyone I have a question and I wondering if my access form will support this idea. First let me explain what I am trying to do.

Our security department recently received 30 radios and so I am trying to come up with a radio "Check Out / Check In" board. I have a form created already and I have a table set up that is organized 1st by department and then by alphabetical order. What I would like to do now is have a picture of the person who checked out the radio be displayed and also remain displayed until the radio is checked back in.

So, if John Q. Smith shows up and checks out "radio #5", go to the database click on the combo box next to "Radio #5" , find John Q. Smith in the list, select it and then his picture is displayed next to his name. The reason for this is we have fairly high turnover and not all of our personnel knows everyone so having a name with a face would be helpful.

Is access capable of doing this or am I off base here?



Thanks

[ItsMe]

You can store photos of employees on a server using a shared folder. You can store the directory path to each photo with the relative employee record. I would advise storing the path as plain text and avoid using hyperlink data types.

So the idea is to have a table for the employees. This table will have one record for each employee. One column will store the path (plain text) to the picture on the server.

[June7]

Images can also be stored in the table in an attachment or OLEObject field. However, lots of images can use up Access 2GB size limit rather fast. But if your image set is small and the images are small (jpg/bmp/png less than 100kb), this may be preferable in your case.

I've not had good experience with OLEObject field so I use the Attachment field and Image control bound to field. The Attachment field is a type of multi-value field.

[maxx102]

So the size of the photos will vary a little bit, but they are going to be around 20 KB or less per picture (in .jpg format).

So there are things that I think I have to do, please tell me if below is correct?
1st - create a file for the pictures to reside in.
2nd - create a column for the database to look for the pictures.
3rd - go to each person and ensure that the database is pulling up the correct picture.
4th - create a "picture" space for the picture to show up in the actual form.

[ItsMe]

There are two basic approaches you can take. You can store the jpg files inside the database or you can store the jpg files on a server somewhere. The method I described is to store them on a server. I find it easier to manage files stored on a server than managing files stores in a database. You might want to investigate the Attachment data type and the associated Attachment Control before deciding which approach to use in your situation.

If you choose to store the files on a server ...

1st - create a file for the pictures to reside in.

You want to create a folder that all client machines have access to. Create a shared folder to store all of the picture files.

2nd - create a column for the database to look for the pictures.

Yes. Create a column of type text. You would store text similar to this ... \\ServerName\Folder\filename.jpg

3rd - go to each person and ensure that the database is pulling up the correct picture.

You want to verify that each record in your table is storing the correct path.
Record 1 = \\ServerName\Folder\Joe.jpg
Record 2 = \\ServerName\Folder\Martha.jpg
Record 3 = \\ServerName\Folder\Frank.jpg

4th - create a "picture" space for the picture to show up in the actual form.

You will want to add a control to your form while in design view. The type of control I would use is the Image Control. Depending on your version of Access, you can use "Insert Image" from the Ribbon and select "Browse". Browse to one of the images in the shared folder and select it. From there, you will want to change one of the properties of the new image control.

Change the Picture Type property from Shared to Linked. Click 'OK' or 'Yes' when prompted to remove the image. Now you can use a little bit of code to dynamically update the Picture Property. When you get to this point, the hard work is out of the way. The one caveat is the image control will be one size. So, if possible, you want to standardize the size of the employee picture. When I say, "Size", I mean dimension of width and height.

[June7]

I don't use Picture property. I use the Image control ControlSource property. No VBA code is required.

=[fieldname]

It is possible to only store the image name in the text field and then build the path in the ControlSource with an expression:

="\\ServerName\Folder\" & [fieldname]

If each image is named with the employee ID, then you don't even need another field in the table, just use the EmployeeID field:

="\\ServerName\Folder\" & [EmployeeID] & ".jpg"

Or reference the combobox that has a column for the EmployeeID:

="\\ServerName\Folder\" & [cbxEmployee].[Column](x) & ".jpg"

The advantage is if you have to move the image files, there is no need to edit the data in the table, just change the expression.

[maxx102]

I should also mention that I intend on this to be all stored locally on a tablet with no access to a server. We received some cheap tablets from another department that didn't need them so rather than returning them, they gave them to us to use. Our IT department won't allow them to be connected the company network so all updates will be done via PC and then transferred over to the tablet using a flash drive.

Will this make a difference at all?

[Perceptus]

Shouldn't make a difference. but If you are recording data to all these tablets getting that data to merge could be daunting down the road.

[maxx102]

My next question - what picture setting should I use for the photo space?

I have three choices -
Unbound Object Frame
Bound Object Frame
Image

[June7]

I always use Image control. My experience with Object Frame has not been positive. But it has been a while since I played with it.

[ItsMe]

My vote is Image Control. To be honest, I do not think there is much difference between the Image Control and a classic OLE control. Just that, an Image Control is specific for Images. So it is easier to set up and understand the Image Control's properties.