UPDATE query won't update

[Richard Hollenbeck]

I have a temporary table for recording grades for college classes. Once the
grades are correct, I want to update any existing grades in the permanent
scores table (studentScores). Though I tried for several days to solve
this, I can't seem to figure out what's wrong with this query. It looks
right to me:

Private Sub updateScoresTable()
'any existing records modified in the temp table need to be updated in the
permanent table

Dim StrSql As String

' studentID and activityID together form a composite key in both tables.

StrSql = "UPDATE studentScores "
StrSql = StrSql & "SET studentScores.studentID = temp.studentID, "
StrSql = StrSql & "studentScores.activityID = temp.activityID, "
StrSql = StrSql & "studentScores.score = temp.score "
StrSql = StrSql & "WHERE studentScores.studentID = temp.studentID "
StrSql = StrSql & "AND studentScores.activityID = temp.activityID;"

'DoCmd.SetWarnings False ' remove the nasty prompts
'(temporarily turned back on for troubleshooting.)

DoCmd.RunSQL StrSql
DoCmd.SetWarnings True ' restore the nasty prompts

End Sub

Thanks!
Richard Hollenbeck

 

[Duane Hookom]

This might work:
StrSql = "UPDATE studentScores, Temp "

If not, you may need to use an INNER Join on the related fields.

 

[Richard Hollenbeck]

That did the trick! I added temp to the UPDATE clause:
StrSql = "UPDATE studentScores, temp "

Works like a charm! Thank you very much!