so i let this go for a while and i'm back at it. below is the code i've put together. i get this weird error "item is not in this collection". don't know what to do.
Code:
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.
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
Private Sub Command10_Click()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = False
' Set the title of the dialog box.
.title = "Select a File"
' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
' Loop through each file that is selected and then add it to the list box.
Dim rst As DAO.Recordset
Const strTable = "tblImages"
Const strField = "File"
Set rst = CurrentDb.OpenRecordset(strTable)
rst.AddNew
AddAttachment rst, strField, varFile
rst.Update
rst.MoveLast
rst.Close
Else
msgbox "You clicked Cancel in the file dialog box."
End If
End With
End Sub