CODEWITHSUNDEEP
sqlvbaformsms-accessupsert
Ronan Garrison
Ronan Garrison

Reputation: 99

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

Upvotes: 0

Views: 477

Answers (1)

Lee Mac
Lee Mac

Reputation: 16025

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.

Upvotes: 2

Related Questions