CODEWITHSUNDEEP
vbams-access-2013
zybixx
zybixx

Reputation: 15

Which operator is incorrect and returns error '3075'?

I've encounter run time error '3075', not sure what went wrong. Please help.

Private Sub btnUpdate_Click()

If Me.cobDepartment <> "" Then


     CurrentDb.Execute "UPDATE tblSite " & _
     " SET DepartmentID= " & Me.cobDepartment & _
     " WHERE Payroll number=" & Me.Payroll_number.Value & ""
     MsgBox "Updated"

     Else
     MsgBox "Select Department"



End If

End Sub

Upvotes: 0

Views: 26

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

You have a few issues:

  1. Since cobDepartment appears to be a string (given that you have the expression Me.cobDepartment <> "" earlier in the code), the value will need to be surrounded by single or double quotes within the SQL statement, e.g.:

    "SET DepartmentID='" & Me.cobDepartment & "'"

  2. Fields containing spaces or which correspond to reserved words in MS Access will need to be enclosed within square brackets, e.g.:

    [Payroll number]

  3. Most importantly: concatenating values directly into a SQL statement exposes your code to the possibility of SQL injection, whereby a user could 'inject' additional SQL instructions into the values being concatenated.

    A far better approach would be to use parameters, e.g.:

    Private Sub btnUpdate_Click()
    If Me.cobDepartment <> "" Then
        With CurrentDb.CreateQueryDef("","update tblsite set departmentid=@did where [payroll number]=@prn")
            .Parameters("@did") = Me.cobDepartment
            .Parameters("@prn") = Me.Payroll_number
            .Execute
        End With
        MsgBox "Updated"
    Else
        MsgBox "Select Department"
    End If
End Sub

    The use of parameters in this way has the additional benefit of automatically handling the varying SQL data types within your SQL statement, avoiding the need for additional quotes or formatting of values.

    Of course, this is just one possible way to use parameters within a SQL statement in MS Access - this excellent answer describes many other methods.

Upvotes: 2

Related Questions