Solved: sql in access vba giving error

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

BoaterJohn

Thread Starter
Joined
Aug 2, 2011
Messages
116
[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

Joined
Mar 8, 2005
Messages
19,895
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

Thread Starter
Joined
Aug 2, 2011
Messages
116
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

OBP

Joined
Mar 8, 2005
Messages
19,895
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

BoaterJohn

Thread Starter
Joined
Aug 2, 2011
Messages
116
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?
 
Joined
Sep 4, 2003
Messages
4,916
Use debug.print to print your SQL statement to the immediate window and check it to make sure it is accurately being generated.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Well, there is no Join in the SQL, so I would have built the SQL first by making a Delete Query first.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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

Thread Starter
Joined
Aug 2, 2011
Messages
116
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

Thread Starter
Joined
Aug 2, 2011
Messages
116
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
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top