Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / January 2008

Tip: Looking for answers? Try searching our database.

do while--loop--endwith

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
BurtArkin - 31 Jan 2008 20:35 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:

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]
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.