Adding attachments to a table

[kdbailey]

I am looking for a way to attach X amount of pictures to a record. I want to use a command button that opens an explorer to choose X amount of files. I don't want to just open a folder because that won't return the file location.



Does anybody know how to open an "attachment explorer"?

Access 2007

[ItsMe]

Here are some VBA examples

[kdbailey]

I found basically the same code while googling this issue. Every time I run the code the variable "m_strFieldFileData" is empty. How does this variable become populated?

Here is my code...

Private Sub cmdattach_Click()Dim claimnum As String, serialno As Double, rs As DAO.Recordset, db As DAO.Database


claimnum = xx
serialno = 1
filepath = SelectFile()


If filepath <> "" Then
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Picture Table")
    With rs
        .AddNew
            AddAttachment rs, "Attach", filepath
            .Fields("Claim Number") = claimnum
            .Fields("Serial Number") = serialno
        .Update
        .Close
    End With
End If


End Sub

My 2 public functions...

Public Function SelectFile() As String    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Please select file to attach"
        If .Show = True Then
            SelectFile = .SelectedItems(1)
        Else
            Exit Function
        End If
    End With
    Set fd = Nothing
End Function

Public Sub AddAttachment(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String, ByVal strFilePath As String)
 
On Error GoTo AddAttachment_Err
Dim rstChild As DAO.Recordset2
Dim fldAttach As DAO.Field2
Set rstChild = rstCurrent.Fields(strFieldName).Value
rstChild.AddNew
Set fldAttach = rstChild.Fields(m_strFieldFileData)
fldAttach.LoadFromFile strFilePath
 rstChild.Update
rstChild.Close
 Exit Sub
 
AddAttachment_Err:
' Run-time error '3820': (occurs if the file with the same name is already attached)
If Err.Number <> 3820 Then
 modUtility.ErrorMessage
Else
 VBA.Interaction.MsgBox "File already attached!", vbInformation + vbOKOnly, modConstant.AppName
End If
 Resume AddAttachment_End
 
AddAttachment_End:
End Sub

[ItsMe]

I provided a working example that you can compare to. Maybe you can step debug the example to get an illustration. That would probably be better than me trying to describe it here. Your m_strFieldFileData is not mentioned or declared anywhere in the code you provided. In order to get the whole thing to work, you need to nest two DAO recordsets, you would get the missing info from the parent recordset.

[kdbailey]

I just substituted m_strFieldFileData with "FileData" and it works for one attachment.

However, now I am looking into attaching multiple files. I am passing an array of file URL's and everything runs, but it doesn't attach the multiple files. Only one.

Public Sub AddAttachment(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String, filenms() As Variant, cnt As Integer) 
On Error GoTo AddAttachment_Err
Dim rstChild As DAO.Recordset2


Set rstChild = rstCurrent.Fields(strFieldName).Value


For n = 0 To cnt
    rstChild.AddNew
    rstChild.Fields("FileData").LoadFromFile filenms(n)
Next n


rstChild.Update
rstChild.Close
Exit Sub
 
AddAttachment_Err:
' Run-time error '3820': (occurs if the file with the same name is already attached)
If Err.Number <> 3820 Then
 modUtility.ErrorMessage
Else
 VBA.Interaction.MsgBox "File already attached!", vbInformation + vbOKOnly, modConstant.AppName
End If
 Resume AddAttachment_End
 
AddAttachment_End:
End Sub

How do I get it to add multiples?

[ItsMe]

You will need to successfully automate a single file before you can iterate and enumerate multiple files. Hardcoding a file name does not indicate to me that you have successfully automated the attach action.

[kdbailey]

How do I refer m_strFieldFileData to a file then? I could not find anything describing what it even is.

[ItsMe]

I suggest you step debug the sample I provided in post # 2. Follow the suggestions in post #4 to get your answer(s).