Run-time error '3134' Syntax error in INSERT INTO statement

Hello experts,

I have been racking my brains trying to find where the error is and why the data will not insert into the Temporary Table. My code is attached. Any suggestions please?

Thanks
John

Private Sub Form_Dirty(Cancel As Integer)

'    Me.UserName = Forms!F_Startup![UserName]
    
    Dim strSQL As String
    Dim lngPtID As Long
    Dim strURN As String
    Dim strSurname As String
    Dim strGivenNames As String
    Dim varDOB As Variant
    Dim strSex As String
    Dim strAddLine1 As String
    Dim strAddLine2 As String
    Dim strSuburb As String
    Dim strPC As String
    Dim strIndigStatus As String
    Dim lngIndigStatusID As Long
    Dim strCountryOfBirth As String
    Dim lngCountryID As Long
    Dim strMedicareNo As String
    Dim strGuardSurname As String
    Dim strGuardGivenNames As String
    Dim strRelationship As String
    Dim strPhone As String
    Dim strWork As String
    Dim strMobile As String
    Dim strAltContact As String
    Dim strAltAdd1 As String
    Dim strAltAdd2 As String
    Dim strAltSuburb As String
    Dim strAltPC As String
    Dim strAltPhone As String
    Dim strAltMobile As String
    Dim strGP As String
    Dim strGPAdd As String
    Dim strGPAdd2 As String
    Dim strGPSuburb As String
    Dim strGPPC As String
    Dim strGPPhone As String
    Dim strGPFax As String
    Dim strGPEmail As String
    Dim varDOD As Variant

    lngPtID = IIf(IsNull(Me.Patient_ID), Null, Me.Patient_ID)                               'Number field
    strURN = IIf(IsNull(Me.UR_Number), "", Me.UR_Number)                                    'Text field
    strSurname = IIf(IsNull(Me.Surname), "", Me.Surname)                                    'Text field
    strGivenNames = IIf(IsNull(Me.Given_Names), "", Me.Given_Names)                         'Text field
    varDOB = IIf(IsNull(Me.DOB), Null, Me.DOB)                                              'Date/Time field
    strSex = IIf(IsNull(Me.Sex), "", Me.Sex)                                                'Text field
    strAddLine1 = IIf(IsNull(Me.Address_Line_1), "", Me.Address_Line_1)                     'Text field
    strAddLine2 = IIf(IsNull(Me.Address_Line_2), "", Me.Address_Line_2)                     'Text field
    strSuburb = IIf(IsNull(Me.Suburb), "", Me.Suburb)                                       'Text field
    strPC = IIf(IsNull(Me.Postcode), "", Me.Postcode)                                       'Text field
    strIndigStatus = IIf(IsNull(Me.Indigenous_Status), "", Me.Indigenous_Status)            'Text field
    lngIndigStatusID = IIf(IsNull(Me.Indigenous_Status_ID), 0, Me.Indigenous_Status_ID)     'Number field
    strCountryOfBirth = IIf(IsNull(Me.Country_of_Birth), "", Me.Country_of_Birth)           'Text field
    lngCountryID = IIf(IsNull(Me.CountryID), 0, Me.CountryID)                               'Number field
    strMedicareNo = IIf(IsNull(Me.MedicareNumber), "", Me.MedicareNumber)                   'Text field
    strGuardSurname = IIf(IsNull(Me.Guardian_Surname), "", Me.Guardian_Surname)             'Text field
    strGuardGivenNames = IIf(IsNull(Me.Guardian_Given_Names), "", Me.Guardian_Given_Names)  'Text field
    strRelationship = IIf(IsNull(Me.Relationship), "", Me.Relationship)                     'Text field
    strPhone = IIf(IsNull(Me.Phone_Home), "", Me.Phone_Home)                                'Text field
    strWork = IIf(IsNull(Me.Phone_Work), "", Me.Phone_Work)                                 'Text field
    strMobile = IIf(IsNull(Me.Phone_Mobile), "", Me.Phone_Mobile)                           'Text field
    strAltContact = IIf(IsNull(Me.Alternative_Contact), "", Me.Alternative_Contact)         'Text field
    strAltAdd1 = IIf(IsNull(Me.Alternative_Address_Line_1), "", Me.Alternative_Address_Line_1) 'Text field
    strAltAdd2 = IIf(IsNull(Me.Alternative_Address_Line_2), "", Me.Alternative_Address_Line_2) 'Text field
    strAltSuburb = IIf(IsNull(Me.Alternative_Suburb), "", Me.Alternative_Suburb)            'Text field
    strAltPC = IIf(IsNull(Me.Alternative_Postcode), "", Me.Alternative_Postcode)            'Text field
    strAltPhone = IIf(IsNull(Me.Alt_Contact_Phone), "", Me.Alt_Contact_Phone)               'Text field
    strAltMobile = IIf(IsNull(Me.Alt_Contact_Mobile), "", Me.Alt_Contact_Mobile)            'Text field
    strGP = IIf(IsNull(Me.GP_Name), "", Me.GP_Name)                                         'Text field
    strGPAdd = IIf(IsNull(Me.GP_Address), "", Me.GP_Address)                                'Text field
    strGPAdd2 = IIf(IsNull(Me.GP_Address_Line_2), "", Me.GP_Address_Line_2)                 'Text field
    strGPSuburb = IIf(IsNull(Me.GP_Suburb), "", Me.GP_Suburb)                               'Text field
    strGPPC = IIf(IsNull(Me.GP_Postcode), "", Me.GP_Postcode)                               'Text field
    strGPPhone = IIf(IsNull(Me.GP_Phone), "", Me.GP_Phone)                                  'Text field
    strGPFax = IIf(IsNull(Me.GP_Fax), "", Me.GP_Fax)                                        'Text field
    strGPEmail = IIf(IsNull(Me.GP_Email), "", Me.GP_Email)                                  'Text field
    varDOD = IIf(IsNull(Me.Date_of_Death), Null, Me.Date_of_Death)                          'Date/Time field
    
    strSQL = "INSERT INTO TEMPChangeDemographics ([Patient ID],[UR Number],[Surname],[Given Names],[DOB],[Sex],[Address Line 1],[Address Line 2])" & _
        "([Suburb],[Postcode],[Indigenous Status],[Indigenous Status ID],[Country Of Birth],[Country Of Birth ID],[Medicare Number])" & _
        "[Guardian Surname],[Guardian Given Names],[Relationship],[Phone Home],[Phone Work],[Phone Mobile],[Alternative Contact],[Alt Address Line 1])" & _
        "([Alt Address Line 2],[Alt Suburb],[Alt Postcode],[Alt Contact Phone],[Alt Contact Mobile],[GP Name],[GP Address],[GP Address Line 2])" & _
        "([GP Suburb],[GP Postcode],[GP Phone],[GP Fax],[GP Email],[Date of Death])" & _
        "VALUES(" & lngPtID & ",'" & strURN & " ','" & strSurname & " ','" & strGivenNames & " ',#" & Format(varDOB, "dd/mm/yyyy") & "#,'" & strSex & " ')" & _
        "('" & strAddLine1 & "','" & strAddLine2 & "','" & strSuburb & "','" & strPC & "','" & strIndigStatus & "'," & lngIndigStatusID & ")" & _
        "('" & strCountryOfBirth & "'," & lngCountryID & ",'" & strMedicareNo & "','" & strGuardSurname & "','" & strGuardGivenNames & "')" & _
        "('" & strRelationship & "','" & strPhone & "','" & strWork & "','" & strMobile & "','" & strAltContact & "','" & strAltAdd1 & "')" & _
        "('" & strAltAdd2 & "','" & strAltSuburb & "','" & strAltPC & "','" & strAltPhone & "','" & strAltMobile & "','" & strGP & "')" & _
        "('" & strGPAdd & "','" & strGPAdd2 & "','" & strGPSuburb & "','" & strGPPC & "','" & strGPPhone & "','" & strGPFax & "','" & strGPEmail & "')" & _
        "(#" & Format(varDOD, "dd/mm/yyyy") & "#)"

    CurrentDb.Execute strSQL, dbFailOnError
           
End Sub

Select allOpen in new window