Problem with Nested Loop in VBA

[Jenaccess]

Hi Everyone,

This question concerns an Access 2010 database I’m creating for one of my programs at work. I’m having an issue trying to get a nested loop to work. I am very new at loops, and at working with recordsets in vba. Here’s what I’m trying to accomplish:

The user clicks a button, and is taken to a form where they can see students who have a student record, but no student reporting record. I want the user to be able to click another button and create ten reporting records for each student on the list, one for each year from 2007-2016. Here is the code that accomplishes this:

Dim rst As DAO.Recordset
Dim i As Integer
Set rst = CurrentDb.OpenRecordset("SELECT * From qryAPR WHERE StudentID=" & Me.StudentID)
If rst.EOF Then

For i = 1 To 10
With rst
.AddNew
!StudentID = Me.StudentID
!Type = 2
!BatchAY = i
.Update
End With
Next

End If
DoCmd.RefreshRecord

This works perfectly for the first student, but only the first student. If I want to create the reporting records for the other students on the list, I have to go back and click the button each time for each one. I know there has to be a way to do this all at once, but I’m not sure how. I’ve experimented with Do While Loops, but so far I can’t get anything to work. Any help would be appreciated. Thank you!

 

[pbaldy]

Might be easier if you post your failing loop code, so we can fix it. You'd nest your For/Next loop within a Do While Not rst.EOF loop. Your SQL would have to pull in more than one student of course, unless the existing StudentID criteria is pulling more than one that you want to operate on.

 

[pbaldy]

Upon reflection, I goofed that a little. I suspect you want to use the unmatched query wizard to find students who are in one table and not the other, and loop on a recordset based on that query.

 

[Jenaccess]

Thank you so much for answering. That is exactly what I did. I created an unmatched query and put the students who appeared in one table but not the other in a temp table. I then created a form based on the temp table, so that the user could see which students were being added. Then of course I had my add students button on that form, which was supposed to take those students, add them to the reporting table and create the ten new records. It works like a charm for the first student, but not at all for the rest of them.

I figured out many ways to do this wrong. It seemed to me like the Do While loop would be a good solution. I could wrap my code in one of those and just go through the records, but it either didn't work and kept adding just one student, or I'd get a can't have Loop without the Do error, or something to that effect. And it would drive me crazy because I could see the Do While was there, and had no clue why I got the error. My failing code went something like this:

Dim rst As DAO.Recordset
Dim i As Integer
Set rst = CurrentDb.OpenRecordset("SELECT * From qryAPR WHERE StudentID=" & Me.StudentID)
Set rst1=CurrentDb.OpenRecordset ("SELECT StudentID FROM tblTemp") Note-This was how I tried to get the student id's from the temp table so that my code would loop through them and add the records to the reporting table. Both tables have the studentid field; it's the pk in one and the fk in the other.

Do While Not rst1.EOF
rst1.MoveFirst
For i = 1 To 10
With rst
.AddNew
!StudentID = Me.StudentID
!Type = 2
!BatchAY = i
.Update
End With
Next

End If
DoCmd.RefreshRecord
rst1.MoveNext

I know the outer loop is failing somewhere, but I'm not familiar enough with looping to see where I'm going wrong. It seems like I'm on the right track though. I'd appreciate any help you could give me.

 

[pbaldy]

I assume you get an endless loop? Or maybe nothing, as that probably doesn't compile. This line will keep moving the recordset back to the first record:

rst1.MoveFirst

You don't want it in there. You have an End If with no If, and a Do with no Loop. You don't want to get the student ID from the form, you want to get it from the recordset.

 

[Jenaccess]

That makes sense. Yes, you're right. Sometimes I get an endless loop, and sometimes when I experiment with switching rst and rst1 just to see what would happen, I'd get no result.

"You don't want to get the student ID from the form, you want to get it from the recordset."

This was my attempt to get the studentid from the recordset, which is made up of the table underlying the form:

Set rst1=CurrentDb.OpenRecordset ("SELECT StudentID FROM tblTemp")

Does that make sense, or am I misunderstanding? Do I need the two recordsets rst and rst1 like I had? Is the Do While loop the correct solution?

The way I see it in my very limited experience is that the recordset (rst) does the work of creating the 10 reporting records I need and adds to them the reporting table, while rst1 simply should loop through all the students in the temp table and feed them to rst. It seems like the solution would be to wrap rst (inner loop For/Next) in rst1 (outer loop Do/While). Am I on the right track? It seems like I'm not too far off, but due to my lack of looping expertise, I'm having trouble getting it exactly right. I don’t know whether I’m using the wrong looping syntax altogether, or I’m just putting the loop in the wrong place.


Again, thank you so much for help!

 

[pbaldy]

If I'm reading right, rst is where the new records will go and rst1 is the source of the student ID's to add. If so, you would loop rst1, not rst. Rather than the line you quoted I meant this one:

!StudentID = Me.StudentID

which I suspect should be:

!StudentID = rst1!StudentID

 

[Jenaccess]

Thank you!

I've tinkered around with the code a bit, and this is what I've come up with. I know it's wrong because it gives me thousands of records with only the first studentID, but at least it's generating something. Would you be able to tell me where I went wrong or if I'm even on the right track? Thanks again.

Dim rst As DAO.Recordset
Dim i As Integer
Dim strSQL, stDocName, stDocName1 As String
stDocName = "frmAddStudents"
stDocName1 = "frmAPRShow"
Set rst = CurrentDb.OpenRecordset("SELECT * From qryAPR WHERE StudentID=" & Me.StudentID)
Set rst1 = CurrentDb.OpenRecordset("SELECT StudentID, Type, BatchAY FROM tblAcceptedStudentswoutAPR")


Do While Not rst1.EOF

For i = 1 To 10
With rst
.AddNew
!StudentID = rst1!StudentID
!Type = 2
!BatchAY = i
.Update

End With
Next

Loop
rst.MoveNext

 

[pbaldy]

I assume it's running endlessly? This line:

rst.MoveNext

needs to be before "Loop". It also needs to be

rst1.MoveNext

so it refers to the recordset you want to loop, not the recordset you're using to add records.

 

[Jenaccess]

That worked perfectly. I can't thank you enough!

 

[pbaldy]

Happy to help!