programmatically deleting records

  • Thread starter Thread starter Jeff Wilkin
  • Start date Start date
J

Jeff Wilkin

using the AfterUpdate event of a checkbox control on a continuous subform,
I'm trying to delete certain records from a table which is not bound to my
form. I'm getting Runtime error 3131 stating that there is a syntax error in
the FROM clause.

--- Code ---

Private Sub chkBestellt_AfterUpdate()

Dim strSql As String
Dim dSql As String

If Me.chkBestellt = True Then

If Not IsNull(Me.Menge) Then

strSql = "Insert Into BestellDetails
(ArtikelID,Menge,VerpID,BestellungenID) " & _
"Values (" & Me.ArtikelID & ", " & Me.Menge & ", " & Me.VerpID & ",
" & Me.BestellungenID & ")"

CurrentDb.Execute strSql, dbFailOnError

End If

Else

dSql = "DELETE * FROM BestellDetails " & _
"(SELECT BestellDetails.ArtikelID, BestellDetails.BestellungenID
FROM BestellDetails " & _
"WHERE (([BestellDetails.ArtikelID]=" & Me.ArtikelID & " AND " & _
"[BestellDetails.BestellungenID]=" & Me.BestellungenID & ")));"

CurrentDb.Execute dSql, dbFailOnError

End If

End Sub

--------------
ArtikelID & BestellungenID are both long integers.

Any help is greatly appreciated.

TIA
Jeff
 
Jeff,

I think that including the SELECT clause inside the DELETE
may be tripping you up. Try changing it to the following and
see what you get:

dSql = "DELETE * FROM BestellDetails " & _
"WHERE (([BestellDetails.ArtikelID]=" &
Me.ArtikelID & " AND " & _
"[BestellDetails.BestellungenID]=" &
Me.BestellungenID & "));"

Gary Miller
Sisters, OR
 
Gary,

Thank you so much. It works perfectly.

Jeff

Gary Miller said:
Jeff,

I think that including the SELECT clause inside the DELETE
may be tripping you up. Try changing it to the following and
see what you get:

dSql = "DELETE * FROM BestellDetails " & _
"WHERE (([BestellDetails.ArtikelID]=" &
Me.ArtikelID & " AND " & _
"[BestellDetails.BestellungenID]=" &
Me.BestellungenID & "));"

Gary Miller
Sisters, OR -----------------------------------------------------------
dSql = "DELETE * FROM BestellDetails " & _
"(SELECT BestellDetails.ArtikelID, BestellDetails.BestellungenID
FROM BestellDetails " & _
"WHERE (([BestellDetails.ArtikelID]=" & Me.ArtikelID & " AND " & _
"[BestellDetails.BestellungenID]=" & Me.BestellungenID & ")));"

CurrentDb.Execute dSql, dbFailOnError
Click to expand...
Click to expand...
 
Back
Top