programming in MSAccess using VB

i am having two tabels in my database. both the tabels are having two fields, accountno and amt. second table is having some accountno matched to first tabel. i want to update the first table amount filed with the amount of second tabel for accountno matching with the accountno of second tabel
. i want to write code for a command click event .
i am giving the code written by me. Dim db As Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset

Private Sub Command0_Click()
Set db = CurrentDb()
Set rst1 = db.OpenRecordset(" select * from aaa order by accountno")
Set rst2 = db.OpenRecordset("select * from bbb order by accountno")
rst1.MoveFirst
rst2.MoveFirst
MsgBox ("aaa" & rst1!accountno)
MsgBox ("bbb" & rst2!accountno)
Do While rst1.EOF

If rst2!accountno = rst1!accountno Then
rst2!amount = rst1!amount
rst1.Edit
rst1.Update
rst2.MoveNext
Else
rst1.MoveNext
End If

MsgBox (" bbb" & rst2!accountno)

Loop

If rst1.EOF Then
rst1.Close
rst2.Close
End If
End Sub


help in this regard may be highly appreciated.

my email id is : URL
URL
URL
URL

knrrao,
What is the relationship between the two tables? 1-1 or 1-many or many-many and what are the joining fields?

The following code should work assuming that there is zero or none matching records in table2.

rst1.MoveFirst
rst1.Edit
While NOT rst1.EOF
rst2.MoveFirst
While NOT rst2.EOF
If rst1!accountno = rst2!accountno Then
rst1!amount = rst2!amount
rst1.Update
MsgBox (" bbb" & rst2!accountno)
rst2.MoveLast
Else
rst2.MoveNext
End If
Wend
rst1.MoveNext
Wend

rst1.endEdit

Also you don't need the If clause:
If rst1.EOF Then
rst1.Close
rst2.Close
Endif

because you're at the end of the recordset anyway!
Cheers...Ed