How to add attachments to via a button that opens a browse dialog to select file from

[aspen]

HiIts me thanks for trying to help. Can you please test modifying that file picker code in the db to load multiple files. I have tried different ways. to be honest my vba knowledge is nill. But I get done by searching net. This seems a little more complicated so please help. hope you understand me. Now with the code in the sample database of yours I am able to browse and load a single file in to the table and get folder and file path in to two fields. So Its very good. But If you can help me modify the code to multisilect it would be wonder ful
thank you

[ItsMe]

When I get a chance to I will take a look at it. Right now I am not on a machine with Access. I am a control freak so I do not usually allow users to do things like select where a file may come from or where a file will be stored. So I will have to take a look and see.

[aspen]

I am creating this for personal use. I appreciate your intrest in this thread. It would be very helpful if you can come up with a solution. Thank you

[ItsMe]

The following code is adjusted to select multiple files.

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True   'Adjust the File Picker to allow multiple files to be selected
    .Title = "Locate a file to attach"
    .ButtonName = "Choose"
    .Filters.Clear
    .Filters.Add "All Files", "*.*"
    .InitialFileName = "C:\"
    .InitialView = msoFileDialogViewThumbnail
    
        If .Show = 0 Then
        Exit Sub
        End If
    
        'We will look for multiple files selected by the user
        Dim varFile As Variant
            For Each varFile In .SelectedItems
            'You can view the debug results in the immediate window (Keyboard shortcut Ctrl+G)
            Debug.Print varFile 'You will have to nest your code block(s) here (before NEXT) to attach each file
            Next
On Error Resume Next
End With
Me.SetFocus

[aspen]

Hi ITSME I really don't know how to thank you. I never thought it would be so easy to solve this. But now it works. I tried it in your sample database. Just as you said I nested a code from another thread of this forum. Thank you very much. now just with one button its able to browse and pick a file or many files and add to the table as separate records. ands that's what I wanted. Thank you very much. here is the complete code .

 
Private Sub cmdExecute_Click()

If strPath = "" Then
MsgBox "Please pick a file to attach"
Me.cmdPicker.SetFocus
Exit Sub
End If

If Me.Dirty = True Then Me.Dirty = False 'Save the record
Dim rstCurrent As DAO.Recordset
Set rstCurrent = Me.RecordsetClone
'Make sure there is a record to attach to
If rstCurrent.RecordCount < 0 Then
MsgBox "Please add a new record by typing text into the field provided."
rstCurrent.Close
Set rstCurrent = Nothing
Exit Sub
End If

rstCurrent.FindFirst "IndexPK = " & Me.IndexPK
rstCurrent.Edit
Call AddAttachment(rstCurrent, "FileAttach", strPath)
rstCurrent.Update
rstCurrent.Close
Set rstCurrent = Nothing
MsgBox "The folowing path was used... " & vbCrLf & strPath

End Sub
 
Private Sub cmdPicker_Click()
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True   'Adjust the File Picker to allow multiple files to be selected
    .Title = "Locate a file to attach"
    .ButtonName = "Choose"
    .Filters.Clear
    .Filters.Add "All Files", "*.*"
    .InitialFileName = "C:\"
    .InitialView = msoFileDialogViewThumbnail
    
        If .Show = 0 Then
        Exit Sub
        End If
    
        'We will look for multiple files selected by the user
        Dim varFile As Variant
            For Each varFile In .SelectedItems
            'You can view the debug results in the immediate window (Keyboard shortcut Ctrl+G)
            Debug.Print varFile 'You will have to nest your code block(s) here (before NEXT) to attach each file
            '-----------------------------------------------------------------------
            Dim rst As DAO.Recordset
            Const strTable = "tblsample"
            Const strField = "FileAttach"
            Set rst = CurrentDb.OpenRecordset(strTable)
            rst.AddNew
            'rst!Last = Me.IndexPK '
            AddAttachment rst, strField, varFile
            rst.Update
            rst.MoveLast
            rst.Close
            '----------------------------------------------------------------------------
            Next
On Error Resume Next
End With
Me.SetFocus

[ItsMe]

Glad to hear. I glanced at your code and noticed that you can place the following outside of your loop

            Dim rst As DAO.Recordset
            Const strTable = "tblsample"
            Const strField = "FileAttach"
            Set rst = CurrentDb.OpenRecordset(strTable)

So maybe

        'We will look for multiple files selected by the user
        Dim varFile As Variant
            Dim rst As DAO.Recordset
            Const strTable = "tblsample"
            Const strField = "FileAttach"
            Set rst = CurrentDb.OpenRecordset(strTable)

            For Each varFile In .SelectedItems
            'You can view the debug results in the immediate window (Keyboard shortcut Ctrl+G)
            Debug.Print varFile 'You will have to nest your code block(s) here (before NEXT) to attach each file
            '-----------------------------------------------------------------------
            rst.AddNew
            'rst!Last = Me.IndexPK '
            AddAttachment rst, strField, varFile
            rst.Update
            rst.MoveLast
            '----------------------------------------------------------------------------
            Next

'Now we can clean up the DAO object
rst.Close
set rst = nothing

Also, what you are doing here is adding a new record in the table for each file. This is fine but I thought you were trying to add multiple files to a single attachment field.

[aspen]

' -------------------------------------------------------------------------
' Sub/Func : AddAttachment
' Purpose  : Saves the attachments at the current row of the open Recordset
' Arguments: rstCurrent - The recordset open at the current row to save
'          : strFieldName - The name of the attachment field
'          : strFilePath - The full path to the file to attach
' Comments : User must call .AddNew or .Edit on the incoming Recordset
'          : and then Recordset.Update when this returns to commit changes
' -------------------------------------------------------------------------
Sub AddAttachment(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String, ByVal strFilePath As String)
    Const CALLER = "AddAttachment"
    On Error GoTo AddAttachment_ErrorHandler
    Dim rstChild  As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    If Dir(strFilePath) = "" Then ' the specified file does not exist!
        MsgBox "The specified input file does not exist: " & vbCrLf & strFilePath, vbCritical, "File not found"
        Exit Sub
    End If
    Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying Recordset.
    rstChild.AddNew ' add a new row to the child Recordset
     
    Set fldAttach = rstChild.Fields(m_strFieldFileData) ' set the DAO.Field2 object to the field that holds the binary data.
    fldAttach.LoadFromFile strFilePath ' store the file's contents in the new row.
    
    rstChild.Update ' commit the new row.
    '=====================================================
            Me.FPathName = strFilePath '?????????????????????????????????????????????????????????
            '=============================================
    rstChild.close ' close the child Recordset.
    Exit Sub
AddAttachment_ErrorHandler:
    'Check for Run-time error '3820': (occurs if the file with the same name is already attached)
    'You cannot enter that value because it duplicates an existing value in the multi-valued lookup or attachment field.
    'Multi-valued lookup or attachment fields cannot contain duplicate values.
    Debug.Print "Error # " & Err.Number & " in " & CALLER & " : " & Err.Description
    If Err.Number <> 3820 Then
        MsgBox Err.Description, VbMsgBoxStyle.vbCritical, "Error # " & Err.Number & " in " & CALLER
        Debug.Assert False ' always stop here when debugging
    Else
        MsgBox "File of same name already attached", VbMsgBoxStyle.vbCritical, "Cannot attach file"
    End If
    Exit Sub
End Sub 'AddAttachment

Thanks It works graet now. I did a lot of search and didn't find a thread with a simple solution like yours. Thank you very much for this. This forum is great. Thanks for you all. This thread is solved. have nice day. .Hi Its me I need a bit of help on how to get the full file path names with the added attachments. Cause I want to Play the videos on a form.
I Think this question is better asked with in this thread, cause its a related issue. When I add a line to the Function on top of the window I get the attachments attached plus a blank record with the full path for one rec. Can you please help me get the full file path for the attachments.

Thank you

[aspen]

I got this working. By inserting the bolded part below in your code:

...
rst.AddNew
'rst!Last = Me.IndexPK '
AddAttachment rst, strField, varFile
rst!FolderFileName = varFile
rst.Update
rst.MoveLast
...