How can I fix my syntax error 3134 in this code?

Question

Private Sub Form_Close()

    Dim sSQL, stringSQL As String
    Dim rst As DAO.Recordset

    sSQL = "SELECT BarCode, [Goods Name] FROM tblInventory WHERE BarCode='" & Me.ID & "'"
    Set rst = CurrentDb.OpenRecordset(sSQL)
    If rst.EOF Then
        stringSQL = "INSERT INTO tblInventory(BarCode,[Goods Name],Unit,[Unit Price],[Initial Stock],[Current Stock],[Exit Item]) values('" & Me.ID & "','" & Me.GoodsName & "','" & Me.Unit & "'," & Replace(Format(Me.Price, "0.00"), ",", ".") & "," & Me.Amount & "," & Me.Amount & ",0)"
        DoCmd.SetWarnings False
        DoCmd.RunSQL [stringSQL]
        DoCmd.SetWarnings True
    Else
        stringSQL = "UPDATE tblInventory SET [Current Stock]=[Current Stock]+" & Me.Amount & " WHERE BarCode='" & Me.ID & "'"
        DoCmd.SetWarnings False
        DoCmd.RunSQL (stringSQL)
        DoCmd.SetWarnings True
    End If
    rst.Close

End Sub

Answer 1

Firstly, note that this:

Dim sSQL, stringSQL As String

Results in sSQL being defined as a Variant, not a String; which, although will not cause your code to fail (since a Variant can hold data of any type), it will be memory inefficient.

Instead, you should use:

Dim sSQL As String, stringSQL As String

Or, perhaps more readable:

Dim sSQL As String
Dim stringSQL As String

---

Secondly, when invoking a function independently of any other expression, you should not surround the arguments with parentheses of any type.

In your code, on line 11 you have:

DoCmd.RunSQL [stringSQL]

And on line 16 you have:

DoCmd.RunSQL (stringSQL)

Both of these should be changed to:

DoCmd.RunSQL stringSQL

Or, just supply the SQL string directly, e.g.:

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblInventory SET [Current Stock]=[Current Stock]+" & Me.Amount & " WHERE BarCode='" & Me.ID & "'"
DoCmd.SetWarnings True

---

Also, since you are only using the recordset to test whether a record exists, you could simplify this to a DLookup call, e.g.:

DLookup("BarCode","tblInventory","BarCode=Forms![YourForm]!ID")

And test whether or not this returns Null using IsNull:

If IsNull(DLookup("BarCode", "tblInventory", "BarCode=Forms![YourForm]!ID")) Then
    ...
Else
    ...
End If

---

Finally, it is much better practice to use a parameterised query in place of concatenating values within a SQL statement.

Using parameters offers two key advantages:

• Protects against SQL injection.
• Automatically handles SQL data types.

For example, consider the following code:

Private Sub Form_Close()
    If IsNull(DLookup("BarCode", "tblInventory", "BarCode=Forms![YourForm]!ID")) Then
        With CurrentDb.CreateQueryDef _
            ( _
                "", _
                "insert into tblInventory(BarCode,[Goods Name],Unit,[Unit Price],[Initial Stock],[Current Stock],[Exit Item]) " & _
                "values(@id, @goodsname, @unit, @unitprice, @initstock, @stock, 0)" _
            )
            .Parameters(0) = Me.ID
            .Parameters(1) = Me.GoodsName
            .Parameters(2) = Me.Unit
            .Parameters(3) = Replace(Format(Me.Price, "0.00"), ",", ".")
            .Parameters(4) = Me.Amount
            .Parameters(5) = Me.Amount
            .Execute
        End With
    Else
        With CurrentDb.CreateQueryDef _
            ( _
                "", _
                "update tblInventory set [Current Stock]=[Current Stock]+@amount where BarCode=@id" _
            )
            .Parameters(0) = Me.Amount
            .Parameters(1) = Me.ID
            .Execute
        End With
    End If
End Sub

Answer 2

Try manually to run the SQL with some values.

You probably need to use either parameters or to concatenate the variables properly, for example using my function CSql.