Scott:
Storing images as objects in an Access database is not very efficient. A
better approach is to store the paths to the image files in a text field in a
table and load them into an Image control on a form and/or report at runtime.
The following is my standard method for doing this, and includes the
relevant code for a form's and report's modules. It also includes the code
for a few modules you'd need to paste into your database. One of these opens
a common dialog to allow you to browse to the image file and insert its path
into the text field.
And be sure to back up your database to a safe medium very regularly!
Good luck,
Ken Sheridan
Stafford, England
''''module starts''''
Option Compare Database
Option Explicit
Private Sub cmdAddImage_Click()
On Error GoTo Err_Handler
Dim OpenDlg As New BrowseForFileClass
Dim strPath As String
Dim strAdditionalTypes As String, strFileList As String
' grab position of cursor
GrabCursor
strFileList = "*.bmp; *.jpg"
strAdditionalTypes = "Image Files (" & strFileList & ") |" & strFileList
' force form to Dirty
Me.ImageTitle = Me.ImageTitle
' open common 'file open' dialogue and get path to selected file
OpenDlg.DialogTitle = "Select Image File"
OpenDlg.AdditionalTypes = strAdditionalTypes
strPath = OpenDlg.GetFileSpec
Set OpenDlg = Nothing
' if file selected then set Picture property of Image control
' to path to file and enable ImageTitle control
If Len(strPath) > 0 Then
Me.ImagePath = strPath
Me.Image1.Picture = strPath
Me.Image1.Visible = True
End If
Exit_here:
' reset cursor position
ReturnCursor
Exit Sub
Err_Handler:
Select Case Err.Number
Case 2001
Resume
Case Else
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_here
End Select
End Sub
Private Sub cmdDeleteImage_Click()
Me.ImagePath = Null
Me.Image1.Visible = False
End Sub
Private Sub cmdReport_Click()
On Error Resume Next
DoCmd.OpenReport "rptImages", acViewPreview
End Sub
Private Sub Form_Current()
GrabCursor
If Not IsNull(Me.ImagePath) Then
Me.Image1.Visible = True
Me.Image1.Picture = Me.ImagePath
Else
Me.Image1.Visible = False
End If
ReturnCursor
End Sub
''''module ends'''''
The form has the following controls:
1. ImagePath Text box bound to the ImagePath field in the table. The
Visible property of this can be set to True (yes in the properties sheet) to
hide it if you wish.
2. Image1 Image control. When this is added to the form in design
view its Picture property's value should be deleted in the control's
properties sheet as this is set in code at runtime.
3. cmdAddImage Command button to add or change image attached to record.
4. cmdDeleteImage Command button to de-attach image from current record.
5. cmdReport Command button to open report.
The report also includes an image control, Image1, in its detail section.
The report's module is:
''''module starts''''
Option Compare Database
Option Explicit
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Not IsNull(Me.ImagePath) Then
Me.Image1.Picture = Me.ImagePath
Me.Image1.Visible = True
Else
Me.Image1.Visible = False
End If
End Sub
Private Sub Report_Close()
DoCmd.Restore
End Sub
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data to report", vbInformation, "Images"
Cancel = True
End Sub
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
End Sub
''''module ends''''
The code also draws upon the following modules which should be pasted into
your database, the first, freely distributed by Bill Wilson and slightly
amended by me, to browse to an image file, the second to move the cursor back
to its original position as it otherwise changes position when an image is
loaded:
''''module starts''''
Option Compare Database
Option Explicit
' Note: This module is a modified copy of part of the modRefreshLinks
' module in the Solutions database that is supplied with Access.
'
' That module in the Solutions database contains a lot of descriptive
' comments about the OPENFILENAME data structure that is used below, and
' it contains definitions for a list of constants that can be used in the
' Flags field in the OPENFILENAME data structure.
'
' This version was created by Bill Wilson in January 1999.
' E-mail: (e-mail address removed)
' Modified by Ken Sheridan, May 1999 to allow multiple 'additional types'
'
' The purpose of this class is to activate a dialog box that the User will
' use to pick out a particular file. The VBA code that uses this class can
' either use it to open a file or to just save the complete path and filename
' for a file which will be used at some future time.
'
' NB The dialog does not actually open the file. It only returns the path
' to the file for use in code (comment added by KWS).
'
' There are default values for the dialog box title and the list of file
types
' in the 'file filter' section of the dialog box. The calling VBA code can
' use the following Properties and Methods of this class.
'
' Properties:
' DialogTitle -- the text that is displayed as the title of the
' dialog box. The default is "Browse For a File".
' AdditionalTypes -- one or more additional file types to be added
as
' one item in the dialog box's file filter list,
' formatted like this sample:
' "My Files (*.mf1;*.mf2) | *.mf1;*.mf2 |
Your Files (*.yf1;*.yf2) *.yf1;*.yf2"
' The following file types are in the built-in
list:
' "All Files (*.*)"
' "Text Files (*.txt;*.prn;*.csv)"
' "Word Documents (*.doc)"
' "Word Templates (*.dot)"
' "Rich Text Files (*.rtf)"
' "Excel Files (*.xls)"
' "Databases (*.mdb)"
' "HTML Documents (*.html;*.htm)"
' DefaultType -- the item in the dialog's file filter list that
will be
' active when the dialog box is activated. If the
' AdditionalTypes property is not used, the default
' is "All files (*.*)". If the AdditionalTypes
property
' is used, this property cannot be used and the
file type
' specified in the AdditionalTypes property will be
active
' when the dialog box is activated. To set this
property,
' specify a string that will match with the desired
type,
' such as "*.doc" or "HTML".
' InitialFile -- the file name that is to be displayed in the File
Name
' field in the dialog box when it is activated. The
' default is to leave the File Name field blank.
' InitialDir -- the directory/folder which should be active when the
' dialog box is activated. The default is the current
' directory.
'
' Methods:
' GetFileSpec() -- this function activates the dialog box and then
returns
' the full path and filename of the file that the
User
' has selected. If the User clicks Cancel, a zero
' length string is returned.
'
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As Long
nMaxCustrFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustrData As Long
lpfnHook As Long
lpTemplateName As Long
End Type
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Boolean
Private strDialogTitle As String
Private intDefaultType As Integer
Private strNewTypes As String
Private strInitialFile As String
Private strInitialDir As String
Private strFilter As String
Private strFltrLst As String
Private strFltrCnt As String
''''module ends'''''
''''module starts'''''
Option Compare Database
Option Explicit
Type POINTAPI
X As Long
Y As Long
End Type
Declare Function GetCursorPos Lib "User32" (lpPoint As POINTAPI) As Long
Declare Function SetCursorPos Lib "User32" (ByVal X As Long, ByVal Y As
Long) As Long
Public lngCursorX As Long, lngCursorY As Long
Public Sub GrabCursor()
Dim dl As Long
Dim pt As POINTAPI
dl = GetCursorPos(pt)
lngCursorX = pt.X
lngCursorY = pt.Y
End Sub
Public Sub ReturnCursor()
SetCursorPos lngCursorX, lngCursorY
End Sub
''''module ends'''''<<
