0

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
braX
  • 11,506
  • 5
  • 20
  • 33
Yi Zong Kuang
  • 544
  • 1
  • 6
  • 17
  • Is your form bound or unbound? PS I'd stay away from attachment fields. Use hyperlinks instead. – Rene Aug 20 '19 at 04:48
  • 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 Aug 20 '19 at 05:32
  • 1
    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 Aug 20 '19 at 08:23
  • @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 Aug 22 '19 at 01:51
  • @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 Aug 22 '19 at 01:56
  • @ErikA Thank you, I will try that alternative once I get the chance – Yi Zong Kuang Aug 22 '19 at 01:58
  • I just did a test with entering an item into attachment control on form. When I pressed ESC the attachment field/control input was canceled as well as other inputs. If you want an "Abort" button on form for user, then code could run Me.Undo to cancel the new record inputs, including the attachment (also tested and confirmed). This tells me attachment entry is NOT immediately committed to table. Record is committed to table when: 1) closing table/query/form or 2) move to another record or 3) run code to save. – June7 Aug 22 '19 at 03:08
  • @June7 Thank you! Now I am gonna look for a way to access the temporary files in that's attached to Attachment Control. – Yi Zong Kuang Aug 22 '19 at 16:43
  • AFAIK, there is no way to do that. Record must be committed to table before there are any attachments to access. If file was just added to attachment control then file is already in external location. Instead of attachment field, just save file path into a text field. Use Windows FileSystemObject Dialog code to browse to and select file for attachment to email object. – June7 Aug 22 '19 at 18:08
  • @June7 Thank you, I have changed my design to do exactly that – Yi Zong Kuang Aug 30 '19 at 13:55

2 Answers2

1

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
Gustav
  • 53,498
  • 7
  • 29
  • 55
0

Open the form in design mode.

Then in the Form property's > in Data Tab > set Data Entry to True.

add Event to the form 

Private Sub Form_AfterInsert()
 DoCmd.RunCommand acCmdRecordsGoToNew 
End Sub

All this action will insure that you are all the time in new record mode .

xShen
  • 452
  • 4
  • 9
  • Thanks for the input, but that part of the question, I already have the answer for. What I am more intrigue about is ways to access the Attachment Control's content. Say if a user select files in that control to be insert into the DB, and the user clicks a button on the forms that emails a report of the current values on the form. In that scenario, the contents in the Attachment Control is not yet in the DB, so I cannot access the contents through the DB. I must access it from the Attachment Control. I want to know a way to do that programmatically – Yi Zong Kuang Aug 22 '19 at 02:04
  • Items in attachment field cannot be directly added to an email. Items must be extracted to external location first then with Outlook automation attach to email object. – June7 Aug 22 '19 at 03:14
  • Yes, I am currently trying to find a way to access those temporary files in the Attachment Control, so that I may save them to an external location. Like your previous comment mentioned, the files that user adds to the Attachment Control is not immediately committed to the table, and that's the main problem I am having right now: Accessing the temporary files in the Attachment Control – Yi Zong Kuang Aug 22 '19 at 16:45