.AddNew to create new record??? 1

[jane30]

Hi,In my access97 table [Mater],there are [Month From](in "yyyymm" format) and [Month To] fields along with other fields. Currently, each record indicates a person receive certain amount of money during the period of [Month From] and [Month to]. Now, what I need to do is to create an individual record for each month during the period of [Month From] and [Month to]. For example, person A receive $1000 from 200207 to 200209. It currently has one record in [Mater] table. I need to create 3 separate record like person A receive $1000/3 (prorated by # of months) in 200207, receive $1000/3 in 200208, receive $1000/3 in 200209. Any suggestion how to do this?

Much thanks in advance.

 

[JoJ]

Hi Jane,

Try the code below. You might need to fine tune the calculation of the number of months:

Private Sub Jane()

Dim rstRead As ADODB.Recordset
Dim rstRite As ADODB.Recordset
Dim intMonthsCnt As Integer
Dim i As Integer

Set rstRead = New ADODB.Recordset
Set rstRite = New ADODB.Recordset

rstRead.ActiveConnection = CurrentProject.Connection
rstRite.ActiveConnection = CurrentProject.Connection

rstRead.Open "Mater", , adOpenForwardOnly, adLockReadOnly
rstRite.Open "New", , adOpenForwardOnly, adLockOptimistic

Do While Not rstRead.EOF

intMonthsCnt = rstRead.Fields("MonthTo&quot - rstRead.Fields("MonthFr&quot

For i = 1 To intMonthsCnt
rstRite.AddNew
rstRite.Fields("Name&quot = rstRead.Fields("Name&quot
rstRite.Fields("Amount&quot = rstRead.Fields("Amount&quot / intMonthsCnt
rstRite.Fields("Month&quot = rstRead.Fields("MonthFr&quot + intMonthsCnt - 1
rstRite.Update
Next i

rstRead.MoveNext

Loop

End Sub