Display attachment for specific record Microsoft Access

Question

I've tried to consult Google and my Access book for this, but with no luck.

Here comes the problem:

If you create a table in Access that looks like this

The attachment field is called MyImage and contains an image for Bar but not for Foo.

I created a form with an attachment control in order to easily add, remove and display images for different users.

What I want to do is to be able to pick a user in my combo box, and display the image for that user in the attachment control.

How can I do that?

I created the attachment control by going into design view and click Add existing fields. Then I dragged the MyImage-field to the form. I noticed that if Foo has an image, I'll show up in the attachment control. So the control only seems to look at Foo. I thought that there was a property in the Property sheet that I could use in my VBA code to change which record the attachment control is "looking at", but I couldn't find anything. Is there such a property, and if so, what is it called?

Answer 1

Solved in another way. Used FileDialog to open a dialog box where the user can pick a file.

The file is read into the database and displayed in an ImageBox. The only problem I have now is how to retrieve the attachment from the database and display it.

  Private Sub Command1_Click()
       ' Note that you need to add a reference to Microsoft Office
       ' 15.0 Object Library using Tools -> References... for the
       ' file picker to work
       With Application.FileDialog(msoFileDialogFilePicker)
          ' Prevent multiple selections
          .AllowMultiSelect = False
          ' Set the caption of the dialog box
          .Title = "Please pick a signature"
          ' Add filters for common image formats
          .Filters.Clear
          .Filters.Add "Joint Photographic Experts Group (JPG)", "*.JPG"
          .Filters.Add "Joint Photographic Experts Group (JPEG)", "*.JPEG"
          .Filters.Add "Portable Network Graphics", "*.PNG"
          .Filters.Add "Bitmap", "*.BMP"
          If .Show = True Then ' File selected
             For Each imagePath In .SelectedItems
                ' Instantiate the parent recordset
                Set rsImage = CurrentDb.OpenRecordset("Image")
                ' Step to record (We know record exist, else add rsImage.EOF)
                While rsImage.Fields("ID") <> 3 ' Or whatever
                    rsImage.MoveNext
                Wend
                ' Instantiate the child recordset
                Set rsPictures = rsImage.Fields("MyImage").Value
                ' Clear attachments (we only want one attachment at a time)
                Do While Not rsPictures.EOF
                    rsPictures.Delete
                    rsPictures.MoveNext
                Loop
                ' Activate edit mode
                rsImage.Edit
                ' Add the attachment selected by the user
                rsPictures.AddNew
                rsPictures.Fields("FileData").LoadFromFile imagePath
                rsPictures.Update
                ' Update the parent record
                rsImage.Update
                ' Set the content of the ImageBox
                Me.ImageBox.Picture = imagePath
             Next
          Else ' User clicked "Cancel"

          End If
       End With
    End Sub

Answer 2

how to retrieve attachment field value into attachment control in ms access programatically

I have a table name EmployeeeInfo which two Field "EmployeeId", "Attach"

#Declare a sub routine 

Private Sub ShowPic(id as integer)
 Me.RecordSource = "SELECT * FROM EMPLOYEEINFO WHERE EMPLOYEEID=" & id
 Me.atchmntControl.ControlSource = "attach"
End Sub

#Call the method