Storing Pictures In An Access Database pt2

Now that you have created your form to store the path name all you need to do is type in the filename of your picture for each record, and that’s it!

However, if that’s all we were going to do it would be pretty pointless having this long tutorial, we would just tell you to have a text field with all your filenames stored in it.

In this part of the tutorial we will explain how to add a little functionality to the form allowing you to select the file you want using the standard Windows file selector box, and display a thumbnail of the picture on the form. We will break the features into four parts:

  • Adding a VBA module to handle the Windows calls to get the filename
  • Selecting the filename for the picture using the file selector box.
  • Updating the thumbnail view on the form.
  • Opening the picture in your default image handler for that picture type.

Adding the VBA Module

Before you insert the code to display get the filename, and display the thumbnail etc you will need to insert a lengthy piece of VBA code into your database in the form of a module, the code is far too long to explain on this page, but click here to get instructions for adding the Module to your project.

Adding the File Selector Box

In order to add the file selector box we need to add some VBA code to an event, in our example we add the code to the ‘On Double Click’ event of the text box that will hold the filename

  • So, in design mode for the form, select the textbox and press F4 to bring up the properties dialog.
  • Click the ‘Event’ tab, and select the ‘On Dbl Click’ event.
  • Click the ellipsis button to the right of the box and select ‘Code builder’ from the dialog

Now you are in the VBA editor you need to paste the following code into this event.

Dim lngFlags As Long
Dim strFilter As String
Dim strPathAndFile As String
Me.AllowDeletions = False
strFilter = ahtAddFilterItem(strFilter, “Compressed Image Files (*.jpg, *.jff, *.gif, *.tiff )”,”*.JPG;*.JFF,*.GIF,*.TIF”)
strFilter = ahtAddFilterItem(strFilter, “Uncompressed Image Files (*.bmp, *.wmf)”, “*.BMP, *.WMF”)
‘strFilter = ahtAddFilterItem(strFilter, “All Files (*.*)”, “*.*”)
strPathAndFile = ahtCommonFileOpenSave(InitialDir:=”c:”, Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags,_ DialogTitle:=”Choose an Image File”)
If Len(strPathAndFile) > 0 Then
MsgBox “You selected: ” & strPathAndFile ‘Tells you what file you selected
Me![PicPath].SetFocus ‘Sets focus on PicPath Textbox
Me![PicPath].Text = strPathAndFile ‘Inserts filename string into PicPath textbox
Me![ImgControl].Picture = Me![PicPath1] ‘Sets thumbnail to look at the PicPath Filename
Else
MsgBox “You didn’t select a file”, , “Flower Index Image Handler”
Cancel = True
End If

Once you have pasted this code in, you need to save your database, and try it out:

  • Open the form, and double click on the ‘Picture Pathname’ textbox, if all goes well you will be presented with the standard Windows file selector box, navigate to any picture you like, and select it as normal.
  • The form will update, and a confirmation message will be displayed showing which file you selected. Lastly, the thumbnail view will update to reflect the file you selected.
  • Add a few records to the database each with a different picture then exit the database.

Adding the thumbnail update code

When you re-start the database and use your form to browse through the few records you created above you will notice that the thumbnail doesn’t automatically appear, this is deliberate to help with error avoidance, what you will now need to do is add a few lines of code to make the thumbnail appear ‘On demand’.

To achieve this we will add some code to the ‘On Click’ event to the PicPath text box, so in the same way as you did above with the ‘On Double Click’ event, select the box, open the properties menu select event, etc, until you are in the VBA editor with the ‘On Click’ event for the text box in the centre of the screen.

You will now need to paste the following short piece of code into the ‘On Click’ event

If Len(Me![PicPath]) <> 0 Then
Me![ImgControl].Picture = Me![PicPath]
End If

This piece of code checks for an entry in the PicPath text box, and if it’s not blank, uses the path name to set the picture for the image control.

Save your work, and try out your additions, now if you have a blank thumbnail, and click once on the PicPath text box, the thumbnail will be displayed.

Opening the Picture in another program

All that remains to be added is some code to open the picture in your PC’s default image handler if required, to achieve this we will add some code to the ‘On Double Click’ event, so as previously, select the thumbnail object, and going through the menus get into the VBA editor with the ‘Double Click’ event open, and paste in the following code.

Dim strPicture As String
Dim strViewer As String
strPicture = Me![ImgControl].Picture
Shell Chr(34) & Environ(“comspec”) & Chr(34) & ” /c ” & “””” & strPicture & “”””

This will take the filename used in the thumbnail and pass it to the shell command so the picture will open in the default handler.

Now that you have done all this you will have the basis for handling pictures in your database, and with a little work you will be able to use this technique in any database you write.

WARNING

This code contains very little validation or error trapping, therefore it’s possible to do some strange things such as select a non-image file and then try to display that in your thumbnail viewer etc, this will obviously cause problems, and Chimpytech.com cannot take responsibility for any loss of data etc, caused by the use of this code. If you need to validate things then you have to do that yourself to suit your situation.