I have a table with one record which includes 2 fields--a year (1999) and a
total number of years (any single number greater than 2) I want to add lines
to the table where the year increases by one and the total number of years
decreases by one, and I want the number of lines added to the table to stop
when the total number of years =0 The code I've written is:
Dim RecSetVal As String
RecSetVal = "PlanNumber-" & Me.PlanNumber
Dim ctl As Control
Set ctl = Me.PlanNumber
Dim dbs As Database, rst As Recordset, NewId As Long
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(RecSetVal)
With rst
Do While Me.HowManyYears > 0
.MoveLast
.MoveFirst
.AddNew
!HowManyYears = Me.HowManyYears - 1
!FiscalYearStart = Me.FiscalYearStart + 1
.Update
.MoveNext
Loop
End With
dbs.Close
After the first line, the loop procedure continues adding lines, but
neglects to reach zero.
Please tell me what it is I'm missing.

Signature
Burt
Klatuu - 31 Jan 2008 21:06 GMT
That is because Me.HowManyYears is not changing values. At some point in the
code you need to increment or decrement the values.

Signature
Dave Hargis, Microsoft Access MVP
> I have a table with one record which includes 2 fields--a year (1999) and a
> total number of years (any single number greater than 2) I want to add lines
[quoted text clipped - 26 lines]
>
> Please tell me what it is I'm missing.
John W. Vinson - 31 Jan 2008 23:09 GMT
>I have a table with one record which includes 2 fields--a year (1999) and a
>total number of years (any single number greater than 2) I want to add lines
>to the table where the year increases by one and the total number of years
>decreases by one, and I want the number of lines added to the table to stop
>when the total number of years =0 The code I've written is:
How about a solution using no VBA code at all? You can do this in a Query with
the help of a little auxiliary table, Num, with one integer field N; fill it
with values from 0 through the largest number of years you'll ever need (be
generous, 10000 rows is still a very small table).
INSERT INTO yourtable
SELECT yourtable.[theyear] + N, yourtable.[totalnumber] - N
FROM yourtable, Num
WHERE N < yourtable.totalnumber
AND <whatever criteria select the record you want to duplicate>
John W. Vinson [MVP]