do while--loop--endwith

[BurtArkin]

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.

 

[Klatuu]

That is because Me.HowManyYears is not changing values. At some point in the
code you need to increment or decrement the values.

 

[John W. Vinson]

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]