Syntax Error WHY?

N

NEWER USER

I have a subform on an Unbound form. I am running a Function from a command
button on my subform. I am getting a Syntax Error in my Update statement.
Can anyone tell me WHY?

Function QClose()
On Error GoTo QClose_Err

Dim sql As String
Dim strWhere As String
Dim strMsg As String
Dim frm As Form

With CodeContextObject
strWhere = "WHERE " & .Filter
End With

strMsg = "FILTERED records will be updated to Closed."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Update filtered records on subform
DoCmd.CancelEvent
Exit Function
End If

DoCmd.SetWarnings False
Set frm = Screen.ActiveControl.Parent
sql = "update " & frm.RecordSource & " set " & frm.RecordSource &
".ThreatStatus = 'Closed'" & strWhere
DoCmd.RunSQL sql
DoCmd.Requery
DoCmd.SetWarnings True

QClose_Exit:
Exit Function

QClose_Err:
MsgBox Err.Description
Resume QClose_Exit

End Function
 
A

Allen Browne

To see what you've done wrong, add the line:
Debug.Print sql
just ahead of the RunSQL line. When it fails, open the Immediate Window
(Ctrl+G) and see what came out.

For example, frm.RecordSource could be something like:
SELECT * FROM Table1;
which would give you a completely invalid SQL statement.

Or perhaps ThreatStatus is not a Text field.

You can copy the SQL statement into SQL View of a query and see if Access
can make sense of it if you can't.
 
N

NEWER USER

The space before Where gave the same error. Attached is the Debug. Maybe
this will help as Allen suggested. Thanks for the help.

update Select * from qryData Where ThreatStatus = 'Open' And Threat = 'Low'
set Select * from qryData Where ThreatStatus = 'Open' And Threat =
'Low'.ThreatStatus = 'Closed' WHERE ((frmVulnerability.svc_name="cifs"))
 
N

NEWER USER

I eliminated the subform and went to a bound Main form. I changed the code
but got the same syntax error.

Here is the Debug

update Select * from qryData Where ThreatStatus = 'Open' And Threat = 'Low'
set Select * from qryData Where ThreatStatus = 'Open' And Threat =
'Low'.ThreatStatus = 'Closed' WHERE ((frmVulnerability.svc_name="cifs"))


Private Sub Update_Click()
On Error GoTo Update_Click_Err
Dim sql As String
Dim strWhere As String
Dim strMsg As String
Dim frm As Form

With CodeContextObject
strWhere = " WHERE " & Me.Filter
End With

strMsg = "FILTERED records will be updated to Closed status."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Update filtered records to Closed status
DoCmd.CancelEvent
Exit Sub
End If

DoCmd.SetWarnings False
Set frm = Screen.ActiveForm
sql = "update " & frm.RecordSource & " set " & frm.RecordSource &
".ThreatStatus = 'Closed'" & strWhere

Debug.Print sql
DoCmd.RunSQL sql
DoCmd.Requery
DoCmd.SetWarnings True

Update_Click_Exit:
Exit Sub

Update_Click_Err:
MsgBox Err.Description
Resume Update_Click_Exit

End Sub
 
J

John W. Vinson

The space before Where gave the same error. Attached is the Debug. Maybe
this will help as Allen suggested. Thanks for the help.

update Select * from qryData Where ThreatStatus = 'Open' And Threat = 'Low'
set Select * from qryData Where ThreatStatus = 'Open' And Threat =
'Low'.ThreatStatus = 'Closed' WHERE ((frmVulnerability.svc_name="cifs"))
Click to expand...

You can have an Update query...

or you can have a Select query...

but you can't have an Update Select query.

The syntax for an update query is

UPDATE

SET fielda = valuea, fieldb = valueb
WHERE <criteria>

Some update queries can be based on JOINS but that doesn't appear to be the
case here.

I've read your query several times and have no guess what your repeated WHEREs
are intended to do, nor what fields you want to update to what value. Could
you explain what this query is intended to do, and for which records?
 
N

NEWER USER

I open a form 'frmVulnerability' based on 'qryAll' which displays all
records. From here, I filter on several fields to get my final recordset
(filters will vary each time). Once I have my selected records, I want to
update field 'ThreatStatus' to 'Closed' for ONLY the filtered records
displayed on current form using a command button with code that works.
Thanks for your help and patience.

John W. Vinson said:
The space before Where gave the same error. Attached is the Debug. Maybe
this will help as Allen suggested. Thanks for the help.

update Select * from qryData Where ThreatStatus = 'Open' And Threat = 'Low'
set Select * from qryData Where ThreatStatus = 'Open' And Threat =
'Low'.ThreatStatus = 'Closed' WHERE ((frmVulnerability.svc_name="cifs"))
Click to expand...

You can have an Update query...

or you can have a Select query...

but you can't have an Update Select query.

The syntax for an update query is

UPDATE

SET fielda = valuea, fieldb = valueb
WHERE <criteria>

Some update queries can be based on JOINS but that doesn't appear to be the
case here.

I've read your query several times and have no guess what your repeated WHEREs
are intended to do, nor what fields you want to update to what value. Could
you explain what this query is intended to do, and for which records?
Click to expand...
 
N

NEWER USER

I have been able to get it to work SOMETIME. I open the form and it works
the first time. Close the form and reopen - works again. But, if I run the
command without closing and reopening the form, it fails everytime. Here is
my code and the 4 Debugs. You can see what is happening on the last debug.
How might I remedy this problem? I need to reset ?? before running each time
rather than closing/opening form.

update qryData set qryData.ThreatStatus = 'Closed'WHERE
(((qryData.svc_name="unknown"))) AND ((qryData.Threat="High"))
update qryData set qryData.ThreatStatus = 'Closed'WHERE
(((qryData.svc_name="cifs"))) AND ((qryData.Threat="Low"))
update qryData set qryData.ThreatStatus = 'Closed'WHERE
((((qryData.svc_name="www"))) AND ((qryData.Priority=3))) AND
((qryData.Threat="Low"))

'The following errors
update Select * from qryData Where ThreatStatus = 'Open' set Select * from
qryData Where ThreatStatus = 'Open'.ThreatStatus = 'Closed'WHERE
((frmVulnerability.port=21))


Private Sub Update_Click()
Dim sql As String
Dim strWhere As String
Dim strMsg As String
Dim frm As Form


With CodeContextObject
strWhere = "WHERE " & .Filter
End With

strMsg = "FILTERED records will be updated to Closed status."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Update filtered records to Closed status
DoCmd.CancelEvent
Exit Sub
End If

DoCmd.SetWarnings False
Set frm = Screen.ActiveForm

sql = "update " & frm.RecordSource & " set " & frm.RecordSource &
".ThreatStatus = 'Closed'" & strWhere
Debug.Print sql
DoCmd.RunSQL sql
DoCmd.Requery
DoCmd.SetWarnings True

Update_Click_Exit:
Exit Sub

Update_Click_Err:
MsgBox Err.Description
Resume Update_Click_Exit

End Sub





John W. Vinson said:
The space before Where gave the same error. Attached is the Debug. Maybe
this will help as Allen suggested. Thanks for the help.

update Select * from qryData Where ThreatStatus = 'Open' And Threat = 'Low'
set Select * from qryData Where ThreatStatus = 'Open' And Threat =
'Low'.ThreatStatus = 'Closed' WHERE ((frmVulnerability.svc_name="cifs"))
Click to expand...

You can have an Update query...

or you can have a Select query...

but you can't have an Update Select query.

The syntax for an update query is

UPDATE

SET fielda = valuea, fieldb = valueb
WHERE <criteria>

Some update queries can be based on JOINS but that doesn't appear to be the
case here.

I've read your query several times and have no guess what your repeated WHEREs
are intended to do, nor what fields you want to update to what value. Could
you explain what this query is intended to do, and for which records?
Click to expand...
 
J

John W. Vinson

I open a form 'frmVulnerability' based on 'qryAll' which displays all
records. From here, I filter on several fields to get my final recordset
(filters will vary each time). Once I have my selected records, I want to
update field 'ThreatStatus' to 'Closed' for ONLY the filtered records
displayed on current form using a command button with code that works.
Thanks for your help and patience.
Click to expand...

Then create an Update query (in the syntax I suggested) that applies the same
criteria.

Since I have no way to know what 'several fields' you mean, it's more than a
bit difficult for me to help! I think the problem stems from the fact that
you're attempting to adapt code which was created for a different purpose
(starting with the SELECT clause), rather than building the UPDATE query from
scratch. Try creating an UPDATE query in the query grid which does what you
want (or a reasonable facsimile thereof), go to SQL view, and use that SQL as
the target for your code.
 
N

NEWER USER

I can't create a single update query as the recordsource/filters change each
session I open the form. This is why I want to look at the current
recordsouce and any filters applied (Where clause) and update ONLY the viewed
records on the form.

The form opens and displays 500 records. I Filter By Selection on a field
and I now have 100 records. I further filter on another field and now have
15 records. The recordsource and 2 filters (Where string) are displayed in
the Form's properties. I now want to update the [ThreatStaus] field to
'Closed' for the 15 records only.

Form Name "frmVulnerability"
Recordsource "qryAll"
Field Names ThreatStatus 'Open or Closed'
Priority "1,2,3,4,5"
Threat 'High,Medium,Low
Name
 
J

John W. Vinson

I have been able to get it to work SOMETIME. I open the form and it works
the first time. Close the form and reopen - works again. But, if I run the
command without closing and reopening the form, it fails everytime. Here is
my code and the 4 Debugs. You can see what is happening on the last debug.
How might I remedy this problem? I need to reset ?? before running each time
rather than closing/opening form.

update qryData set qryData.ThreatStatus = 'Closed'WHERE
(((qryData.svc_name="unknown"))) AND ((qryData.Threat="High"))
update qryData set qryData.ThreatStatus = 'Closed'WHERE
(((qryData.svc_name="cifs"))) AND ((qryData.Threat="Low"))
update qryData set qryData.ThreatStatus = 'Closed'WHERE
((((qryData.svc_name="www"))) AND ((qryData.Priority=3))) AND
((qryData.Threat="Low"))
Click to expand...

Again...

This comes of building SQL strings when you evidently don't have a clear
understanding of SQL strings.

An UPDATE query can have *only one* UPDATE clause. You're stringing a whole
bunch of UPDATE strings into the SQL.

Are you intending to create three update queries? If so, empty the string
after running the query.

Or are you trying to create one update query to update all three svc_names -
"Unknown", "cifs" and "www" - in one operation? If so you need to build an OR
clause.

You also need to put a blank before the WHERE keyword. Blanks are meaningful.

Again, as suggested elsethread - *create the query first in the query design
window*. Look at the SQL. Write your code to mimic that SQL!

'The following errors
update Select * from qryData Where ThreatStatus = 'Open' set Select * from
qryData Where ThreatStatus = 'Open'.ThreatStatus = 'Closed'WHERE
((frmVulnerability.port=21))


Private Sub Update_Click()
Dim sql As String
Dim strWhere As String
Dim strMsg As String
Dim frm As Form


With CodeContextObject
strWhere = "WHERE " & .Filter
End With

strMsg = "FILTERED records will be updated to Closed status."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Update filtered records to Closed status
DoCmd.CancelEvent
Exit Sub
End If

DoCmd.SetWarnings False
Set frm = Screen.ActiveForm

sql = "update " & frm.RecordSource & " set " & frm.RecordSource &
".ThreatStatus = 'Closed'" & strWhere
Debug.Print sql
DoCmd.RunSQL sql
DoCmd.Requery
DoCmd.SetWarnings True

Update_Click_Exit:
Exit Sub

Update_Click_Err:
MsgBox Err.Description
Resume Update_Click_Exit

End Sub





John W. Vinson said:
The space before Where gave the same error. Attached is the Debug. Maybe
this will help as Allen suggested. Thanks for the help.

update Select * from qryData Where ThreatStatus = 'Open' And Threat = 'Low'
set Select * from qryData Where ThreatStatus = 'Open' And Threat =
'Low'.ThreatStatus = 'Closed' WHERE ((frmVulnerability.svc_name="cifs"))
Click to expand...

You can have an Update query...

or you can have a Select query...

but you can't have an Update Select query.

The syntax for an update query is

UPDATE

SET fielda = valuea, fieldb = valueb
WHERE <criteria>

Some update queries can be based on JOINS but that doesn't appear to be the
case here.

I've read your query several times and have no guess what your repeated WHEREs
are intended to do, nor what fields you want to update to what value. Could
you explain what this query is intended to do, and for which records?
Click to expand...
Click to expand...
 
N

NEWER USER

I have been able to get it to work SOMETIME. I open the form and it works
the first time. Close the form and reopen - works again. But, if I run the
command without closing and reopening the form, it fails everytime. Here is
my code and the 4 Debugs. You can see what is happening on the last debug.
How might I remedy this problem? I need to reset ?? before running each time
rather than closing/opening form.

update qryData set qryData.ThreatStatus = 'Closed'WHERE
(((qryData.svc_name="unknown"))) AND ((qryData.Threat="High"))
update qryData set qryData.ThreatStatus = 'Closed'WHERE
(((qryData.svc_name="cifs"))) AND ((qryData.Threat="Low"))
update qryData set qryData.ThreatStatus = 'Closed'WHERE
((((qryData.svc_name="www"))) AND ((qryData.Priority=3))) AND
((qryData.Threat="Low"))

'The following errors
update Select * from qryData Where ThreatStatus = 'Open' set Select * from
qryData Where ThreatStatus = 'Open'.ThreatStatus = 'Closed'WHERE
((frmVulnerability.port=21))


Private Sub Update_Click()
Dim sql As String
Dim strWhere As String
Dim strMsg As String
Dim frm As Form


With CodeContextObject
strWhere = "WHERE " & .Filter
End With

strMsg = "FILTERED records will be updated to Closed status."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Update filtered records to Closed status
DoCmd.CancelEvent
Exit Sub
End If

DoCmd.SetWarnings False
Set frm = Screen.ActiveForm

sql = "update " & frm.RecordSource & " set " & frm.RecordSource &
".ThreatStatus = 'Closed'" & strWhere
Debug.Print sql
DoCmd.RunSQL sql
DoCmd.Requery
DoCmd.SetWarnings True

Update_Click_Exit:
Exit Sub

Update_Click_Err:
MsgBox Err.Description
Resume Update_Click_Exit

End Sub
 
N

NEWER USER

I want ONE Update Query to update ONE field only as the Where string varies.
Update field ThreatStaus to Closed'. I nderstand how to write the Update
Query, view the SQL, etc. The Where string will change everytime I filter
the form differently. All records display On Open and I manually narrow down
to get my final set of records. This is why I want the code to look at the
recordsouce and varying Where string so only the final filtered records being
viewed will get updated, not all records in the table. The next time I run
the code it will update an entire different set of filtered records. The
code WORKS the first time. A second attempt will fail. How do I clear the
string prior to running the command a second time? I can close the form and
reopen and run it again and it works, but I must close and open before a
second attempt.

John W. Vinson said:
I have been able to get it to work SOMETIME. I open the form and it works
the first time. Close the form and reopen - works again. But, if I run the
command without closing and reopening the form, it fails everytime. Here is
my code and the 4 Debugs. You can see what is happening on the last debug.
How might I remedy this problem? I need to reset ?? before running each time
rather than closing/opening form.

update qryData set qryData.ThreatStatus = 'Closed'WHERE
(((qryData.svc_name="unknown"))) AND ((qryData.Threat="High"))
update qryData set qryData.ThreatStatus = 'Closed'WHERE
(((qryData.svc_name="cifs"))) AND ((qryData.Threat="Low"))
update qryData set qryData.ThreatStatus = 'Closed'WHERE
((((qryData.svc_name="www"))) AND ((qryData.Priority=3))) AND
((qryData.Threat="Low"))
Click to expand...

Again...

This comes of building SQL strings when you evidently don't have a clear
understanding of SQL strings.

An UPDATE query can have *only one* UPDATE clause. You're stringing a whole
bunch of UPDATE strings into the SQL.

Are you intending to create three update queries? If so, empty the string
after running the query.

Or are you trying to create one update query to update all three svc_names -
"Unknown", "cifs" and "www" - in one operation? If so you need to build an OR
clause.

You also need to put a blank before the WHERE keyword. Blanks are meaningful.

Again, as suggested elsethread - *create the query first in the query design
window*. Look at the SQL. Write your code to mimic that SQL!

'The following errors
update Select * from qryData Where ThreatStatus = 'Open' set Select * from
qryData Where ThreatStatus = 'Open'.ThreatStatus = 'Closed'WHERE
((frmVulnerability.port=21))


Private Sub Update_Click()
Dim sql As String
Dim strWhere As String
Dim strMsg As String
Dim frm As Form


With CodeContextObject
strWhere = "WHERE " & .Filter
End With

strMsg = "FILTERED records will be updated to Closed status."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Update filtered records to Closed status
DoCmd.CancelEvent
Exit Sub
End If

DoCmd.SetWarnings False
Set frm = Screen.ActiveForm

sql = "update " & frm.RecordSource & " set " & frm.RecordSource &
".ThreatStatus = 'Closed'" & strWhere
Debug.Print sql
DoCmd.RunSQL sql
DoCmd.Requery
DoCmd.SetWarnings True

Update_Click_Exit:
Exit Sub

Update_Click_Err:
MsgBox Err.Description
Resume Update_Click_Exit

End Sub





John W. Vinson said:
On Sat, 13 Feb 2010 21:11:02 -0800, NEWER USER

The space before Where gave the same error. Attached is the Debug. Maybe
this will help as Allen suggested. Thanks for the help.

update Select * from qryData Where ThreatStatus = 'Open' And Threat = 'Low'
set Select * from qryData Where ThreatStatus = 'Open' And Threat =
'Low'.ThreatStatus = 'Closed' WHERE ((frmVulnerability.svc_name="cifs"))

You can have an Update query...

or you can have a Select query...

but you can't have an Update Select query.

The syntax for an update query is

UPDATE

SET fielda = valuea, fieldb = valueb
WHERE <criteria>

Some update queries can be based on JOINS but that doesn't appear to be the
case here.

I've read your query several times and have no guess what your repeated WHEREs
are intended to do, nor what fields you want to update to what value. Could
you explain what this query is intended to do, and for which records?
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

I want ONE Update Query to update ONE field only as the Where string varies.
Update field ThreatStaus to Closed'. I nderstand how to write the Update
Query, view the SQL, etc. The Where string will change everytime I filter
the form differently. All records display On Open and I manually narrow down
to get my final set of records. This is why I want the code to look at the
recordsouce and varying Where string so only the final filtered records being
viewed will get updated, not all records in the table. The next time I run
the code it will update an entire different set of filtered records. The
code WORKS the first time. A second attempt will fail. How do I clear the
string prior to running the command a second time? I can close the form and
reopen and run it again and it works, but I must close and open before a
second attempt.
Click to expand...

I'm not sure why it's piling on the strings; let's see...

I'd use the EXECUTE method rather than RunSQL, it makes it unneccesary to turn
off SetWarnings, and traps errors. Try:


Private Sub Update_Click()
Dim sql As String
Dim strWhere As String
Dim strMsg As String
Dim frm As Form
On Error GoTo Update_Click_Err

With CodeContextObject
strWhere = " WHERE " & .Filter ' <<< note blank before WHERE
End With

strMsg = "FILTERED records will be updated to Closed status."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Update filtered records to Closed status
DoCmd.CancelEvent
Exit Sub
End If

'DoCmd.SetWarnings False <<< commented out
'Set frm = Screen.ActiveForm '<<< Just use Me. instead of frm.
' If there's only one ThreatStatus field you don't need to qualify it

sql = "update " & Me.RecordSource & " set ThreatStatus = 'Closed'" & strWhere
Debug.Print sql
'DoCmd.RunSQL sql
Application.Execute sql, dbFailOnError
DoCmd.Requery
'DoCmd.SetWarnings True <<< not needed now
sql = "" ' clean up after yourself when you're done


Update_Click_Exit:
Exit Sub

Update_Click_Err:
MsgBox Err.Description
Resume Update_Click_Exit

End Sub
 
N

NEWER USER

I used your code and Application.Execute gave me a Compile Error (Method or
data member not found). I changed back to RunSQL. I opened the form and ran
the code - It Works (1st instance)

I closed the form and reopened and ran it again with different filters - It
works (2nd instance)

I ran the code again WITHOUT closing and reopening form and it fails. See
generated SQL on each separate instance. Why does it generate Update Select
* instead of the query name (qryData)?

1st Instance
update qryData set ThreatStatus = 'Closed' WHERE
(((qryData.ip="172.17.16.111"))) AND ((qryData.svc_name="unknown"))

2nd instance
update qryData set ThreatStatus = 'Closed' WHERE
((((qryData.svc_name="www"))) AND ((qryData.Threat="Low"))) AND
((qryData.ip="172.17.16.28"))

3rd instance
update Select * from qryData Where ThreatStatus = 'Open' set ThreatStatus =
'Closed' WHERE (((frmVulnerability.ip="172.17.16.100"))) AND
((svc_name="cifs"))
 
J

John W. Vinson

I used your code and Application.Execute gave me a Compile Error (Method or
data member not found). I changed back to RunSQL. I opened the form and ran
the code - It Works (1st instance)
Click to expand...

Apologies for messing up the syntax of the Execute method: it's not
Application.Execute but CurrentDb.Execute.
I closed the form and reopened and ran it again with different filters - It
works (2nd instance)

I ran the code again WITHOUT closing and reopening form and it fails. See
generated SQL on each separate instance. Why does it generate Update Select
* instead of the query name (qryData)?

1st Instance
update qryData set ThreatStatus = 'Closed' WHERE
(((qryData.ip="172.17.16.111"))) AND ((qryData.svc_name="unknown"))

2nd instance
update qryData set ThreatStatus = 'Closed' WHERE
((((qryData.svc_name="www"))) AND ((qryData.Threat="Low"))) AND
((qryData.ip="172.17.16.28"))

3rd instance
update Select * from qryData Where ThreatStatus = 'Open' set ThreatStatus =
'Closed' WHERE (((frmVulnerability.ip="172.17.16.100"))) AND
((svc_name="cifs"))
Click to expand...

Without being able to step through the code, I have no idea. I'd put a
breakpoint in the code (mouseclick in the grey bar to the left of the code
next to an executable statement); run the code and then use the Debug options
to step through it, and view the variables as they're getting set. Access
isn't providing the Select * by itself - it must be in your code somewhere!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Current Record Source 6
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
How to Apply Current Recordsource? 13
Create a SubForm on a Tab Control 0
Keep form open until loop completes 2
You must use the dbSeeChanges option with Open Recordset whenaccessing a SQL Server table that has a 1
HOW TO Simplify following code 4
Simplify Code-Delete Query 3

Top