Access VBA: using `do while` loop to update a recordset

Question

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

What I'm trying to do is:

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

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

Rene · Accepted Answer · 2017-11-13 06:40:31Z

1

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

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;

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

answered Nov 13, 2017 at 6:40

Rene

1,0931 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

alan Over a year ago

Really thanks for your help. The update statement is much easier. thanks

ashleedawg Over a year ago

Welcome to Stack Overflow, Alan! Incidentally you picked a good day to join, today is Microsoft Access's 25th Birthday since version 1.0 was released Nov 13, 1992. :-)

Collectives™ on Stack Overflow

Access VBA: using `do while` loop to update a recordset

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related