Record sets and looping

[Knicks]

I have a nested Do Unil loop and an interior loop for table defs. I am basically comparing 2 lists and updating a list when the linking fields are the same and a criteria is met. It works as far as making an update. The problem is it only does the 1st record in the loop, so I believe it has something to do with the nesting of loops. When I put breakpoints I can see it go from the 1st loop, then immediately into the 2nd loop and then test the table defs and make the update. But it only does it once as the breakpoint then goes back to the first loop as it should but does not go into the 2nd loop ever again. Any help would be appreciated

Here is the code

Dim strTemp As String
Dim strTemp2 As String
Dim strTemp3 As DAO.Field
Dim STRsql As String
Dim strTempALL As String
Dim hyp As Hyperlink
Dim QueryName As String
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim CUI As Double
Dim CLN As Double
Dim cwre As String

Dim tdf2 As DAO.TableDef
Dim fld2 As DAO.Field

Set rs2 = db.OpenRecordset("Temp_exp_client")
Set re = db.OpenRecordset("qryRisk")
Set tdf2 = db.TableDefs("temp_exp_client")

Do Until re.EOF
cwre = re!cw_risk_exposure
CUI = re![client UID]
Debug.Print "cwre = " & cwre
Debug.Print "cui = " & CUI

Do Until rs2.EOF
CLN = rs2!cln_client_ID
Debug.Print "CLN = " & CLN
If CUI = CLN Then
For Each fld2 In tdf2.Fields
strTemp = fld2.Name
strTemp2 = "RS2!" & strTemp
Debug.Print strTemp2

If cwre = strTemp Then

Debug.Print "fldName = " & strTemp
rs2.Edit
rs2.Fields(strTemp) = -1
rs2.Update
Else
End If


Next fld2

Else
End If






rs2.MoveNext
Loop


re.MoveNext
Loop

 

[Knicks]

I got it, sorry about that.

I needed to open and close rs2 recordset within the the first re loop - I guess it won't naturally restart the 2nd loop you have to officially re-open it

 

[softhemc]

Could you not use something like

[tt]
rs2.MoveFirst
[/tt]

before
[tt]
Do Until rs2.EOF
[/tt]

 

[MajP]

 Set re = db.OpenRecordset("qryRisk")
 Set tdf2 = db.TableDefs("temp_exp_client")

 Do Until re.EOF
   cwre = re!cw_risk_exposure
   CUI = re![client UID]
   Debug.Print "cwre = " & cwre
   Debug.Print "cui = " & CUI
 
  'assuming CLN_client_ID is numeric
   Set rs2 = db.OpenRecordset("Select * from Temp_exp_client where cln_client_ID = " & CUI)

   Do Until rs2.EOF
     For Each fld2 In tdf2.Fields
       strTemp = fld2.Name
        strTemp2 = "RS2!" & strTemp
        Debug.Print strTemp2
        If cwre = strTemp Then
           Debug.Print "fldName = " & strTemp
           rs2.Edit
           rs2.Fields(strTemp) = -1
           rs2.Update
        End If
      Next fld2
      rs2.MoveNext
   Loop
   re.MoveNext
 Loop

Makes a more sense to only return a recordset with the records you need. As written this could be really inefficient if RS2 has thousands of records. In other words rs2 should only include records where CUI = CLN.