CODEWITHSUNDEEP
ms-access
derek
derek

Reputation: 1055

vba code to insert file into table based on ID

I have developed code which inserts files into an attachment field in an access database table. The Table is called "Reports" and the attachment field is called "Field1". At the moment my code inserts files to the first record in the table "Reports", what i would like to do is add an attachment to a record based on its ID, So for example (add attachment to Field1 where the ID = some value) . Is this possible can anyone help me out with this ?

Instantiate the parent recordset.
Set rsfile = db.OpenRecordset("Reports")


 Do While Not rsfile.EOF
If rsfile.Fields("ID").Value = 1 Then
' Activate edit mode.
rsfile.Edit

' Instantiate the child recordset.
Set rsReport = rsfile.Fields("Field1").Value

'Add a new attachment.
filePath = "C:\dbPDF\sitereport.pdf"
rsReport.AddNew
rsReport.Fields("FileData").LoadFromFile (filePath)
rsReport.Update

' Update the parent record
rsfile.Update

rsfile.MoveNext
Loop

Upvotes: 1

Views: 4562

Answers (1)

www
www

Reputation: 4391

You could iterate thought reports and update if condition match, is should be similar to this:

rsfile = db.OpenRecordset("Reports")

Do While Not rsfile.EOF
 If rsfile.Fields("ID").Value = 1 Then
    'Activate edit mode.
    rsfile.Edit

    'Instantiate the child recordset.
    Set rsReport = rsfile.Fields("Field1").Value
    'Add a new attachment.
    filePath = "C:\dbPDF\sitereport.pdf"
    rsReport.AddNew
    rsReport.Fields("FileData").LoadFromFile (filePath)
    rsReport.Update 
    'Update the parent record
    rsfile.Update
 End If
'Next row
 rsfile.moveNext
Loop

Upvotes: 1

Related Questions