do while--loop--endwith

  • Thread starter Thread starter BurtArkin
  • Start date Start date
B

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.
 
That is because Me.HowManyYears is not changing values. At some point in the
code you need to increment or decrement the values.
 
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:
Click to expand...

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]
 
Back
Top