Access 2016 VBA Record .addnew saves to previous record

Question

I have been using a form to track actions for my work. The form also handles attachments. All was fine but since IT transitioned my system to Windows 10 and at the same time office 13 then 2016. The attachment function does not work correctly. When adding an attachment to a new record it completes the VBA with no error's but saves the record to the previous record. Has something changed in later versions that I should be aware of. I am using a simple enough rotine to add files:-

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2
Set db = CurrentDb
Set rsParent = Me.Recordset
rsParent.OpenRecordset
rsParent.Edit

Set rsChild = rsParent.Fields("Attachments").Value

rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile FilePath
rsChild.Update
rsParent.Update

I forgot to add the dim's to my post;Dim db As DAO.Database Dim rsParent As DAO.Recordset2 Dim rsChild As DAO.Recordset2 — NsC0001
Why are you opening a recordset for the parent and then using .edit - You don't appear to be doing anything with it, no values are applied or changed. — Minty
I removed the openrecordset on the parent but its still not working. I can only add attachments if the record is saved without attachment and i move to a different record and then back to the record i want to add the attachment to. — NsC0001
I made a copy of the db and stripped out all data and used the entry form to again add records. I completed record one with attachment as this worked fine I added a second record and again an attachment but the same happened the attachment from record two was saved to record one. — NsC0001
It may well be a issue with Access 2016. I still develop on 2010, so can't verify if it's a problem. Why not simply add the record get the new ID and then add the attachment to that ID ? — Minty

NsC0001 NsC0001 · Accepted Answer · 2017-03-24T10:25:07

Ok found a fix. After trying all sorts of methods to have attachments save to the current record with no result. I had to force the attachment to the current recordset using the current ID.:-

Set db = CurrentDb
strSQL = "SELECT * FROM ATS WHERE ID=" & ID
Set rsParent = db.OpenRecordset(strSQL)
Set rsChild = rsParent.Fields("Attachments").Value
rsParent.Edit
rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile FilePath
rsChild.Update
rsParent.Update

Access 2016 VBA Record .addnew saves to previous record

1 Answers