Introduction.
MS-Access Tables have the OLE Object/Attachment Field to store and display images on Forms/Reports.
Example: Storing the employee photo or Product image on the data table.
Depending on the Staff size of your Company or the product items in stock, the number of images stored directly in their respective table can increase the database size considerably. In Access 2007 the Bitmap Images (.BMP image files) stored in the Attachment field are automatically compressed and reduced to the size of a JPG image. In either case, the database size will increase, with the addition of each image in the table. If you have hundreds or thousands of images to store, then you can imagine what will happen. The maximum size of a database can be only 2 GigaBytes.
Identifying the Image with its Related Record.
But, if you can foresee this issue of your Project and plan to store the required images in a dedicated folder on the disk (Server or Local depending on the User requirements) then you don’t have to bother about the database size at all. But, you should be able to identify and pick the correct image, related to a particular record from the disk easily, and display it on a Form or Report, whenever the need arises.
Organizing the Images.
It is important that we organize the images on disk in such a way that we can easily identify which image belongs to which record. Once this is properly planned and executed we can pick the correct image and display it on Form/Report, in the Image Control very easily.
The easiest approach is to give names to images with some unique field value of the related table, like the Employee ID of the Employee record. For example, an employee’s photo name can be the Employee’s ID value, like 1.bmp, 2.bmp, etc. Each record in the Products Table will have Product Codes as unique values and images can be named with the product code of each item.
When a particular employee’s record is currently on the Form we can read the employee code from the form and add the image extension (like Me![ID] & “.jpg”) to create the image name and load it from the disk into an image control on the Form.
If you prefer to create an image type smaller in file size, to save disk space, then you may choose any one of these types: png, jpg, or gif. GIF images are lesser in size, but image quality may not be good. Select only one of these image types and save all the images in that type, say jpg, then the program will be a simple routine, to load the image into an image control on the form/report.
We need only a small Sub-Routine that runs on the Form_Current() Event Procedure to load the picture into the Image control.
Prepare for a Trial Run.
Let us prepare for a trial run of a small Program.
Import the Employees Table from Microsoft Access Northwind.mdb sample database.
Use the Form Wizard to create a Quick Form in Column Format.
Move or resize the Fields (or remove some unwanted fields) so that we can add an Image Control on the Form, big enough to display sample images, which you are going to create now.
A sample Employees Form Design is given below, with the Image Control on it:
Select the Image Control from the Toolbox and draw an Image Control on the Form, as shown above.
Display the Image Control’s Property Sheet (F4) and change the Name Property value to ImgCtl.
Save the Form with the name Employees.
Note: As I have mentioned earlier, the Employee ID field values are 1,2,3, etc. We will create a few images with the names: 1.jpg, 2.jpg, 3.jpg, etc.
If you were able to Import the Employees Table from Northwind.mdb then you are lucky, you have the employee’s photo bitmap images in the OLE Object field. You can save these images on the disk. I am using Access2007 and I will explain the procedure of Access2007 to save the image from Employees Table to disk. If you are using any other Access Version the menu options may be different.
Open Employees Table in Datasheet View.
Find the Photo field of the first record, the Employee ID of this record is 1.
Right-Click on the Photo field with the Bitmap Image caption, and select Open from the Bitmap Image Object option. The image will open in the Paint program.
Before saving the image to disk, create a new folder C:\images.
Use Save As… option to save the image in folder C:\images with the name 1.jpg.
Save a few more employee photos in this way.
If you could not save images from Employees Table then open any image from your disk in Paint Program and save them as 1.jpg, 2.jpg, 3.jpg, etc., in C:\images Folder.
Open the Employees Form (you have saved in Step-6) in Design View.
Press ALT+F11 to display the VBA Window.
Copy and paste the following code into the VBA Window of the Form, below the Module Global Option: Option Compare Database.
Private Sub Form_Current()
Dim strImagePath As String, pic As String
On Error GoTo Form_Current_Err
'image folder
strImagePath = "c:\images\"
'create image name
pic = Me![ID] & ".jpg"
strImagePath = strImagePath & pic
'validate image name and location
If Len(Dir(strImagePath)) > 0 Then
'image found, load it into the image control
Me!ImgCtl.Picture = strImagePath
Else
'image not found, make image control empty.
Me!ImgCtl.Picture = ""
End If
Form_Current_Exit:
Exit Sub
Form_Current_Err:
'Not necessary to display a message here
Resume Form_Current_Exit
End Sub
Save and close the Form.
The Trial Run.
Open the Form in Normal View.
If everything went on well then you will see the first employee photo in the image control. The Sample Screenshot of the Employees Form with the photo is given below:
Use the record navigation control to move to the next record and display other images also.
Image Display on Report.
A sample Employees' Report Design, with Image Control, is given below.
Design a Report using a few fields from the Employees table, as shown above, and see that you are using the Employee [ID] Field on the Report. This is important because we need this number to create the image name corresponding to the record in print.
Insert the Image Control on the Report, as shown in the above Image.
Display its Property Sheet and change the Name Property value to ImgCtl.
Click somewhere on the empty area of the Detail Section of the Report. If you have closed the Property Sheet then press F4 to display it.
Select the On Format Event Property and click on the build ( . . . ) Button to open the VBA Window of the Report.
Copy and Paste the following code and replace the Detail_Format() Event procedure opening and closing statements:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strImagePath As String, pic As String
On Error GoTo Detail_Format_Err
'image folder
strImagePath = "c:\images\"
'create image name
pic = Me![ID] & ".jpg"
strImagePath = strImagePath & pic
'validate image name and location
If Len(Dir(strImagePath)) > 0 Then
'if found load the image into the image control
Me!ImgCtl.Picture = strImagePath
Else
'make image control empty.
Me!ImgCtl.Picture = ""
End If
Detail_Format_Exit:
Exit Sub
Detail_Format_Err:
'Not necessary to display a message here
Resume Detail_Format_Exit
End Sub
Save the Report and close it.
The Trial Run of the Report.
Open the Report in Print Preview.
The Sample Report Preview is given below:
If you want to give more flexibility to your project, with all the four image types (bmp, png, jpg, and gif) then you may use the following modified Code:
Private Sub Form_Current()
Dim strImagePath As String, pic As String
Dim strImagePathName As String, strI(0 To 3) As String
Dim j As Integer, strType As String
On Error GoTo Form_Current_Err
strI(0) = ".bmp"
strI(1) = ".png"
strI(2) = ".jpg"
strI(3) = ".gif"
strImagePath = "c:\images\"
pic = Me![ID]
strType = ""
For j = 0 To UBound(strI)
strImagePathName = strImagePath & pic & strI(j)
If Len(Dir(strImagePathName)) > 0 Then
strType = strI(j)
Exit For
End If
Next
strImagePathName = strImagePath & pic & strType
If Len(strType) > 0 Then
Me!ImgCtl.Picture = strImagePathName
Else
Me!ImgCtl.Picture = ""
End If
Form_Current_Exit:
Exit Sub
Form_Current_Err:
'Not necessary to display a message here
Resume Form_Current_Exit
End SubNote: Report must be in Print-Preview mode to view the images on Report. The Images may not appear in Layout View.
