Tech Support Guy banner
Status
Not open for further replies.

Solved: sql in access vba giving error

1K views 10 replies 3 participants last post by  OBP 
#1 ·
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
 
See less See more
#2 ·
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
 
#3 ·
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
 

Attachments

#4 ·
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?
 

Attachments

#8 ·
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.
 
#9 ·
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.
 
#10 ·
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
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top