VBA SQL update/insert to local table using values from form

I am trying to update a row in a local table stored in access (or insert a new row if that ID doesn’t already exist) using values populated in a form by the user. I am writing it in VBA using SQL. Here is the code:

Public Function update_table()

Dim strSQL As String

strSQL = "IF NOT EXISTS (SELECT * FROM table1 WHERE (id1 = [Forms]![Study Info Cleaned]![Text156] AND id2 = [Forms]![Study Info Cleaned]![Text158]))" & _
    "INSERT INTO table1 ( id1, id2, id3, id4, id5 )" & _
    "VALUES ([Forms]![Study Info Cleaned]![Text156]," & _
        "[Forms]![Study Info Cleaned]![Text158]," & _
        "[Forms]![Study Info Cleaned]![Text160]," & _
        "[Forms]![Study Info Cleaned]![Text201]," & _
        "[Forms]![Study Info Cleaned]![Text166])" & _
"Else" & _
    "Update table1" & _
    "SET id4 = [Forms]![Study Info Cleaned]![Text201], id5 = [Forms]![Study Info Cleaned]![Text166]" & _
    "WHERE (id1 = [Forms]![Study Info Cleaned]![Text156] AND id2 = [Forms]![Study Info Cleaned]![Text158])"


DoCmd.RunSQL (stringSQL)


End Function

The code returns the following error message :

Run-time error ‘3129’, invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.

Answer

MS Access SQL does not support if statements, only iif statements.

As such, you’ll likely need to implement the logic within VBA, e.g.:

Public Function update_table()
    Dim strSQL As String

    If DCount("*","table1","id1 = [Forms]![Study Info Cleaned]![Text156] AND id2 = [Forms]![Study Info Cleaned]![Text158]") = 0 Then
        strSQL = _
        "INSERT INTO table1 ( id1, id2, id3, id4, id5 ) " & _
        "VALUES ([Forms]![Study Info Cleaned]![Text156]," & _
        "[Forms]![Study Info Cleaned]![Text158]," & _
        "[Forms]![Study Info Cleaned]![Text160]," & _
        "[Forms]![Study Info Cleaned]![Text201]," & _
        "[Forms]![Study Info Cleaned]![Text166])"
    Else
        strSQL = _
        "UPDATE table1 " & _
        "SET id4 = [Forms]![Study Info Cleaned]![Text201], id5 = [Forms]![Study Info Cleaned]![Text166] " & _
        "WHERE (id1 = [Forms]![Study Info Cleaned]![Text156] AND id2 = [Forms]![Study Info Cleaned]![Text158])"
    End If
    DoCmd.RunSQL strSQL
End Function

There are a few other issues:

• stringSQL is a typo, since you initially define your variable as strSQL.
• DoCmd.RunSQL (stringSQL) parentheses should not surround the argument since the expression is not part of another expression.