Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

Nested Recordset Loops

I'm trying to copy data from "parent" records to "child" records in a table (each child has a field containing its parent's ID). I'm trying to use nested Do..Until loops, but I only get the first record from the outer loop's recordset (rst). Are these recordsets conflicting somehow?

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
   
    Dim strSQL As String
    Dim strSQL2 As String
   
    Dim intAnswer As Integer
   
    Set dbs = CurrentDb

    strSQL = "SELECT * FROM ContactsTest WHERE Relation = 'Self';"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    Do Until rst.EOF

        strSQL2 = "SELECT * FROM ContactsTest WHERE SpouseOrParentID = " & rst!ContactID & ";"
        Set rst2 = dbs.OpenRecordset(strSQL2, dbOpenDynaset)

        Do Until rst2.EOF
            rst2.Edit
            rst2!CompanyID = rst!CompanyID
            rst2!Address = rst!Address
            rst2!City = rst!City
            rst2!State = rst!State
            rst2!Zip = rst!Zip
            rst2!Phone = rst!Phone
            rst2!Salesman = rst!Salesman
            rst2.Update
            rst2.MoveNext
            intAnswer = MsgBox(rst!ContactID & ":" & rst2!ContactID, vbOKCancel)
            If intAnswer = vbCancel Then Exit Sub
        Loop

        rst2.Close
        rst.MoveNext
    Loop

    rst.Close
    dbs.Close
0
brady1700
Asked:
brady1700
  • 4
  • 3
  • 2
1 Solution
 
rockiroadsCommented:
U have a where clause in the first SQL, can u double check how many records you expected?

You only got a msgbox prompt, if you cancel, you exit the procedure - not good, u are not closing down your recordsets
0
 
rockiroadsCommented:
If you are just updating, u can try this sql instead

UPDATE ContactsTest a, ContactTests b
SET a.CompanyID = b.CompanyID,
a.Address = b.Address,
a.City = b.City,
a.State = b.State,
a.Zip = b.Zip,
a.Phone = b.Phone,
a.Salesman = b.Salesman
WHERE a.SpouseOrParentID = b.ContactID

0
 
brady1700Author Commented:
rockiroads,

I know, this is just a test db. The recordsets close when they go out of scope anyway, don't they?

I don't usually work with SQL, could you put that update query in some actual sample code?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim P.Commented:
  Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
   
    Dim strSQL As String
    Dim strSQL2 As String
   
    Dim intAnswer As Integer
   
    Set dbs = CurrentDb

    strSQL = "SELECT * FROM ContactsTest WHERE Relation = 'Self';"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    If rst.EOF = False then rst.movefirst
    Do Until rst.EOF

        strSQL2 = "SELECT * FROM ContactsTest WHERE SpouseOrParentID = " & rst!ContactID & ";"
        Set rst2 = dbs.OpenRecordset(strSQL2, dbOpenDynaset)

        If rst2.EOF = False then rst.movefirst
        Do Until rst2.EOF
            rst2.Edit
            rst2!CompanyID = rst!CompanyID
            rst2!Address = rst!Address
            rst2!City = rst!City
            rst2!State = rst!State
            rst2!Zip = rst!Zip
            rst2!Phone = rst!Phone
            rst2!Salesman = rst!Salesman
            rst2.Update
            rst2.MoveNext
            intAnswer = MsgBox(rst!ContactID & ":" & rst2!ContactID, vbOKCancel)
            If intAnswer = vbCancel Then Exit Sub
        Loop

        rst2.Close
        rst.MoveNext
    Loop

    rst.Close
    dbs.Close
0
 
brady1700Author Commented:
jimpen the movefirst method doesn't seem to help.
0
 
rockiroadsCommented:
yes just do this

Dim sSql as String
 
sSql = "UPDATE ContactsTest a, ContactTests b " & _
        "SET a.CompanyID = b.CompanyID, " & _
        "a.Address = b.Address, " & _
        "a.City = b.City, " & _
        "a.State = b.State, " & _
        "a.Zip = b.Zip, " & _
        "a.Phone = b.Phone, " & _
        "a.Salesman = b.Salesman " & _
        "WHERE a.SpouseOrParentID = b.ContactID " & _
        "AND b.Relation = 'self'"

Debug.Print sSql

DoCmd.RunSQL sSql



Does this query make sense?
using table aliases (lets u use same table twice!)
a is child
b is master
0
 
Jim P.Commented:
Is  rst!ContactID a numeric filed in both tables?
0
 
brady1700Author Commented:
That works great rockiroads!

I obviously still have to learn to think in SQL instead of Basic...

:-)
0
 
rockiroadsCommented:
If it can be done in SQL, then try that first. Thats what I do, then I use VBA
Glad to have helped
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now