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, Im 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
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
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?
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
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!