CODEWITHSUNDEEP
vbams-accessupdatesdo-while
alan
alan

Reputation: 1

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

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:

  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

Upvotes: 0

Views: 4571

Answers (1)

Rene
Rene

Reputation: 1093

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).

Upvotes: 1

Related Questions