none
Update db SET not working for me RRS feed

  • Question

  • Please Help, I receive a "Compile error: Expected: end of statement" on the SET when I try to write the following;

    UPDATE db Set rs2.Fields("Jan")=rs1.Fields("Totals") Where rs2.Fields("Cat")=rs1.Fields("Cat")

    Here is my sub;

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

     Set db=CurrentDb

    Set rs1=db.OpenRecordset("Total Forecast")

    set rs2=db.Openrecordset("Forecast")

      If rs1.Fields("Cat")="Class" then

              If rs1.Fields("Months")="3" Then

                   Update db SET rs2.Fields("Jan")=rs1.Fields("Totals") Where rs2.Fields("Cat")=rs1.Fields("Cat")

              End If

      End If

    Wednesday, August 21, 2019 3:39 PM

All replies

  • You need to look up the proper syntax for recordset updates. You are mixing SQL syntax with recordset syntax. 

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, August 21, 2019 3:58 PM
  • Can you elaborate? I am new to this programming and need guidance.  Can you tell me where in the code I used recordset and where I used SQL?
    Thursday, August 22, 2019 11:46 AM
  • Here is an example of an updatable recordset that uses the information on the currently opened form. The code is in the form itself so we can use the identifier Me to refer to the form.

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSQL As String
    
    
        Set db = CurrentDb
        strSQL = "SELECT LastName, SubjectLine " _
                 & "FROM EmailTemplate " _
                 & "WHERE NoticeName='" & Me.txtNoticeName & "'"
    
        Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
        With rs
            !LastName = Me.txtLastName
            !SubjectLine = Me.txtSubject
            .Update
        End With
    
        DoCmd.Close acForm, Me.Name
    
        Set rs = Nothing
        Set db = Nothing


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Thursday, August 22, 2019 12:41 PM
  • Bill is correct.  Probably the easiest approach is first make your query using the Query Design View....when it is working the way you want, then look at it in SQL View - - that will give you the SQL statement syntax.....
    Saturday, August 24, 2019 4:36 PM
  • Can you elaborate? I am new to this programming and need guidance.  Can you tell me where in the code I used recordset and where I used SQL?
    The equivalent to Bill's example, using an SQL UPDATE statement would be:

        Dim strSQL As String

        strSQL = "UPDATE EmailTemplate " & _
            "SET LastName = """ & Me.txtLastName  & """", " & _
            "SubjectLine = """ & Me.txtSubject & """ " & _
            "WHERE NoticeName = """ & Me.txtNoticeName & """"

        CurrentDb.Execute strSQL, dbFailOnError

    Note how the literal quotes characters required to delimit each string expression are each represented by a contiguous pair of quotes characters.

    Ken Sheridan, Stafford, England

    Sunday, August 25, 2019 10:02 PM