访问VBA:使用`do while`循环更新记录集 [英] Access VBA: using `do while` loop to update a recordset

问题描述

我想使用 while 循环获得结果.但是，我的结果仅给出一条记录...

I would like to get a result using a do while loop. However, my result gives only one record...

我想做的是:

1. 浏览 rs (记录集)记录
2. 检查 rs 中的值是否等于 rs2
3. 如果是这样，请将 username 从 rs 复制到 rs2

4. 移至下一条记录

1. Move through rs (record-set) records
2. Check if a value in rs is equal to rs2
3. If so, copy the username from rs to rs2

4. Move to the next record

Do While Not rs.BOF                                  ' No of records in rs     
    Do While Not rs2.EOF                             ' No of records in rs2          
        If Trim(rs2![pic_no]) = Trim(rs![pic]) Then            
            rs![UserID] = rs2![NEW_USER]
            rs2.MoveNext
            rs.Update   
        Else
            rs2.MoveNext
            rs.Update    
        End If
    Loop          

    rs.MovePrevious
    rs.Update
Loop

推荐答案

Do While Not rs.EOF                                  ' No of records in rs
    Do While Not rs2.EOF                             ' No of records in rs2
        If Trim(rs2![pic_no]) = Trim(rs![pic]) Then
            MsgBox rs!UserID
            rs.Edit
            rs.Fields("UserID") = rs2![NEW_USER]
            rs.Update
        End If
    rs2.MoveNext
    Loop
rs2.MoveFirst
rs.MoveNext
Loop

rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing

End Sub

但是为什么不简单地使用更新语句呢?假设您有两个表，分别称为TableUser(在rs中调用的表)和TableNewUser(在rs2中引用的表).您的更新声明想:

But why don't you simply use an update statement? Say you have two tables called TableUser (table you tefer to in rs) and TableNewUser (table you refer to in rs2). Your update statement would like like:

UPDATE TableUser, TableNewUser
SET TableUser.UserID = TableNewUser.NEW_USER
WHERE TableUser.pic = TableNewUser.pic_no;

容易得多.您也可以在VBA代码中添加此更新语句(如果有必要/理由).

Much easier. You can put this update statement in VBA code too (if there's a need/reason to do so).

这篇关于访问VBA:使用`do while`循环更新记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！