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:
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.
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
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
The references to the ctl.Column(x, i) are all explicitly defined when the SQL statement is created dynamically.
Dale
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!!!!!!!!
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
Just sayin' ...
mx
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.
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.
Glad we were able to resolve your problem.
Try removing [ and ]