I've kept the database as simple as possible but give this a try:
2 tables.
tblGangs
GangID (Autonumber, PK)
GangName (Text)
tblMembers
MemberID (Autonumber, PK)
GangID (Number)
Photo (Text)
MemberName (Text)
In the Relationships window join tblGangs and tblMembers. Enforce referential integrity.
Create a form based on tblGangs (one record per page). Call it frmGangs.
Create a subform based on tblMembers (multiple records per page). Call it sfmMembers.
Add a Command button to sfmMembers. Name it cmdShowImage. It needs to go in the Detail row of the subform so you get 1 button per record.
Code for the form: Note -- you will need to set a reference to the Microsoft Office x.0 Oject Library, where x depends on your version.
Code:
Private Function CopyImage()
''====================================================
''You will need a reference to the Microsoft Office x.0 Object Library
'' where x = 10,11,12 or 13
''Browse to a directory,
'Declare a variable as a FileDialog object.
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
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant
'Use a With...End With block to reference the FileDialog object.
With fd
'Change the contents of the Files of Type list.
'Empty the list by clearing the FileDialogFilters collection.
.Filters.Clear
'Add a filter that includes all files.
.Filters.Add "All files", "*.*"
'Add a filter that includes GIF and JPEG images and make it the first item in the list.
.Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 1
'only pick 1 image at a time
.AllowMultiSelect = False
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
'Step through each String in the FileDialogSelectedItems collection.
'we need the file name, split from the path, so we can save the file elsewhere
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
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
Private Sub cmdShowImage_Click()
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
End Sub
If you click the button on a blank record you will get a file dialog that lets you browse to a folder, select an image and copy it to a default folder that will hold all images referenced by the forms. It then displays the image in imgDisplay, an unbound image frame on frmGangs (see below).
Clicking the button on a record with something in the Photo field will display the image.
Finishing off the main form:
Open frmGangs in Design view. Add an unbound image frame to the form. You will be prompted for an image; load whatever you would like to be the default image on the form.
Name this frame imgDisplay.
Now, drag the icon for sfmMembers and drag it below the image frame (but still in the Detail section of the form) and let go. IMMEDIATELY go to the Properties, select the Data tab and check that both Master and Child fields have GangID entered into them. If not, do it manually: this syncs records on the subform with the main form.
Save the form and give it a spin.
Denis
