Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2020

    Updating a recordset with an attachment in vba, produces error if more than one attachment


    I would appreciate some help please. I have created a form to add new records. I'm using the following code to update the recordset. It works when I have one attachment but causes an error if there's more then one. After the update, I need to set the attachment field to null to add another new record. I would be very grateful for an example of the code I should use in your response please as I am new to this.

    Also, when I click on the btnSaveNew, how do I advance the ClientActivityID to a new record please.

    Private Sub btnSaveNew_Click()
    Dim ClientActivities As DAO.Recordset

    Set ClientActivities = CurrentDb.OpenRecordset("SELECT * FROM [ClientActivities]")
    ClientActivities![ClientId] = Me.ClientId.Value
    ClientActivities![ClientActivityId] = Me.ClientActivityId.Value
    ClientActivities![CreatedDate] = Me.CreatedDate.Value
    ClientActivities![Activity] = Me.Activity.Value
    ClientActivities![Priority] = Me.Priority.Value
    ClientActivities![AssignedTo] = Me.AssignedTo.Value
    ClientActivities![AppointmentDate] = Me.AppointmentDate.Value
    ClientActivities![StartTime] = Me.StartTime.Value
    ClientActivities![EndTime] = Me.EndTime.Value
    ClientActivities![Details] = Me.Details.Value
    ClientActivities![ActivityStatus] = Me.ActivityStatus.Value

    ClientActivities![Note] = Me.Note.Value
    ClientActivities![Note] = Me.Attachment.AttachmentCount

    Set ClientActivities = Nothing

    Me.ClientId.Value = Null
    Me.CreatedDate.Value = Null
    Me.CreatedBy.Value = Null
    Me.Activity.Value = Null
    Me.Priority.Value = Null
    Me.AssignedTo.Value = Null
    Me.AppointmentDate.Value = Null
    Me.StartTime.Value = Null
    Me.EndTime.Value = Null
    Me.Details.Value = Null
    Me.ActivityStatus.Value = Null
    Me.Note.Value = Null

    End Sub

    Thank you,

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land

    Embedding files in db uses up Access 2gb size limit. So unless your db will never get very large regardless of attachments, better to store path to external file. If you need multiple files for each record then use a related dependent table.

    Why do you even need code to add a record? Why not just data entry into a bound form?
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jun 2020
    Thank you June, I got it working. I appreciate your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. attachment control bind to a recordset
    By danial.a in forum Programming
    Replies: 0
    Last Post: 07-30-2016, 01:39 PM
  2. Replies: 3
    Last Post: 10-13-2014, 05:48 PM
  3. Replies: 1
    Last Post: 09-04-2014, 11:10 AM
  4. Replies: 13
    Last Post: 12-05-2013, 06:04 PM
  5. Replies: 16
    Last Post: 04-30-2012, 07:12 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums