Add a new record with attachment using VBA

[joycesolomon]

Hi,

I have created a table that has a attachment field, and has a form to create a new record, at the submit button, i need the values to be updated to the table.

Here is the code for the "add record" that i created..... I added this line

MySet!trAttach = Attachment

in the code for Attachment and get an error "type mismatch". Please help

Dim Mydb As Database, MySet As Recordset, strSQL As String
        strSQL = "SELECT Max([tbl Historical TRs].trOTRNumber) AS MaxOftrOTRNumber FROM [tbl Historical TRs] WHERE (((Format([trYear],""yyyy""))=Format(Date(),""yyyy"")));"
        Set Mydb = DBEngine(0)(0)
        Set MySet = Mydb.OpenRecordset(strSQL, DB_OPEN_DYNASET)
 
        If IsNull(MySet!MaxOftrOTRNumber) Then
             dummy = 0
        Else
             dummy = MySet!MaxOftrOTRNumber
        End If
 
        strSQL = "SELECT Max([tbl Working TRs].trOTRNumber) AS MaxOftrOTRNumber FROM [tbl Working TRs] WHERE (((Format([trYear],""yyyy""))=Format(Date(),""yyyy"")));"
        Set Mydb = DBEngine(0)(0)
        Set MySet = Mydb.OpenRecordset(strSQL, DB_OPEN_DYNASET)
 
        If Not IsNull(MySet!MaxOftrOTRNumber) Then
             If MySet!MaxOftrOTRNumber > dummy Then dummy = MySet!MaxOftrOTRNumber
        End If
 
        dummy = dummy + 1
        MySet.Close
 
 
        Set Mydb = DBEngine(0)(0)
        Set MySet = Mydb.OpenRecordset("tbl Working TRs", DB_OPEN_DYNASET)
        MySet.AddNew
        MySet!trYear = Date
        MySet!trStation = "NA"
        MySet!trOTRNumber = dummy
        MySet!trGroup = Combo608
        MySet!trSubGroup = Combo589
        MySet!trRefDoc1 = Combo610
        MySet!trRefDoc2 = "SAR"
        MySet!trRefDoc3 = Combo642
        MySet!trRef1Number = Text611
        MySet!trRef2Number = "Living ASER"
        MySet!trRef3Number = Combo639
        MySet!trRequest = Text625
        MySet!trPreparedby = Text627
        MySet!trIssueDate = Text628
        MySet!trProposedAction = "This is an item for the Living ASER. No actions required."
        MySet!trClosureName = Text627
        MySet!trClosureDate = Text628
        MySet!trActionsComplete = True
        MySet!trTargetPhoneCall = True
        MySet!trFinalDisposition = "This is an item for the Living ASER. No actions required."
        '  If Not Me.Combo631 = "" Then MySet!trCogPerson = Combo631
        MySet!trInvestDueDate = Text628
        MySet!trSuggestedDueDate = Text628
        MySet!trSubmitterDept = "Training"
        MySet!trAttach = Attachment
 
        MySet.Update
 
        TextNote = "Your input has been added to the TMS Database and assigned #"
        TextNote = TextNote & Format(Date, "yy") & "-" & Format(dummy, "0000")
        TextNote = TextNote & "    Thanks for taking the time to document training effectiveness.   "
        MsgBox TextNote, 0, "New LASER Item Submitted"
        MySet.Close
 
        Me.Text607 = dummy
        DoCmd.ShowAllRecords
 
   End If
End If

[jzwp11]

I have not worked with the attachment fields yet, but I did find this site that may help you out.