Updating Tables from a Batch Transaction File

[DMHCCI]

Does anyone have a generic coding example or an online resource of a coding example that will do the following:

1) Read a record/row in TABLE_A (Transaction File).
2) Read a matching record/row in Table_B (Master File) based on a common key.
3) Perform Calculations Using fields from both records and addtional work fields in memory, and updating some fields in Table_B.
4) Add a record/row to Table_C (BillFile) showing the results of the calculations.
5) Rewriting the updated record/row in Table_B and DELETING the record in Table_A.
6) Loop back and get the next record/row in step 1 until EOF/End of Table.
7) End of Routine - "Display Completed

 

[pcgram]

Here is a piece of code I use when I proces a series of records. Maybe this will inspire u:

Sub runrecs()

Dim dbs As Database
Dim rs As Recordset
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("[Query A and B]")

With rs

Do While Not .EOF
.Edit
!field_in_B = "Some value"
.Update
.MoveNext
Loop

End With
rs.Close
dbs.Close

End Sub

In your case, however, I would consider to use a series of queries. At first I would build them using the query-builder and then I would cut-and-paste the SQL-code into a number of sentences like:
DoCmd.RunSQL "SELECT * INTO temp2 FROM [Table A]"

 

[DMHCCI]

Thanks
I see what you are doing but am unclear on the following:

Do I need a separate Set for each query?

Set rsA = dbs.OpenRecordset("[Query A]")
Set rsB = dbs.OpenRecordset("[Query B]")

And if so how would I alter your code to make sure I have matching records in Query A and Query B at the same time?

Also do I use a DoCmd to do the delete as well or is there a

.delete

If you would be so kind to expand on this a little, I am a little new at record set manipulation with code.

Thanks