Do Loop syntaxt (Access97 SR2, XP SR1)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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… )

Reply | Quote

Oops… That isn’t going to work quite right either… 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!

Reply | Quote

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.

Reply | Quote