Update syntax error

[pgsibson]

Hi
I am now trying to find the last record in a table and update a field in that table. The code I have is

Dim dbsCollege As DAO.Database
Dim rstStudentAnnualcourses As DAO.Recordset
Dim strSQL As String
Dim intLastRecord As Integer

Set dbsCollege = CurrentDb
Set rstStudentAnnualcourses = dbsCollege.OpenRecordset("tblStudentsAnnualCourses")
strSQL = "Update rstStudentsAnnualcourses" & _
"Set StuAnnCrsesCrseID = ' & intCrseID & '" & _
"WHERE StuAnnCrsesID = ' & intLastRecord & '"

DoCmd.SetWarnings False
rstStudentAnnualcourses.MoveLast
intLastRecord = StuAnnCrsesID
DoCmd.RunSQL strSQL

intCrseID is a public variable that I created and should be an integer ID from a previous action. I get a syntax error in the update statement? Can anybody help?

Regards

Paul
Salisbury, UK

 

[namliam]

Your text quotes are messed up...

Should look like:
strSQL = ""
strSQL = strSQL & "Update rstStudentsAnnualcourses" &
strSQL = strSQL & "Set StuAnnCrsesCrseID = '" & intCrseID & strSQL = strSQL & "'"
strSQL = strSQL & "WHERE StuAnnCrsesID = '" & intLastRecord & "'"

Now this is assuming that both IDs are string/text fields, if they are indeed IDs most likely they are number fields in which case you need to remove the ' in the SQL string.

 

[pgsibson]

HI

I pasted your response in and removed the ' because both are integer. It still rports an error and your first line appears in red as soon as I pasted it. I had noticed a spelling mistake which I have corrected but still no luck.

 

[Kiwiman]

Howzit

You also need to make sure there are spaces in the right spot. At the moment there is no space between Update line and the set line (also on the final line) so it would read like the below in the immediate window

Update rstStudentsAnnualcoursesSet StuAnnCrsesCrseID =...

You want somehting like the below

strSQL = ""
strSQL = strSQL & "Update rstStudentsAnnualcourses " & 
strSQL = strSQL & "Set StuAnnCrsesCrseID = " & intCrseID 
strSQL = strSQL & " WHERE StuAnnCrsesID = " & intLastRecord

 

[namliam]

Howzit

You also need to make sure there are spaces in the right spot.

DUH ! I broke my own advice....

Always gave a space at the start of each command line...
strSQL = ""
strSQL = strSQL & " Update rstStudentsAnnualcourses "
strSQL = strSQL & " Set StuAnnCrsesCrseID = " & intCrseID
strSQL = strSQL & " WHERE StuAnnCrsesID = " & intLastRecord

Above is what Kiwi already posted but slightly modified it, note the missing & on the second line.

 

[pgsibson]

Tried both and still have syntax error in DoCmd.RunSQL strSQL line?

 

[pgsibson]

Hi
Tried both the solutions and stillcome up with syntax error in DoCmd.RunSQL strSQL?

 

[Kiwiman]

Howzit

Had a closer look at your first post. You have set the SQL stmt before assigning the value to the variable intLastRecord. As this variable is used in the sql stmt, it needs to get the value first, then build the sql stmt.

Hi


Dim dbsCollege As DAO.Database
Dim rstStudentAnnualcourses As DAO.Recordset
Dim strSQL As String
Dim intLastRecord As Integer

Set dbsCollege = CurrentDb
Set rstStudentAnnualcourses = dbsCollege.OpenRecordset("tblStudentsAnnualCourses")
strSQL = "Update rstStudentsAnnualcourses" & _
"Set StuAnnCrsesCrseID = ' & intCrseID & '" & _
"WHERE StuAnnCrsesID = ' & intLastRecord & '"

DoCmd.SetWarnings False
rstStudentAnnualcourses.MoveLast
intLastRecord = StuAnnCrsesID
DoCmd.RunSQL strSQL

so try in this order

intLastRecord = StuAnnCrsesID

strSQL = ""
strSQL = strSQL & " Update rstStudentsAnnualcourses "
strSQL = strSQL & " Set StuAnnCrsesCrseID = " & intCrseID 
strSQL = strSQL & " WHERE StuAnnCrsesID = " & intLastRecord

 

[Kiwiman]

Howzit

This part may also need to be like the following??

intLastRecord = rstStudentAnnualcourses!StuAnnCrsesID

 

[namliam]

I hadnt even noticed, but there is no way to update a recordset...
rstStudentsAnnualcourses is a recordset in memory...

You either have to use
rstStudentsAnnualcourses.edit
...
rstStudentsAnnualcourses.update

or update the source table instead.... tblStudentsAnnualCourses

 

[Kiwiman]

Howzit

Well spotted mailman.

 

[pgsibson]

Thank everyone. I had found that changing from rst... to tbl... worked. Onto the next step.
Paul