VBA Attachment: Item Not found in Collection

Question

All,

I'm trying to save a record for 1 record to a drive. I've spent about a day searching for a solution so this is a last ditch effort for some help. I am not a developer by any stretch of the imagination so please, go easy.

Code is below.

Table where record is located: tracker.

Field I am searching based on: ReqID - where ReqID = the record I am entering, find the attachment and move it to a location.

Dim db As DAO.Database
Dim rsChild As DAO.Recordset2
Dim ReqID As String

ReqID = Me.Form![Text145]
Debug.Print ReqID

Set db = CurrentDb
Set rsChild = db.OpenRecordset("Select * from tracker Where " & ReqID & " = [tracker].[ID]", dbOpenDynaset)
Debug.Print rsChild.RecordCount



   If (rsChild.EOF = False) Or (rsChild.BOF = False) Then

    While Not rsChild.EOF
rsChild("FileData").SaveToFile "C:\Users\<folder>\"
        rsChild.Delete
    Wend
    End If

Is there any error or is it simply not working? Did you try to debug (F8) and see what part is going wrong? — Techie, Apr 07 '16 at 20:48
Does the [tracker] table really contain a field named [FileData]? If not, then open the table in Design View and see what the `Attachment` field is actually called. — Gord Thompson, Apr 07 '16 at 20:50
Error I get is RunTime 3265 "Item not found in collection" - I assume it's just not finding the attachment somehow.. — jmcginley, Apr 07 '16 at 20:51
Attachment field is simply called Attachments. From what I've seen, the "FileData" needs to be there. — jmcginley, Apr 07 '16 at 20:52

score 2 · Accepted Answer · answered Apr 07 '16 at 21:05

2

You actually need to use two Recordset objects: one for the main record and another for the attachment(s) associated with that record. This is the sample code that works for me, where [tblTest] is the name of the table and [Attachments] is the name of the Attachment field:

Option Compare Database
Option Explicit

Sub SaveAllAttachments()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Dim rstMain As DAO.Recordset
    Set rstMain = cdb.OpenRecordset("SELECT Attachments FROM tblTest WHERE ID=1", dbOpenDynaset)
    rstMain.Edit
    Dim rstChild As DAO.Recordset2
    Set rstChild = rstMain.Fields("Attachments").Value
    Do Until rstChild.EOF
        Dim fileName As String
        fileName = rstChild.Fields("FileName").Value
        Debug.Print fileName
        Dim fld As DAO.Field2
        Set fld = rstChild.Fields("FileData")
        fld.SaveToFile "C:\Users\Gord\Desktop\" & fileName
        rstChild.Delete  ' remove the attachment
        rstChild.MoveNext
    Loop
    rstChild.Close
    rstMain.Update
    rstMain.Close
End Sub

answered Apr 07 '16 at 21:05

Gord Thompson

116,920
32
215
418

I'll give this a shot.. I'm buying you a beer if this works. – jmcginley Apr 07 '16 at 21:08
Getting a "Could not update; currently locked" at rstMain.edit – jmcginley Apr 07 '16 at 21:12
The record must be open somewhere. Try closing the database and re-opening it. – Gord Thompson Apr 07 '16 at 21:16
I want to store the attachments as soon as I can after entering in the Request. I'm guessing I won't be able to do this from the same form is that right? I have the ReqID being pulled off of my form, should I store that information and run this code after I've freed up the record? – jmcginley Apr 07 '16 at 21:19
I'm not clear on what your workflow is, but if the form is bound to the same record and is `.Dirty` then that might explain a locking conflict. You might try throwing in a `Me.Dirty = False` statement at the beginning to see if that helps. – Gord Thompson Apr 07 '16 at 21:26
Same Error.. I'll see if I can't put the code after the form closes and could free up the record. – jmcginley Apr 07 '16 at 21:36
You've helped me out. I should have mentioned that our backend is located on Sharepoint, I'm seeing the file is stored there which probably opens a whole new can of worms. Thanks for the help! – jmcginley Apr 07 '16 at 21:56

VBA Attachment: Item Not found in Collection

1 Answers1