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.OpenRecordset("TSSINGLE-WEEKLY")
'Set rstForm = Forms!Form_WCE140_FRM.Form.Recordset
Set rstForm = Forms!WCE140_FRM.Form.Recordset

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

W4SQLCMD = "UPDATE rst SET " & _
"rst![SWHIGHPOSN] = Forms!WCE140_FRM.Form!F1HIGHPOSN, " & _
"rst![SWWKSTOP30] = Forms!WCE140_FRM.Form!F1WKSTOP30, " & _
"rst![SWWKSTOP75] = Forms!WCE140_FRM.Form!F1WKSTOP75, " & _
"rst![SWFIRST30] = Forms!WCE140_FRM.Form!F1FIRST30, " & _
"rst![SWHIGHYEAR] = Forms!WCE140_FRM.Form!F1HIGHYEAR, " & _
"rst![SWWK30YEAR] = Forms!WCE140_FRM.Form!F1WK30YEAR, " & _
"rst![SWLEFT30] = Forms!WCE140_FRM.Form!F1LEFT30, " & _
"rst![SWLEFT75] = Forms!WCE140_FRM.Form!F1LEFT75, " & _
"rst![SWYEAR] = Forms!WCE140_FRM.Form!F1YEAR " & _
"WHERE rst![SWRECID] = Forms!WCE140_FRM.Form!SWRECID"

'"IIf (Forms!WCE140_FRM.Form!F1LEFT75' <> Null And rst![SWYEAR] = Null, " & _
'"rst![SWYEAR] = Year(Forms!WCE140_FRM.Form!F1LEFT75), rst![SWYEAR]) " & _


'Process all records
DoCmd.GoToRecord , , acFirst
rstForm.MoveFirst

Do While Not rstForm.EOF

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

' Set rst = CurrentDb.OpenRecordset("TSSINGLE-WEEKLY")
' Set dbs = CurrentDb

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

' MsgBox W4SQL
' CurrentDb.Execute W4SQL
' DoCmd.RunSQL W4SQL
' rst.FindFirst rst![SWRECID] = Forms!WCE140_FRM.Form!SWRECID

' DoCmd.FindRecord W4SEARCH

' rst![SWHIGHPOSN] = Forms!WCE140_FRM.Form!F1HIGHPOSN
' rst![SWWKSTOP30] = Forms!WCE140_FRM.Form!F1WKSTOP30
' rst![SWWKSTOP75] = Forms!WCE140_FRM.Form!F1WKSTOP75
' rst![SWFIRST30] = Forms!WCE140_FRM.Form!F1FIRST30
' rst![SWHIGHYEAR] = Forms!WCE140_FRM.Form!F1HIGHYEAR
' rst![SWWK30YEAR] = Forms!WCE140_FRM.Form!F1WK30YEAR
' rst![SWLEFT30] = Forms!WCE140_FRM.Form!F1LEFT30
' rst![SWLEFT75] = Forms!WCE140_FRM.Form!F1LEFT75
' If Forms!WCE140_FRM.Form!F1LEFT75 <> Null And rst![SWYEAR] = Null Then
' rst![SWYEAR] = Year(Forms!WCE140_FRM.Form!F1LEFT75)
' End If
' rst.Update

' Set rstForm = Forms!WCE140_FRM.Form.Recordset
' DoCmd.GoToRecord , , acNext

' DEBUG - Show record ID for Form and for Table
MsgBox "Form RRN is " & Forms!WCE140_FRM.Form!SWRECID & "File RRN is " & rst![SWRECID]
rstForm.MoveNext
' MsgBox "Record ID is " & Forms!WCE140_FRM.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.