Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Valentine, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Do Loop syntaxt (Access97 SR2, XP SR1)

    What am doing wrong? I am trying to print all the categories in the recordset and all the products for the current category using VBA. Basically, I am trying to loop through the recordset until EOF. Within that loop I am trying to loop through products until change of category. I am getting "Run-time error '3021'" No current record error message. I tried various method to overcome the problem by using Do Until, Do While but all the methods I used crashed on the same error. Find attached Access2000 file sample and code below.

    PS: I need to cyctle through 3 loops, the sample below and the attached file are just a practice (part of Nortwind sample) I used to play with because I could not get my database to work. It only shows two loops but I couldn't even get it to work. What I need to do is the following:

    Open Recordset
    Loop Until rst.EOF
    Save rst!LastDate
    Loop Until Saved rst!LastDate changes OR recordset is finished (rst.EOF)
    Save rst!PublicationType
    Loop Until Saved rst!PublicationType changes OR Saved rst!LastDate changes OR recordset is finished (rst.EOF)
    Do various tasks
    End Loop
    End Loop
    End Loop

    Close Recordset

    How do I put this in code? Any help would be greatly appreciated. Thank you.

    'Sample I played with for two loops (didn't work)
    Private Sub cmdTesting_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngSavedCategory As Long
    Dim lngSavedProduct As Long

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryTesting")
    Do Until rst.EOF
    lngSavedCategory = rst!CategoryID
    Me!txtCategory = Me!txtCategory & vbCrLf & rst!CategoryID & ": " & rst!CategoryName
    Do Until rst.EOF And lngSavedCategory <> rst!CategoryID
    Me!txtProduct = Me!txtProduct & vbCrLf & rst!ProductID & ": " & rst!ProductName
    rst.MoveNext
    Loop
    Me!txtProduct = Me!txtProduct & vbCrLf
    Loop
    End Sub
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Do Loop syntaxt (Access97 SR2, XP SR1)

    Nested loops are quite possible, but you have to clarify what you are trying to nest. The outer loop moves through each record in the recordset. The logic of your inner loop is incorrect. You're setting the variable lngSavedCategory equal to the recordset's CategoryID and then trying to test for [/b]Do Until rst.EOF And lngSavedCategory <> rst!CategoryID[/b], which doesn't make sense. The only way to use a nested loop on the same recordset is if you're looping through the fields collection of that recordset. Normally, nested loops work with *different* recordsets and relate those records based on shared fields.

    If you explain what you're trying to accomplish with these loops, someone will be able to help you do it.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do Loop syntaxt (Access97 SR2, XP SR1)

    I can't open the sample db since I only have Access '97 at work... but I looked at your code and one problem is that you have an AND in the second loop condition...
    Do Until rst.EOF And lngSavedCategory <> rst!CategoryID
    This means that the first time through the outer loop it's on the first record... then by the time it's done the second loop once it's at the end of the recordset...
    And... even if you had an OR in that second loop condition you'd still have problems because you never move the recordset at the bottom of the outer loop...

    From what you said you wanted to do... I've come up with this code:

    Dim db as dao.database
    Dim rst as dao.recordset
    Dim pdatLastDate as Date
    Dim pstrPublicationType as String

    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryTesting")

    rst.MoveFirst
    If Not rst.EOF Then

    Do Until rst.EOF
    pdatLastDate = rst!LastDate
    Do Until pdatLastDate <> rst!LastDate OR rst.EOF
    pstrPublicationType = rst!PublicationType
    Do Until pstrPublicationType <> rst!PublicationType OR rst.EOF
    'various tasks
    rst.movenext
    Loop
    rst.movenext
    Loop
    rst.movenext
    Loop
    End If

    Set rst = nothing
    Set db = nothing

    I added an IF statement to handle if there are no records in the recordset to begin with.... If by moving first, we are already at the end of the recordset, it won't drop into the loop... (a Do Until will always be completed once... and this would cause an error if there were no records...)

    HTH

    (P.S. This is assuming that you have the recordset sorted by the fields in question... LastDate and then PublicationType... )

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do Loop syntaxt (Access97 SR2, XP SR1)

    Oops... That isn't going to work quite right either... <img src=/S/doh.gif border=0 alt=doh width=15 height=15> I don't know what your data the query looks like... but I just created a test db and tried it myself... If the date changes while inside the publication type loop, that isn't going to be caught....

    Charlotte is right... Best to use the fields collection... Or define different recordsets, maybe using SQL statements in the code... I've always used different recordsets for nested loops...

    Post more details of the query and/or examples of what output you want and maybe we can help more...
    Have a great day!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Do Loop syntax (Access97 SR2, XP SR1)

    The problem is that you have AND in your condition, as Trudi pointed out, but also that both parts in the condition will be evaluated. If EOF is true, VBA will still try to test whether rst!CategoryID is equal to lngSavedCategory, but you can't refer to a field when EOF is true.

    A possible solution is to separate the tests. Here is code using only one loop:

    Private Sub cmdTesting_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngSavedCategory As Long
    Dim lngSavedProduct As Long

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryTesting")
    Do Until rst.EOF
    If rst!CategoryID = lngSavedCategory Then
    Me!txtProduct = Me!txtProduct & vbCrLf & rst!ProductID & ": " & rst!ProductName
    Else
    lngSavedCategory = rst!CategoryID
    Me!txtCategory = Me!txtCategory & vbCrLf & rst!CategoryID & ": " & rst!CategoryName
    Me!txtProduct = Me!txtProduct & vbCrLf
    End If
    rst.MoveNext
    Loop
    End Sub

    Another method would be to have three recordsets: the first selects distinct LastDate values, the second selects distinct PublicationType values for a particular LastDate, and the third selects records for a particular LastDate and PublicationType.

Posting Permissions

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