Solved: sql in access vba giving error

I have a access sample database with table A and table B.
I am trying to delete any records in table A with a matching [TicketDeliverNo] field in table B
and if [AmtPaid] field in table B >1.
I have a sub proc on the forms 'close event’ that will trigger a sql clauses to delete the records in table A.
However, I’m getting a syntax error in the SQL statement, so the error states. I tried to no avail to fix it.
Any help is highly appreciated,
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

 

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

 

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

 

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?

 

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?

 

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

 

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

 

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.

 

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.

 

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

 

Well done