Access has traditionally not been a good environment for storing media, images and files due to database bloating. Because of that many people prefer to store images in the file system and link to those files from Access. The approach shown below is just one of many methods, and has these features:
- Files are stored in a predefined directory
- The images are located / stored using a button on a subform. They are displayed in an unbound image frame on the main form.
- The same button is used to browse for and display a new image, and to display an existing image.
- Images are located on disk using the FileDialog object. Once selected you can open, save, rename or otherwise modify the file.
You will need to set up the following to use this code:
- A command button on a subform, with the code shown in Listing 1.
- A text field in the subform, named Photo
- An unbound image frame in the main form, called imgDisplay. When you set up this control you will be prompted to browse for an image. Whatever you select will become the default image.
- You will also need to save Listing 2 in the code module of the subform.
Listing 1
TryAgain: If IsNull(Me.Photo) Or Len(Me.Photo) = 0 Then 'no image; load an image before showing CopyImage GoTo TryAgain Else Me.Parent!imgDisplay.Picture = Me.Photo End If
The If statement checks first to see whether the Photo field is empty. If it is the CopyImage function launches, prompting the user to locate an image for the record. Once the image is copied and the link written, the code returns to the top of the routine and loads the link into the image container.
Listing 2
Private Function CopyImage() ''==================================================== ''You will need a reference to the Microsoft Office x.0 Object Library '' where x = 10,11,12 or 13 ''==================================================== Dim fd As Office.FileDialog Const SAVE_PATH = "H:\Documents\Images\" 'default folder for saving the images Dim strFname As String Dim i As Integer Dim vrtSelectedItem As Variant Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .Filters.Clear .Filters.Add "All files", "*.*" .Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 1 .AllowMultiSelect = False If .Show = -1 Then For Each vrtSelectedItem In .SelectedItems strFname = "" For i = Len(vrtSelectedItem) To 1 Step -1 If Mid(vrtSelectedItem, i, 1) = "\" Then strFname = Mid(vrtSelectedItem, i + 1) Exit For End If Next i 'copy to the destination directory FileCopy vrtSelectedItem, SAVE_PATH & strFname ‘optional: delete the original, keeping only the copy. Kill vrtselecteditem ‘write the full path to the Photo field Me.Photo = SAVE_PATH & strFname Next vrtSelectedItem 'The user pressed Cancel. Else End If End With 'Set the object variable to Nothing. Set fd = Nothing End Function