recordset holds values?

[solun]

I'm using a recordset to run thru a bunch of records and evaluate various fields within each record. Access seems to be holding the value of fieldZ of recordA if recordB's fieldZ are not populated.

Is this possible? And what can i do to clear it?

Thanks,

Heidi

 

[PHV]

Any chance you could post your code as what you say isn't normal behaviour of correctly used Recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[solun]

I didn't think it was normal

Okay, here's code part 1:

'Get all loan records for this audit
strSQL = "SELECT * FROM Loans WHERE [auditid]= " & aid
Set rs = CurrentDb.OpenRecordset(strSQL)

' cycle thru loan records
If rs.RecordCount Then
x = 0
rs.MoveFirst
Do While Not rs.EOF
lid = rs!LoanID
ltid = rs!osiLoanTypeID
errid = aid & lid
MsgBox lid
Select Case ltid

--
Code part 2:

Case 2 'COLLATERAL MORTGAGE
strSQL = "SELECT * FROM [2zCollateralMortgage] WHERE [AuditID] = " & aid
Set rs3 = CurrentDb.OpenRecordset(strSQL)
If rs3.RecordCount Then
GoSub ApplicationA
GoSub Properties 'includes flood, insurance, appraisal, titles
GoSub TILeval
GoSub Mortgage
GoSub Rescission
GoSub HUD1
GoSub LTV
End If

---
Code part 3:

TILeval:
nTIL = nTIL + 1
If rs3!TILDiscl = 1 Or IsNull(rs3!TILDiscl) Then
strMsg = strMsg & "Truth-in-lending form is missing." & vbCrLf
nTILErr = nTILErr + 1
End if

---
Code part 4:

'Close recordsets & get next record
rs3.Close
Set rs3 = Nothing
rs.MoveNext
---


When i step into with record #59, rs3!TILDiscl =2, which the value of the last Case 2, record #50, not 1, the value that is in the table.

 

[JerryKlmns]

Dim strSQL1 As String
Dim strSQL2 As String
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim fld1 As DAO.Field
Dim fld2 As DAO.Field

strSQL1 = "SELECT Loans.* FROM Loans WHERE [auditid]= " & aid
Set rs1 = CurrentDb.OpenRecordset(strSQL)
Set fld1 =rs.Fields("LoanID")
Set fld2 =rs.Fields("osiLoanTypeID")

While Not rs1.EOF
rs1.MoveFirst
lid = Nz(fld1)
ltid = Nz(fld1)
errid = aid & lid
MsgBox lid
Select Case ltid
Case 2 'COLLATERAL MORTGAGE
strSQL2 = "SELECT * FROM [2zCollateralMortgage] WHERE [AuditID] = " & aid
Set rs2 = CurrentDb.OpenRecordset(strSQL2)
If Not rs2.BOF And Not rs2.EOF Then
GoSub ApplicationA
GoSub Properties 'includes flood, insurance, appraisal, titles
GoSub TILeval
GoSub Mortgage
GoSub Rescission
GoSub HUD1
GoSub LTV
End If
nTIL = nTIL + 1
If rs2!Field("TILDiscl") = 1 Or rs2!Field("TILDiscl") & "" = "" Then
strMsg = strMsg & "Truth-in-lending form is missing." & vbCrLf
nTILErr = nTILErr + 1
End if
rs2.Movenext
Wend
rs2.close
set rs2 =Nothing
Case Else
End Select
rs1.MoveNext
Wend

rs1.Close
Set rs1 = Nothing

I tried to clear things up but you should have posted more of your code...