What is a programmatic method to remove attachment(s) on a Access Form's Attachment Control?

Question

I am creating a Form on Access. This Form has source controls to an underlying Access DB. This Form is ONLY INTENDED for insertion of a new record into the DB (No intention of modifying any of the DB's existing data).

In the Access DB, there is a field for Attachments. And on the Form, there is an Attachment Control that is Source Controlled to the Attachments field in the Access DB. The Attachment Control allows users to select files and attach to the form's current record.

I have tried closing the Form and re-opening, but that means the form goes through its life cycle, and does the BeforeUpdate()+AfterUpdate()! This means that the data on the Form that was just recently closed, was entered into the DB. I don't want that!

For the reason that this Form is only for creating new records into the DB, I want a button that can wipe the current Form clean, without the Form doing its BeforeUpdate()+AfterUpdate() methods, which means I can't close the Form and re-open it. I have got most of the Controls on the form figured out in terms of giving it default values. My only problem is resetting the Attachment Control.

What is a programmatic way (In VBA or some other language, DAO?) to remove entries on this Attachment Control (Not the Attachment Field in the DB, but the actual data on the Form currently being used)?

I am aware that in BeforeUpdate(), you may cancel the form's update by setting its Cancel = true in the definition of BeforeUpdate(). But there should still be a way to programmatically deal with Attachment Control (Not the attachment field, I know DAO can handle that easily), it just seem stupid not to have a way for it.

(EDIT) Addon 8/30/19: It's a better idea to store the pathname of the file in your Access DB rather than the attachment it self, and then do a file copy from one location to another (Looks at the code below as my example). And this is exactly what I did, so I don't have to deal with the attachment control. I have found this control to be too limiting in terms of the programmatic-actions that it can offer to developers.

Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
On Error GoTo DebugMsg ' DebugMsg is where I defined what happens on Error
fso.CopyFile srcPath, dstPath, False

Is your form bound or unbound? PS I'd stay away from attachment fields. Use hyperlinks instead. — Rene
I am confused. You say the form is for adding new record then you say you don't want entries saved. So it's not clear to me what the issue is. Why do controls need to be 'reset'. I agree with @Rene - usually best to leave files external and save path to text field or construct path with expression. Yes, requires VBA and DAO recordset to programmatically manage Attachment field. Attachment field is a type of multi-value field. What kind of files are in Attachment field? — June7
A usable attachment field is always bound and commits entries immediately. If you don't want them to be saved, you don't want to use an attachment field. There's no such thing as attachments inside the control but not the field. If you want to work with files without committing them to a table, use OLE object frames. — Erik A
@Rene It is bound to a Access DB. Ya, it will only be a short term solution to a problem that I have. I am planning to migrate the Access DB to SQl Server, at that time, I will store hyperlinks/pathname instead of attachments — Yi Zong Kuang
@June7 In the case that the user who is entering a bunch of data on the form, but realize he/she entered wrong information and would like to restart. It's a hassle to delete/modify manually, so a "reset" button would be nice. I am aware you could do Me.Undo to reset entire form. And that led me to think about ways to reset various parts of the form, and I just thought it's weird to not have a way to access contents stored in the Attachment Control — Yi Zong Kuang

Gustav Gustav · Accepted Answer · 2019-08-20T07:11:13

The attachment field holds a subrecordset of type Recordset2, so you must deal with it as such:

Private Sub DeleteCurrentAttachment()

    Dim Records     As DAO.Recordset
    Dim Attachments As DAO.Recordset2

    Set Records = Me.RecordsetClone
    Records.Bookmark = Me.Bookmark

    Set Attachments = Records!YourAttachmentFieldName.Value
    While Not Attachments.EOF
        Attachments.Delete
        Attachments.MoveNext
    Wend
    Attachments.Close

End Sub

What is a programmatic method to remove attachment(s) on a Access Form's Attachment Control?

2 Answers