By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

Update Table Snag

P: 57
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.
Aug 31 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,392
I only looked at the WHERE clause so I can't say if the rest of your code is correct but:
Expand|Select|Wrap|Line Numbers
  1. 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).
Aug 31 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.