If you work with Microsoft Access 2003 you may discover that you can only display bitmap images in Access forms where you want to show a different image for each record. Try using a jpg or gif file for each record and you're stumped. This because the Access field type used to display images OLE (object link embedded) only caters for bitmap files. You can display a jpg or gif file in an Access form, such as a logo, in an image frame, but the image frame cannot be linked to separate image records, so the one image will always display on the form independent of the record selected.

However you can display a jpg or gif image for each record in an Access form, and this article describes how to achieve this, using some clever VBA code from Microsoft. This code uses an image frame on a form and changes the image in the image frame each time a record is changed. You don't need to understand VBA code to do this - all you need to be able to do is copy and paste two sets of code from a Microsoft web page. In this article we'll describe how to do this, and then use linked jpg images files stored in a particular folder to display a different image for each record. This technique will work for jpg, gif or indeed bmp files. We'll link the images rather than embed them in to avoid significantly increasing the size of the database file.

The steps we'll take to do this are as follows. Firstly we amend an Access table and add the image paths and filenames in a text field. Secondly we create a form based on this table. On this form we add an Image control and set it to display the first employee picture. We'll also give the Image control a particular name. On this form we also add two text boxes with particular names. One box is used to show the image file location and the other shows any messages. Thirdly we copy and paste two pieces of code from a Microsoft support web site. One piece is used to create a new module in the Access database and the other piece is used in the form itself. This code uses set names for the image control and the extra two text boxes on the form, so please stick to the names we'll specify later.

First step: Amending the Access Table
To get started you need to have an Access 2003 table containing records you want to use with different images, and you also need to have all the corresponding images stored in an accessible folder. For example you might have a table of Employee records, and you also have pictures of each person. We're going to add the path and filename for each employee record in the table in an additional text field. So in table design view add a new text field, and give the field any name you like, for example, Photo. Then in normal table view type the path and image name in the Photo field for each record. So for example your images might be stored in a folder called Employees on the C drive, and the images are named staff1.jpg, staff2.jpg and so on. In the Photo field in the first record you would type CEmployeesStaff1.jpg and so on down the list of employees.

Second step: Creating the Access Form
Next you create and save a form based on this table. Once this is done you need to add three controls to the form in design view. Firstly add an Image control in an empty space on the form, and in the resulting wizard navigate to and select the first employee image file. Then in the Image control properties, give the Image control the name ImageControl and set the image source as linked rather than embedded. Next add two text boxes to the form and delete both labels. Rename one text box TxtImageNote and leave the textbox unbound. Rename other text box TxtImageName and choose its control source Photo, then save the form. TxtImageName will then show the path and filename of the image being displayed.

Third step: Gathering the two pieces of VBA Code
We're next going to use two pieces of VBA code. The first piece becomes an Access module which creates a custom function to substitutes the jpg file detail into the image frame, and the other piece is used within the form to call the custom function to update the image frame picture on the form for the current record.

In your Access 2003 database create a new module. Then navigate to the code using this link http://support.microsoft.com/default.aspx?scid=kb%3Ben%2Dus%3B285820 and scroll down the web page to the part titled "Creating the custom function". If you find this link doesn't work you might try Googling "How to display images from a folder in an Access 2003 form" which should help you find this page. Carefully copy all the code in the custom function, switch back to the Access module and paste the code into the empty module. Unfortunately you end up with the first line of code in duplicate, so carefully delete this very first line of code "Option Compare Database" then save the module as Module1, and close the module.

The second piece of VBA code is from the same web page and will be added to your form. To do this, ensure the form is open, in design view. Then return to the web page and scroll further down to the section titled "Using the custom function in a form" and go to the part 2 of this section. Copy the code and switch back to your form, still in design view. Then choose View, Code and paste the code. Again, remove the extra line of code at the start "Option Compare Database", then save and close the form.

That's all three steps complete. Now if you view the form in regular view and navigate through the records you should see the picture change for each record. The image frame is being re-loaded with a new image every time the selected record changes.

If you've found this article useful then you might consider attending an Access training course to build on your skills.