Using file names from a listbox and .LoadFromFile Method to upload attachments to a table

[PoolHallJunkie]

Good Afternoon,

I'm trying to loop through the values in a listbox (called "Attachments") and use those values to attach a file to a new record in a table. The attachments field in the table is called "Evidence". The below code will create a new record but not add the attachments.

Private Sub cmdImport_Click()
    Dim dbs As Database
    Dim rst As Recordset
    Dim aFile
    
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("VRM Evidence")

    rst.Edit
    rst.AddNew
    For Each aFile In Me.Attachments.ItemsSelected
        rst.Fields("Evidence").LoadFromFile Me.Attachments.ItemData(aFile)
    Next
    rst.Update
    
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Sub

[June7]

Attachment data type is a type of multi-value field. Adding data to a multi-value field requires opening a 'child' recordset. http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Lots of large embedded documents will use up Access 2GB file size limit fast.

[PoolHallJunkie]

Ok, I've been trying to get my head around this and it just isn't working. Below is the code I'm using:

Private Sub cmdImport_Click()
    Dim dbs As Database
    Dim rst As Recordset
    Dim arst As Recordset2
    Dim aFile
    
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("VRM Evidence")
    
    Set arst = rst.Fields(“Evidence”).Value
On Error Resume Next
For Each aFile In Me.Attachments
    With arst
        .AddNew
        .Fields(“FileData”).LoadFromFile (Me.Attachments.ItemData(aFile))
        .Update
        .Close
    End With
Next
    rst.Update
    
    Set rst = Nothing
    Set dbs = Nothing
End Sub

• I have a table called "VRM Evidence" with an attachments field called "Evidence"
• On the entry form I have a "Browse" button that has code that allows the user to search for 1 or more files and the file path(s) are populated into a list box on the form called "Attachments"
• When the Import button is clicked, it's supposed to:
  
  • create a new record on the "VRM Evidence" table
  • using the .LoadFromFile method, add the attachments based on the file paths from the listbox

This code is neither creating a new record nor adding the attachments and I can't figure out why...

[June7]

Need to set the parent recordset to edit mode:
rst.Edit

Code to loop through all items of listbox:
http://stackoverflow.com/questions/2...a-list-box-vba

Suggest you remove the On Error Resume Next line because this interferes with debugging.

Private Sub cmdImport_Click()
    Dim rst As Recordset
    Dim arst As Recordset2
    Dim i As Integer
    Set rst = CurrentDb.OpenRecordset("VRM Evidence")
    rst.Edit
    Set arst = rst.Fields("Evidence").Value
    For i = 0 To Me.Attachment.ListCount - 1
        arst.AddNew
        arst.Fields("FileData").LoadFromFile (Me.Attachment.ItemData(i))
        arst.Update
    Next i
    rst.Update
End Sub

It only saves attachment to the first record of rst.

[PoolHallJunkie]

This was very helpful thanks! I have updated the code. Now I'm getting an actual error:

Error 3265
Item not found in this collection

Private Sub cmdImport_Click()
Dim dbs As Database
Dim rst As Recordset
Dim arst As Recordset2

Dim aFile

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("VRM Evidence")

rst.Edit

Set arst = rst.Fields("Evidence").Value

On Error GoTo Proc_Error

For aFile = 0 To Me.Attachments.ListCount - 1
With arst
.AddNew
.Fields(“FileData”).LoadFromFile (Me.Attachments.ItemData(aFile))
.Update
.Close
End With
Next
rst.Update

Set rst = Nothing
Set dbs = Nothing

Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in cmdImport.Click" & vbCrLf & Err.Description
Resume Proc_Exit

End Sub

The line that is producing the error is .Fields(“FileData”).LoadFromFile (Me.Attachments.ItemData(aFile))

I also am not sure what you mean by

It only saves attachment to the first record of rst.

I thought this would create a new record and attach the file to that? If that is not the case is there a better way to do this?

[PoolHallJunkie]

Hi! I fixed the error, I wanted to post the final code for reference if others should look this up:

Private Sub cmdImport_Click()
    Dim dbs As Database
    Dim rst As Recordset2
    Dim arst As Recordset2
    Dim fld As Field2
    
    Dim aFile
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("VRM Evidence")
    Set fld = rst("Evidence")
    
    rst.Edit
        
    Set arst = fld.Value
On Error GoTo Proc_Error
For aFile = 0 To Me.Attachments.ListCount - 1
    With arst
        .AddNew
        .Fields("FileData").LoadFromFile (Me.Attachments.ItemData(aFile))
        .Update
        .Close
    End With
Next
    rst.Update
    
    Set rst = Nothing
    Set dbs = Nothing
    
Proc_Exit:
  Exit Sub
Proc_Error:
  MsgBox "Error " & Err.Number & " in cmdImport.Click" & vbCrLf & Err.Description
  Resume Proc_Exit

End Sub

You are correct though, it only attaches the document to the 1st record of the table. This is a different issue however so I will start a new thread. This particular issue is solved. Thank you very much for your help June7, you're pretty much a life saver.

[June7]

If you want a new parent record then change rst.Edit to rst.AddNew.

Then you probably need to populate other fields of the parent record as well.