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

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

    Hi,



    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.AddNew
    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

    ClientActivities.Update
    ClientActivities.Close
    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,
    Tess

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Review https://sourcedaddy.com/ms-access/wo...nt-fields.html

    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: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

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