MS Access: Add attachment to record from Form field using VBA-CodePudding

I have a bounded form with property 'Data Entry = Yes' which has a multiple textboxes, and one field of type Attachment.

I have a "Save" button that prompts confirmation to save the record.

On the form I placed a checkbox labelled 'Includes training'.

If that checkbox is checked, I want to add to the same table, an additional record (other than the one added through the bounded form) with exactly the same information as the record inserted in the bounded form, except for one text field which will be different and defined in the VBA code.

I tried a CurrentDB.Execute SQL query but it does not work with the Attachment field type.

The solution proposed in the documentation doesn't apply to this case since I want to take the Attachment contained in the bounded Attachment field on the form and not from a path on disk.

I think something like the following could work, but when I test it, it saves the record from the bounded form, but not the additional one I want to add with the VBA code, AND it ends with the error:

424 Object Required

The VBA code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctl As Control
    On Error GoTo Err_BeforeUpdate

    If Me.Dirty Then
      If MsgBox("Do you want to save the new record?", vbYesNo   vbQuestion, _
              "Save Record") = vbNo Then
         Me.Undo
         MsgBox "Changes discarded."
      Else
        If Me.checkbox.Value = True Then
        
            Set rsTable = db.OpenRecordset("Table")
            
            With rsTable
            .AddNew
            !TextField1 = Me.TextField1.Value
            !TextField2 = "My Own Text Field"
            !AttachmentField = Me.AttachmentField.Value 
            .Update
            .Bookmark = .LastModified
            End With

            MsgBox "Record was correctly saved."
        End If
    End If
    End If
End Sub

Thanks a lot for any help.

EDIT:

So clearly this is not the right way to save a new record, but I need this record to be save AT THE SAME TIME (or just after) the one on the bounded form. I DON'T want the user to fill in another bounded form.

The reason for this is because what I am entering are language certification records, and there are certifications that are broader and include two levels. Hence the checkbox signals "do you want to include the previous level as well?", if it is checked, then the PDF certificate uploaded will be valid both for level 1 and level 2. All information will be the same except for the level name of the record.

I need these two to be separate records because I can also have them singularly and later I check conditions based on these individual language levels.

CodePudding user response：

To use an attachment field you to use the special methods LoadFromFile to put an attachment in the DB and SaveFromFile to get the attachment back out of the DB. It looks like you attempted to assign a string to an attachment field.

I don't see where db is defined. Did you mean CurrentDb ?

The other problem I see is that you are using bang notation in a With block. Bang notation is a string lookup. If you want to call the item by name you would do so like this:

With CurrentDb.OpenRecordset("Table")
    .AddNew
    .Fields.Item("TextField1").Value = Me.TextField1.Value
    .Fields.Item("TextField2").Value = "My Own Text Field"
    .Fields.Item("AttachmentField").LoadFromFile Me.AttachmentField.FileName
    .Update
    .Bookmark = .LastModified
    .Close
End With

The name of those Items must be correct.

CodePudding user response：

I eventually solved this by creating a separate bounded from that pops up when I hit "Save" on the original bounded form if the checkbox is checked. There the user has the choice on whether to upload the same document as the original bounded form, or a different one.