Need to "split" records

[kgcrowther]

I have two tables:
tblOne - contains regional data (e.g., fields = Region, Class, Num1, Num2)
tblTwo - contains data to split regional numbers to smaller regions based on class and region.

I would like to use the data in tblTwo to split regions in tblOne to either permanently update tblOne to to create another tblThree. But, I have spent hours on this problem and haven't come up with a good way to solve.

Every region that splits (aprroximately 5% of records in tblOne) is split into either exactly 2 or exactly 3 records for each class in the region. I THINK I would like to:

1.) Read a record into an array. (I don't have any idea how to do this.)

2.) RecordSet.AddNew (once or twice) such that the new records are duplicates of the array in (1.) (not sure how to make them duplicates, but I only need to edit a small number of fields, the rest just need to be copied)

3.) RecordSet.Edit all 2 or three arrays to redefine Region, Num1, and Num2. (I'm pretty sure I know how to do this. I know how to edit an existing record, but I'm not sure how to keep track of 2 or 3 records at a time.)

Any advice on either method or individual steps? Thanks in advance for any ideas. I'm really not looking for a solution, just ideas. Thanks!!

 

[kgcrowther]

My prelim code work pretty good, Any advice for improvement

I used the following code. It seemed to work pretty good. Any advice for improvement?

Dim rs As DAO.Recordset
Dim oreg As Integer, dreg As Integer
Dim MyFlds(14)
Dim sctg2 As Integer
Dim d7p2 As Double, d7p3 As Double
Dim f As Integer

Set rs = CurrentDb.OpenRecordset("TblOne")
rs.MoveFirst

Do While Not rs.EOF

oreg = rs("Oregion")

If oreg = 198 Or oreg = 199 Then
For f = 0 To 14
MyFlds(f) = rs.Fields.Item(f)
Next f

If oreg = 198 Then
'First I have to convert the Class with a Bridge tbl
sctg2 = DLookup("[SCTG2]", "Bridge", "[Stcc4] = " & MyFlds(6))
d7p2 = DLookup("[d7p2o]", "TblTwo", "[Commodity] = " & sctg2)
d7p3 = DLookup("[d7p3o]", "TblTwo", "[Commodity] = " & sctg2)

'First edit the original
With rs
.Edit
rs.Fields.Item(1) = 1982
rs.Fields.Item(2) = "West South Central-PADD2"
For f = 8 To 14
rs.Fields.Item(f) = d7p2 * MyFlds(f)
Next f
.Update
End With

'Append another with the other properties
With rs
.AddNew
For f = 1 To 7
rs.Fields.Item(f) = MyFlds(f)
Next f
rs.Fields.Item(1) = 1983
rs.Fields.Item(2) = "West South Central-PADD3"

For f = 8 To 14
rs.Fields.Item(f) = d7p3 * MyFlds(f)
Next f
.Update
End With
End If
End If
rs.MoveNext
Loop

rs.Close