CODEWITHSUNDEEP
sqlvbams-access
JoeriSmits
JoeriSmits

Reputation: 254

Microsoft Access run-time error '3075' Syntax Error

I want to delete something in my database using a button with the following VBA behind it.

Unfortunately it gives me a run-time error and I don't know why. I've looked how Access give the SQL statement back and in SQL Server it gives me no errors.

VBA:

Private Sub btnDeleteTaak_Click()
      If Not IsNull(Me.lstTaakMonteur) Then
        If Not IsNull(Me.comboMonteur.Value) Then
            If Not IsNull(Me.comboOpdracht.Value) Then
                Dim DeleteSQL As String
                DeleteSQL = "DELETE OpdrachtTaak WHERE opdrachtnr = " & Me.comboOpdracht.Value & " AND taaknr = " & Me.lstTaakMonteur.Value & " AND monteurcode = '" & Me.comboMonteur.Value & "'"
                DoCmd.RunSQL (DeleteSQL)
                Me.lstTaakMonteur.Requery
            Else
                MsgBox ("Er is geen opdracht geselecteerd")
            End If
        Else
             MsgBox ("Er is geen monteur geselecteerd")
        End If
    Else
        MsgBox ("Er is geen taak geselecteerd")
    End If
End Sub

The error:

Syntax error (missing operator) in query expression 'OpdrachtTaak WHERE opdrachtnr = 1 AND taaknr = 6 AND monteurcode = 'LM1"

Table structure OpdrachtTaak:

opdrachtnr NUMERIC(5) <pk,fk2> NOT NULL
taaknr NUMERIC(3) <pk, fk3> NOT NULL
tijdsduur NUMERIC(4,1) NULL
Monteurcode VARCHAR(3) <fk1> NULL

Would be great if you can help me out.

Upvotes: 0

Views: 817

Answers (1)

Hamid Reza
Hamid Reza

Reputation: 485

Delete statements in MS Access databases is fairly different from those in SQL Server. In MS Access you must include From statement in your delete query. So your code will be:

DeleteSQL = "DELETE * FROM OpdrachtTaak WHERE opdrachtnr = " & Me.comboOpdracht.Value & " AND taaknr = " & Me.lstTaakMonteur.Value & " AND monteurcode = '" & Me.comboMonteur.Value & "'"

Upvotes: 1

Related Questions