Asked by:
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
All replies
-
-
-
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- Edited by Bill Mosca, MS MVP 2006-2016 Thursday, August 22, 2019 12:43 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