Syntax Error WHY?

Discussion in 'Microsoft Access Form Coding' started by NEWER USER, Feb 13, 2010.

  1. NEWER USER

    NEWER USER Guest

    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
     
    NEWER USER, Feb 13, 2010
    #1
    1. Advertisements

  2. NEWER USER

    Allen Browne Guest

    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" <> wrote in message
    news:...
    > 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
     
    Allen Browne, Feb 13, 2010
    #2
    1. Advertisements

  3. NEWER USER

    DrGUI Guest

    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
     
    DrGUI, Feb 14, 2010
    #3
  4. NEWER USER

    NEWER USER Guest

    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
     
    NEWER USER, Feb 14, 2010
    #4
  5. NEWER USER

    NEWER USER Guest

    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" <> wrote in message
    > news:...
    > > 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

    >
    > .
    >
     
    NEWER USER, Feb 14, 2010
    #5
  6. On Sat, 13 Feb 2010 21:11:02 -0800, NEWER USER
    <> 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]
     
    John W. Vinson, Feb 14, 2010
    #6
  7. NEWER USER

    Allen Browne Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:...
    >> > 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

    >>
    >> .
    >>
     
    Allen Browne, Feb 14, 2010
    #7
  8. NEWER USER

    NEWER USER Guest

    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
    > <> 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]
    > .
    >
     
    NEWER USER, Feb 14, 2010
    #8
  9. NEWER USER

    NEWER USER Guest

    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
    > <> 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]
    > .
    >
     
    NEWER USER, Feb 14, 2010
    #9
  10. On Sun, 14 Feb 2010 08:08:01 -0800, NEWER USER
    <> 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]
     
    John W. Vinson, Feb 14, 2010
    #10
  11. NEWER USER

    NEWER USER Guest

    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

    "John W. Vinson" wrote:

    > On Sun, 14 Feb 2010 08:08:01 -0800, NEWER USER
    > <> 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]
    > .
    >
     
    NEWER USER, Feb 14, 2010
    #11
  12. On Sun, 14 Feb 2010 11:38:01 -0800, NEWER USER
    <> wrote:

    >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"))


    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" wrote:
    >
    >> On Sat, 13 Feb 2010 21:11:02 -0800, NEWER USER
    >> <> 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]
    >> .
    >>

    --

    John W. Vinson [MVP]
     
    John W. Vinson, Feb 14, 2010
    #12
  13. NEWER USER

    NEWER USER Guest

    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 Sun, 14 Feb 2010 08:08:01 -0800, NEWER USER
    > <> 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]
    > .
    >
     
    NEWER USER, Feb 14, 2010
    #13
  14. NEWER USER

    NEWER USER Guest

    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" wrote:

    > On Sun, 14 Feb 2010 11:38:01 -0800, NEWER USER
    > <> wrote:
    >
    > >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"))

    >
    > 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" wrote:
    > >
    > >> On Sat, 13 Feb 2010 21:11:02 -0800, NEWER USER
    > >> <> 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]
    > >> .
    > >>

    > --
    >
    > John W. Vinson [MVP]
    > .
    >
     
    NEWER USER, Feb 14, 2010
    #14
  15. On Sun, 14 Feb 2010 15:37:01 -0800, NEWER USER
    <> wrote:

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


    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


    --

    John W. Vinson [MVP]
     
    John W. Vinson, Feb 15, 2010
    #15
  16. NEWER USER

    NEWER USER Guest

    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"))



    "John W. Vinson" wrote:

    > On Sun, 14 Feb 2010 15:37:01 -0800, NEWER USER
    > <> wrote:
    >
    > >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.

    >
    > 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
    >
    >
    > --
    >
    > John W. Vinson [MVP]
    > .
    >
     
    NEWER USER, Feb 15, 2010
    #16
  17. On Sun, 14 Feb 2010 18:50:01 -0800, NEWER USER
    <> wrote:

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


    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"))
    >


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

    John W. Vinson [MVP]
     
    John W. Vinson, Feb 15, 2010
    #17
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shaun
    Replies:
    0
    Views:
    1,126
    Shaun
    Aug 18, 2004
  2. Guest

    Compile Error: Syntax Error??

    Guest, Apr 23, 2005, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    167
    Guest
    Apr 23, 2005
  3. Guest
    Replies:
    2
    Views:
    762
    Guest
    Oct 28, 2005
  4. Guest

    Compile Error and Syntax Error on Form

    Guest, Sep 19, 2006, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    231
    Graham R Seach
    Sep 19, 2006
  5. Mr.Kane

    Login screen compile error '3075' (syntax error)

    Mr.Kane, Oct 13, 2006, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    653
    ruralguy via AccessMonster.com
    Oct 14, 2006
Loading...

Share This Page