PC Review


Reply
Thread Tools Rate Thread

Syntax Error WHY?

 
 
NEWER USER
Guest
Posts: n/a
 
      13th Feb 2010
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
 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      13th Feb 2010
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"NEWER USER" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
 
 
 
DrGUI
Guest
Posts: n/a
 
      14th Feb 2010
Put a space before the 'W" in strWhere as follows:

strWhere = " WHERE " & .Filter


"NEWER USER" wrote:

> 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

 
Reply With Quote
 
NEWER USER
Guest
Posts: n/a
 
      14th Feb 2010
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"))

"DrGUI" wrote:

> Put a space before the 'W" in strWhere as follows:
>
> strWhere = " WHERE " & .Filter
>
>
> "NEWER USER" wrote:
>
> > 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

 
Reply With Quote
 
NEWER USER
Guest
Posts: n/a
 
      14th Feb 2010
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

"Allen Browne" wrote:

> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "NEWER USER" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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

>
> .
>

 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      14th Feb 2010
On Sat, 13 Feb 2010 21:11:02 -0800, NEWER USER
<(E-Mail Removed)> wrote:

>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 [table]
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?
--

John W. Vinson [MVP]
 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      14th Feb 2010
John Vinson has explained what's wrong with the SQL statement you have.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"NEWER USER" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Allen Browne" wrote:
>
>> 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.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>>
>> "NEWER USER" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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

>>
>> .
>>

 
Reply With Quote
 
NEWER USER
Guest
Posts: n/a
 
      14th Feb 2010
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" wrote:

> On Sat, 13 Feb 2010 21:11:02 -0800, NEWER USER
> <(E-Mail Removed)> wrote:
>
> >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 [table]
> 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?
> --
>
> John W. Vinson [MVP]
> .
>

 
Reply With Quote
 
NEWER USER
Guest
Posts: n/a
 
      14th Feb 2010
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" wrote:

> On Sat, 13 Feb 2010 21:11:02 -0800, NEWER USER
> <(E-Mail Removed)> wrote:
>
> >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 [table]
> 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?
> --
>
> John W. Vinson [MVP]
> .
>

 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      14th Feb 2010
On Sun, 14 Feb 2010 08:08:01 -0800, NEWER USER
<(E-Mail Removed)> wrote:

>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.


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.
--

John W. Vinson [MVP]
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: HELP! UPDATE command tsql syntax and sql access syntax Bob Barrows [MVP] Microsoft Access Queries 5 27th Aug 2008 09:43 PM
"invalid STORE command syntax invalid message set syntax" Catullus Nacakus Windows Vista Mail 6 27th Jan 2008 12:46 AM
WHERE syntax v. JOIN ON syntax Siegfried Heintze Microsoft Access Database Table Design 8 17th Dec 2007 02:30 PM
Tools to convert a .reg file syntax to an inf file syntax Jeffrey Millar Microsoft Dot NET 0 12th Sep 2005 08:35 PM
how to make sure a xsl document has valid xsl syntax? i tried loading it into an xml document but that doesnt show syntax errors inside attributes such as "foo/bar" vs "bar\foo" Daniel Microsoft C# .NET 0 31st Aug 2005 01:01 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:31 PM.