How to update multiple records in MS Access database from a list in a form

[PapaRatzi]

Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound) that displays the new Top 30 positions with additional information defined in the form such as Date Entered chart, Highest Position, etc. I have a command button (Update) that will launch an event to update each table entry for each record shown on the form. This event needs to loop through the records displayed in the form and then find the corresponding record in the database table and update certain fields. The following will successfully go through each record on the form but I am stuck with the coding to find the original record in the database (via its unique Record Number ID). FindFirst or a SQL statement gives a runtime error that states 'syntax error'.
What am I missing?
NB: I've commented out some code until I can confirm the search process works.

Private Sub Update_New_Info _Click()
'Update all entries then exit.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstForm As DAO.Recordset

Dim W4SEARCH As String
Dim W4SQLCMD As String
Dim W4SQL As String

'Get the database and recordset
Set dbs = CurrentDb
Set rst = CurrentDb.OpenR ecordset("TSSIN GLE-WEEKLY")
'Set rstForm = Forms!Form_WCE1 40_FRM.Form.Rec ordset
Set rstForm = Forms!WCE140_FR M.Form.Recordse t

'Define SQL statement
'W4SQLCMD = "UPDATE TSSINGLE-WEEKLY " & _
'(cntd)

W4SQLCMD = "UPDATE rst SET " & _
"rst![SWHIGHPOSN] = Forms!WCE140_FR M.Form!F1HIGHPO SN, " & _
"rst![SWWKSTOP30] = Forms!WCE140_FR M.Form!F1WKSTOP 30, " & _
"rst![SWWKSTOP75] = Forms!WCE140_FR M.Form!F1WKSTOP 75, " & _
"rst![SWFIRST30] = Forms!WCE140_FR M.Form!F1FIRST3 0, " & _
"rst![SWHIGHYEAR] = Forms!WCE140_FR M.Form!F1HIGHYE AR, " & _
"rst![SWWK30YEAR] = Forms!WCE140_FR M.Form!F1WK30YE AR, " & _
"rst![SWLEFT30] = Forms!WCE140_FR M.Form!F1LEFT30 , " & _
"rst![SWLEFT75] = Forms!WCE140_FR M.Form!F1LEFT75 , " & _
"rst![SWYEAR] = Forms!WCE140_FR M.Form!F1YEAR " & _
"WHERE rst![SWRECID] = Forms!WCE140_FR M.Form!SWRECID"

'"IIf (Forms!WCE140_F RM.Form!F1LEFT7 5' <> Null And rst![SWYEAR] = Null, " & _
'"rst![SWYEAR] = Year(Forms!WCE1 40_FRM.Form!F1L EFT75), rst![SWYEAR]) " & _


'Process all records
DoCmd.GoToRecor d , , acFirst
rstForm.MoveFir st

Do While Not rstForm.EOF

'Find record via RRN
W4SEARCH = "[SWRECID] = Forms!WCE140_FR M.Form!SWRECID"

' Set rst = CurrentDb.OpenR ecordset("TSSIN GLE-WEEKLY")
' Set dbs = CurrentDb

W4SQL = ""
W4SQL = W4SQL & "SELECT * FROM TSSINGLE-WEEKLY "
W4SQL = W4SQL & "WHERE TSSINGLE-WEEKLY.SWRECID = " & Forms!WCE140_FR M.Form!SWRECID

' MsgBox W4SQL
' CurrentDb.Execu te W4SQL
' DoCmd.RunSQL W4SQL
' rst.FindFirst rst![SWRECID] = Forms!WCE140_FR M.Form!SWRECID

' DoCmd.FindRecor d W4SEARCH

' rst![SWHIGHPOSN] = Forms!WCE140_FR M.Form!F1HIGHPO SN
' rst![SWWKSTOP30] = Forms!WCE140_FR M.Form!F1WKSTOP 30
' rst![SWWKSTOP75] = Forms!WCE140_FR M.Form!F1WKSTOP 75
' rst![SWFIRST30] = Forms!WCE140_FR M.Form!F1FIRST3 0
' rst![SWHIGHYEAR] = Forms!WCE140_FR M.Form!F1HIGHYE AR
' rst![SWWK30YEAR] = Forms!WCE140_FR M.Form!F1WK30YE AR
' rst![SWLEFT30] = Forms!WCE140_FR M.Form!F1LEFT30
' rst![SWLEFT75] = Forms!WCE140_FR M.Form!F1LEFT75
' If Forms!WCE140_FR M.Form!F1LEFT75 <> Null And rst![SWYEAR] = Null Then
' rst![SWYEAR] = Year(Forms!WCE1 40_FRM.Form!F1L EFT75)
' End If
' rst.Update

' Set rstForm = Forms!WCE140_FR M.Form.Recordse t
' DoCmd.GoToRecor d , , acNext

' DEBUG - Show record ID for Form and for Table
MsgBox "Form RRN is " & Forms!WCE140_FR M.Form!SWRECID & "File RRN is " & rst![SWRECID]
rstForm.MoveNex t
' MsgBox "Record ID is " & Forms!WCE140_FR M.Form!SWRECID
Loop

'MsgBox "SQL is " & W4SQLCMD
'DoCmd.RunSQL W4SQLCMD

'Exit
DoCmd.Close

End Sub

[cactusdata]

Why don't you bind the form to the table?
That will leave you with zero code.