Update Table Snag

[DThreadgill]

I have two tables. The one I am updating has 11 fields (CustNbr (primary), comments, comments1, comment2, etc up to 10).

The other table has only 2 fields (Custnbr, comments) and there can be one custnbr with different comments (hence the 10 fields of comments)

Here's what I have but it's updating ALL the records with the same thing:

Dim rs As Recordset
Dim rst As Recordset
Dim sql As String
Dim str As String
Dim strqry As String
Dim icount As Integer

sql = "Select * from CommentTest"
Set rs = CurrentDb.OpenRecordset(sql)

str = "SELECT comments.Field1 AS Custnbr, [comments]![Field4] & [comments].[field5] & [comments].[field6] & [comments].[field7] AS Comments " & _
"FROM comments GROUP BY comments.Field1, [comments]![Field4] & [comments].[field5] & [comments].[field6] & [comments].[field7] "
Set rst = CurrentDb.OpenRecordset(str)

While Not rst.EOF
For icount = 1 To 10
strqry = "where " & rs(0) & " = " & rst(0) & " "
sql = "UPDATE [commentTest] SET [comment" & icount & "] = '" & rst(1) & "' " & strqry & ""
'MsgBox sql
CurrentDb.Execute (sql)
Next icount
rst.MoveNext
Wend

rs.Close
rst.Close
Set rs = Nothing
Set rst = Nothing

My eyes are tired and I'm about to give up. Any help is appreciated.

[Rabbit]

I only looked at the WHERE clause so I can't say if the rest of your code is correct but:

strqry = "where " & rs(0) & " = " & rst(0) & " "

You're telling it to update if rs(0) is equal to rst(0), which will be true for every single record if the first record in rs(0) equals any of the first 10 records in rst(0).