Link to home
Start Free TrialLog in
Avatar of Megin
Megin

asked on

SQL Syntax - Something is wrong

I keep getting a warning that something is wrong with my syntax in the SQL part of the code below:
Private Sub btnDeleteAssignment_Click()
'****Delete a record from the SubtaskAssignments table****

Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim strSQL As String
Dim i As Variant

Set frm = Forms("f_SubTaskAssignments")
Set ctl = frm![lstExistingAssignments]
Set db = CurrentDb

For Each i In ctl.ItemsSelected

'***For future reference - Add chr(34) around text!

strSQL = "DELETE FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ")"
            
db.Execute strSQL, dbFailOnError

Next i

ctl.Requery
End Sub

Open in new window


I can't see it! It is probably right in front of me, but I have been looking at this code for over an hour and cannot, for the life of me, figure out what the problem is.

Can someone please take a look at tell me where the syntax is wrong? The error message says it is in the strSQL section, though it highlights the db.execute strsql, dbFailOnError.
Avatar of Mark Bullock
Mark Bullock
Flag of United States of America image

I don't know why you used the square brackets.
Try removing [ and ]
re
ctl.Column(0, i)   etc
Not sure you can reference the Column property of a List/Combo box in SQL.
You may need to set variables to those first ...

The brackets are fine ... if Control Name had spaces, the brackets are required anyway ... not necessary here but not really the issue.
SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
add the line shown.  It will output the SQL statement to the Immediate window so you can review it.  If you don't see the error post it up so we can take a look.
OM Gang

Debug.Print strSQL     '<------------ add this line
db.Execute strSQL, dbFailOnError
Joe,

The references to the ctl.Column(x, i) are all explicitly defined when the SQL statement is created dynamically.

Dale
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Megin
Megin

ASKER

Wow! I left for lunch and came back to so many answers! Thank you!

Ends up that I needed to get rid of the last paren and add a quotation mark.

Thank you!!!!!!!!
Megin ... actually I should not get the Accepted answer, as it was Dale who pointed out the extra paren first ...
Just sayin' ...

mx
Avatar of Megin

ASKER

I apologize if that was bad.

I started reading through the answers and, once I found out which one worked, I gave credit to the responses that pointed that out. There were just so many responses!

Dale, I didn't mean to short you credit.
No problem, Megin.  ;-)

Glad we were able to resolve your problem.