Nesting 2 differnet loops help required. (Longish thread)

[tigersden]

I am trying to modify the code below, at present it loops through ADOrs1 & writes a new review date to tblReview for each record based on a value in a text box.
I need to be able to loop through ADOrs1 as before but keep looping until the review date written to tblRebview is less than a given date such as Now().
The new date will be based on adding the value in ADOrs1 ReviewFreq in months to the last review date for each record in tblReview.
The problem is I need the records written to tblReview to be witten by RiskRef descending (Loop through ADOrs1 then looop again & again while the date in tblReview is less than Now())eg:

ADOrs1 contains records with RiskRef numbered sequentially 1 to 228

tblReview expected / needed result:
ReviewRef RiskRef ReviewDate
1 1 01/01/2000
2 2 02/01/2000
3 3 02/01/2000
etc.. etc.. etc..
229 1 01/01/2001
230 2 02/01/2001
231 3 02/01/2001

How can I keep restarting the loop through ADOrs1 while the review date is less than todays date??
Thanks in advance
Tim
Existing code:
'Open a recordset to store selected fields
Set ADOrs1 = New ADODB.Recordset
ADOrs1.Open _
Source:=strSQL, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
On Error Resume Next

'Count number of records to be copied
intCount = ADOrs1.RecordCount

'Move to first record of recordset
ADOrs1.MoveFirst


'Open a second recordset which will be written back to tblReview
Set ADOrs2 = New ADODB.Recordset
ADOrs2.Open _
Source:="TEST_tblReview", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
On Error Resume Next

'Stop the loop when at end of recordset
Do Until ADOrs1.EOF

'Get the next ReviewRef Reference Number
intRevRecNum = Nz(DMax("ReviewRef", "TEST_tblReview")) + 1

'Write data to second recordset
ADOrs2.AddNew
ADOrs2("Dept") = NewData
ADOrs2("ReviewRef") = intRevRecNum
ADOrs2("ReviewDT") = Me.txtReviewDT
ADOrs2("RiskRef") = ADOrs1!ID
ADOrs2("AssessRef") = ADOrs1!AssessorRef
ADOrs2("Impact") = ADOrs1!Severity
ADOrs2("Likelihood") = ADOrs1!Probability

'Write second recordset to tblReview
ADOrs2.Update

'Move to next record in first recordset
ADOrs1.MoveNext

'Show user we are busy
DoCmd.Hourglass True

Loop

'Show user we have finished our work
DoCmd.Hourglass False

'Destroy both recordsets
ADOrs2.Close
ADOrs1.Close
Set ADOrs2 = Nothing
Set ADOrs1 = Nothing

 

[markphsd]

Look up DateAdd. It's the function to check if you are <=> then in a date

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.