Solved: sql in access vba giving error

[BoaterJohn]

[FONT=&quot]I have a access sample database with table A and table B. [/FONT]
[FONT=&quot]I am trying to delete any records in table A with a matching [TicketDeliverNo] field in table B[/FONT]
[FONT=&quot]and if [AmtPaid] field in table B >1.[/FONT]
[FONT=&quot]I have a sub proc on the forms 'close event’ that will trigger a sql clauses to delete the records in table A.[/FONT]
[FONT=&quot]However, I’m getting a syntax error in the SQL statement, so the error states. I tried to no avail to fix it.[/FONT]
[FONT=&quot]Any help is highly appreciated,[/FONT]
[FONT=&quot]Private Sub Form_Close()
Dim strSQL As String
strSQL = "DELETE tblA.*" & _
"FROM tblA" & _
"Where tblA.[TicketDeliverNo] = tblB.[TicketDeliverNo]" & _
"And tblB.[AmtPaid]>1;"
DoCmd.RunSQL strSQL
DoCmd.Close acForm, "FRMB"
End Sub

Thank You boaterjohn[/FONT]

 

[OBP]

Try

Private Sub Form_Close()
Dim strSQL As String
strSQL = "DELETE tblA.*" & _
"FROM tblA" & _
"Where tblA.[TicketDeliverNo] = tblB.[TicketDeliverNo]" & _
"And tblB.[AmtPaid]>1"
DoCmd.RunSQL strSQL
DoCmd.Close acForm, "FRMB"
End Sub

 

[BoaterJohn]

OBP, thanks for response. Looks like you omitted the semi colon. I tried this and got the same error.
"syntax error in FROM Clause" I believe that the semi colon is required using sql in vba proc. correct me if I'm in error of that. I attached the very small db so you could see the error when closing the form

 

[OBP]

I have found that with VBA generated SQL it does not need the ";" on the end.
I have added a space after the FROM tblA
so it now looks like this
Dim strSQL As String
On Error GoTo errorcatch

strSQL = "DELETE tblA.*" & _
"FROM tblA " & _
"Where tblA.[TicketDeliverNo] = tblB.[TicketDeliverNo]" & _
"And tblB.[AmtPaid]>1"
DoCmd.RunSQL strSQL
DoCmd.Close acForm, "FRMB"

Exit Sub
errorcatch:
MsgBox Err.Description

It appears to run OK as it advises you that it is about to run a Delete query.

Can I ask why you have the same data in 2 different tables?

 

[BoaterJohn]

OBP, I did see where it gives the delete warning, but states that zero records were deleted. The criteria of the sql shows 2 records that fit the the "TicketDeliverNo" and "AMTPAID">1
Any Input?

 

[Rollin_Again]

Use debug.print to print your SQL statement to the immediate window and check it to make sure it is accurately being generated.

 

[OBP]

Well, there is no Join in the SQL, so I would have built the SQL first by making a Delete Query first.

 

[OBP]

A delete query with both tables does not work, whereas the Select query does.
It gives an Identify Table to delete from error.
A delete query based on the output of the working Select query also does not work.
I can probably do it OK in VBA though.

 

[BoaterJohn]

thank you both, OBP and Rollin Again for your inputs. OBP, your idea about a join clause might do the trick I hope; and debug.print is a good idea to see what's happening in the immediate window. If all else fails, I will attempt a vba Proc to do the deletes if the sql won't work with the two tables.
Thanks Again. I let you know what I come up with.

 

[BoaterJohn]

DELETE tNotices.*, tNotices.TicketNo, tHistoryTickets.AmtPaid
FROM tHistoryTickets INNER JOIN tNotices ON tHistoryTickets.TicketNo = tNotices.TicketNo
WHERE (((tNotices.TicketNo)=[tHistoryTickets].[TicketNo]) AND ((tHistoryTickets.AmtPaid)>1));
in a qry did the trick with a access delete qry and both tables used
Thanks again OBP and Rollin Again

 

[OBP]

Well done