help with vba altering access data

[Hootyman]

Someone please help me before I go crazy.

I am creating is an accounting table made up of a few hundred clients. I
want to be able to select the latest balance for each client ( I have already
figured out how to do this through SQL queries) But I cannot figure out how
to walk through the records, and assign the data to variables so I can use it
to create a new record with a new balance and transaction date.

I am using access 2007, and I have been trying to use ado recordsets to do
this, but I cannot even connect to the database using the recordsets. im so
confused, im not sure if i should be using ado or dao, or even which
providers, ace or jet.

Any help would be appreciated.

Can anyone help?

 

[Brian]

Something like this (my experience is with Access 2003):

'dim variables
Dim rsMyRecordset As Recordset
Dim rsSource as String
Dim Var1 as Long 'e.g. assuming it will be used for Long Integer
customerID
Dim Var2 as Currency 'e.g. assuming it will be used for text
'open recordset
rsSource = "Table1" 'source table for the information
Set rsMyRecordset = CurrentDb.OpenRecordset(rsSource, dbOpenSnapshot)
'go to beginning of recordset
rsMyRecordset.MoveFirst
'loop through recordset
Do While Not rsMyRecordset.EOF
'capture data to variables
Var1= rsMyRecordset.Fields("SomeLongIntegerField").Value
Var2= rsMyRecordset.Fields("SomeTextField").Value
'update records in other table based on the looked-up or calculated values
above
'Docmd.RunSQL "UPDATE Table2 SET Balance = " & Var2 & " WHERE CustID = " &
Var1
'move to the next customer
rsMyRecordset.MoveNext
Loop
'close the recordset
rsMyRecordset.Close

You could also just write a query that updates the Balance using a DSum to
calculate the current balance. That would avoid the VBA code, but I'm not
sure which method would be more efficient (i.e. which would process faster).