Solved
best way to loop through a record set without creating an endless loop
Posted on 2016-09-14
i am trying to go through a record set and grab some of the values for the current record then add a new record with the values from the previous record. I am running into an endless loop and i am only getting the information from the first record that i get.
Dim db As DAO.Database
Dim rs, rs2 As DAO.Recordset
Dim strSQL, RIGACCT, ClientID, dispname, comp, acctno, bal, exp, expdel, EXPDELDATE, eq, eqdel, EQDELDATE, tu, tudel, tuddate As String
Dim pudate, crednotes, newpudate As String
Dim i As Long
strSQL = "SELECT CREDITREPORT.RIGACCT_FK, CREDITREPORT.CLIENTID_FK, CREDITREPORT.DISPLAYNAME, CREDITREPORT.COMPANYNAME, CREDITREPORT.ACCOUNTNUMBER, CREDITREPORT.BALANCE, CREDITREPORT.EXPERIAN," _
& " CREDITREPORT.EXPERIANDEL, CREDITREPORT.EXPDELDATE, CREDITREPORT.EQUIFAX, CREDITREPORT.EQUIFAXDEL, CREDITREPORT.EQDELDATE, CREDITREPORT.TRANSUNION, CREDITREPORT.TRANSUNIONDEL, " _
& "CREDITREPORT.TUDELDATE, CREDITREPORT.PULLEDDATE, CREDITREPORT.CREDREPORTNOTES, CREDITREPORT.NEWPULLEDDATE " _
& " FROM CREDITREPORT " _
& " WHERE (((CREDITREPORT.CLIENTID_FK)=" & [Forms]![SubMc_CredRptDE1Frm]![ClientID_FK] & ") and ((CREDITREPORT.PULLEDDATE)= #" & [Forms]![SubMc_CredRptDE1Frm]![PULLEDDATE_Txt] & "#));"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
With rs
If Not rs.BOF And Not rs.EOF Then
.MoveLast
.MoveFirst
Debug.Print .RecordCount
Do While Not rs.EOF
RIGACCT = rs!RIGacct_FK
ClientID = rs!ClientID_FK
DisplayName = rs!DisplayName
comp = rs!COMPANYNAME
acctno = rs!AccountNumber
bal = rs!BALANCE
exp = rs!EXPERIAN
expdel = rs!EXPERIANDEL
EXPDELDATE = rs!EXPDELDATE
eq = rs!EQUIFAX
eqdel = rs!EQUIFAXDEL
EQDELDATE = rs!EQDELDATE
tu = rs!TRANSUNION
tudel = rs!TRANSUNIONDEL
'tuddate = rs!TUDELDATE
'pudate = Date
crednotes = rs!CREDREPORTNOTES
rs.AddNew
rs!RIGacct_FK = RIGACCT
rs!ClientID_FK = ClientID
rs!DisplayName = DisplayName
rs!COMPANYNAME = comp
rs!AccountNumber = acctno
rs!BALANCE = bal
rs!EXPERIAN = exp
rs!EXPERIANDEL = expdel
rs!EXPDELDATE = EXPDELDATE
rs!EQUIFAX = eq
rs!EQUIFAXDEL = eqdel
rs!EQDELDATE = EQDELDATE
rs!TRANSUNION = tu
rs!TRANSUNIONDEL = tudel
'rs!TUDELDATE = tuddate
rs!PULLEDDATE = Date
rs!CREDREPORTNOTES = crednotes
rs.Update
rs.MoveNext
Loop
.CLOSE