Update db SET not working for me

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

Answer 1

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

Answer 2

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?

Answer 3

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

Answer 4

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

Answer 5

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