Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58

    Unanswered: Not looping through recordset

    I am trying to loop though the recordset and pull distinct dates and populate the parent node with the values. It is populating the tree with the same date from the 1st record in the recordset. I have attached the code below.

    Thanks..

    Code:
    Public Sub PopulateTree()
    
    Dim nd As Node
    Dim strKeyParent As String
    Dim strTextParent As String
    Dim strKeyChild As String
    Dim strTextChild As String
    Dim strUser As String
    Dim strDate As String
    
    strUser = Environ("UserName")
    
    Set x = Me.tvSavedSnapshot.Object
    x.Indentation = "20"
    x.Nodes.Clear
    
    Set dbconn = CurrentProject.Connection
    Set rstDate = New ADODB.Recordset
    
    strSQLDate = "SELECT DISTINCT date, [user] FROM tblSnapshot " & _
                 "WHERE [user] = '" & strUser & "' " & _
                 "ORDER BY date"
    
    rstDate.Open strSQLDate, dbconn, adOpenForwardOnly, adLockOptimistic
    
    strDate = Format(rstDate("Date"), "mm/dd/yyyy")
    
    'Loop through recordset and add all unique dates
    While Not rstDate.EOF
        strKeyParent = "P" & UniqueKey
        strTextParent = strDate
        Set nd = x.Nodes.Add(, , strKeyParent, strTextParent)
        nd.Tag = strKeyParent
    
        Set rstDescription = New ADODB.Recordset
    
        strDescription = "SELECT description, date FROM tblSnapshot " & _
                         "WHERE date = '" & (rstDate.Fields("date")) & "'" & _
                         "ORDER BY date"
    
        rstDescription.Open strDescription, dbconn, adOpenForwardOnly, adLockOptimistic
    
        While Not rstDescription.EOF
            strKeyChild = "Ch" & UniqueKey
            strTextChild = rstDescription("Description")
            Set nd = x.Nodes.Add(strKeyParent, tvwChild, strKeyChild, strTextChild)
            nd.Tag = strKeyChild
            rstDescription.MoveNext
        Wend
    
        rstDate.MoveNext
    Wend
    
    rstDate.Close
    rstDescription.Close
    
    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    You may need to put an rstDate.MoveFirst prior to entering that loop. I suspect it is on the last record when it goes in for that loop, whereas at the first record for your NEW rstDescription, cause it was BFN (brand spanking new).

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Date is a reserved word ... Where you have it in your SQL statement you need to qualify it so that it's treated as column name ( i.e. [Date]) ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58
    Thanks. The problem was where I was formating the date...

    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •