SQL Syntax ??

[CEH]

I am trying a little something with a form I have... to delete records on that form via SQL statement in the VBA....... IS there something wrong with this syntax?
It just returns me to record number one and does no deleting.....

Private Sub Command144_Click()
Dim strSQL As String
DoCmd.RunCommand acCmdRun

strSQL = "DELETE * FROM tblApplicant INNER JOIN tblInterview ON tblApplicant.ApplicantID = tblInterview.ApplicantID " & _
"WHERE (((tblApplicant.ApplicantID)=" & Me.ApplicantID & ") AND ((tblInterview.InterviewID)=" & Me.InterviewID & "));"

End Sub

Thanks

 

[CraigDolphin]

Don't you need to actually run the SQL for it to do anything?

Domcd.runsql strSQL

apologies if you do have that bit and were testing the string via a query instead...

 

[CEH]

Well....big DUH... but ey, this is my first time doing it this way....still getting odd error...... heres what I changed it to

Private Sub Command144_Click()
On Error GoTo Err_Command144_Click

    Dim strSQL1 As String

strSQL1 = "DELETE * FROM tblApplicant INNER JOIN tblInterview ON tblApplicant.ApplicantID = tblInterview.ApplicantID " & _
"WHERE (((tblApplicant.ApplicantID)=" & Me.ApplicantID & ") AND ((tblInterview.InterviewID)=" & Me.InterviewID & "));"
DoCmd.RunSQL strSQL1


Exit_Command144_Click:
    Exit Sub

Err_Command144_Click:
    MsgBox Err.Description
    Resume Exit_Command144_Click
    
End Sub

Get back error saying "specify the table containing the records you want to delete"
I DO! one is "tblApplicant" the other "tblInterview"
Do I need to set the SQL separately?

 

[mayusri]

Curtis, try this....

DELETE *
FROM tblApplicant where tblApplicant .Applicantid = (SELECT tblApplicant .customer_id from tblApplicant INNER JOIN tblInterview ON tblApplicant.ApplicantID = tblInterview.ApplicantID " & _
"WHERE (((tblApplicant.ApplicantID)=" & Me.ApplicantID & ") AND ((tblInterview.InterviewID)=" & Me.InterviewID & ")));"

 

[CraigDolphin]

Sorry Curtis. I'm not a SQL expert....I usually debug SQL by using the immediate window (ctrl-G) during debug to get the string during code execution, and copy it to the clipboard. Then I create a blank query and paste in the sql and see if it runs. If not, I tinker with it until it does, then go back into the code and modify the string to match the working sql.

If Maysuri's idea doesn't work, the process may help you find a solution that works.

 

[CEH]

Well, I got it to work..... kind of like you do... But I just created a delete query... with both tables... that didn't work, so I removed the tblInterview and just ran the delete query on the ApplicantID.... Then I execute the query from a VBA statement. Thru the way the tables are joined it deletes both records I needed deleted. I probably should have restructured the form to delete the right ID...(I think the DB relationship structure is sound and normalized) but the DB is in use with ALOT of data. And actually that "Delete" key should NEVER be used. I'll try "mayusri's" way to see if it works anyway. I want to know how to do it correctly using the SQL.
Thanks to all!!!