Trying to loop through a five record recordset and getting an infinite loop

Question

Here is my code. When it runs, count goes to 32,767. The procedure is only working with 5 records.

I tried MoveLast and MoveFirst together with no result.

strSQL = "SELECT * FROM People WHERE people.household_id=" & ziprec!household_id
Set peoplerec = CurrentDb.OpenRecordset(strSQL)
With peoplerec
    While (Not peoplerec.EOF)
        currentage = DateDiff("yyyy", !birthday, rightnow)
        Select Case currentage
            Case 0 To 4
                age_under5 = age_under5 + 1
            Case 5 To 17
                age_5to17 = age_5to17 + 1
            Case 18 To 64
                age_18to64 = age_18to64 + 1
            Case Else
                age_over65 = age_over65 + 1
        End Select
        count = count + 1
        .MoveNext
    Wend
End With

The code should go through the five records and count the number of people in each age group. It does go through all five records, but endless times.

As it stands at this moment, this is not a complete code-sample - for example, there are references to variables that have no declarations. My suspicion is that your entire query process is being executed infinitely, but we can't see what's wrapped around it to judge conclusively. — Eight-Bit Guru
– Eight-Bit Guru, Commented Jun 30, 2019 at 19:51
While (Not peoplerec.EOF) should be While Not .EOF inside the With expression. — Lee Mac
– Lee Mac, Commented Jun 30, 2019 at 22:22
I didn't put the whole app because it is large and this is where the problem is. The only external variable is ziprec!household_id, which works correctly. This section gets an ID number and then gets 5 people in the family. The problem is when this loop keeps running instead of seeing an end of file after the 5 items. — Bob Smith
– Bob Smith, Commented Jun 30, 2019 at 22:35
@LeeMac, shouldn't make a difference that recordset variable is repeated - it's unnecessary but shouldn't hurt. What is ziprec - another recordset that is being looped? How many households are there? Why would there be five people in household? I also think there is more code we need to see. These calcs can probably be done in aggregate query or with DCount() in textboxes. — June7
– June7, Commented Jun 30, 2019 at 23:13

Van Ng · Accepted Answer · 2019-07-01 06:15:37Z

Try to debug it like this (in immediate window Ctrl + G in VBA editor):

strSQL = "SELECT * FROM People WHERE people.household_id=" & ziprec!household_id
Set peoplerec = CurrentDb.OpenRecordset(strSQL)
Debug.Print "strSQL is: " & strSQL
peoplerec.MoveLast
Debug.Print "peoplerec recordcount is " & peoplerec.RecordCount
peoplerec.MoveFirst
With peoplerec
    While (Not peoplerec.EOF)
        currentage = DateDiff("yyyy", !birthday, rightnow)
        Select Case currentage
            Case 0 To 4
                age_under5 = age_under5 + 1
            Case 5 To 17
                age_5to17 = age_5to17 + 1
            Case 18 To 64
                age_18to64 = age_18to64 + 1
            Case Else
                age_over65 = age_over65 + 1
        End Select
        count = count + 1
        .MoveNext
    Wend
End With

Because I'm not sure, that there is 5 records exactly

Collectives™ on Stack Overflow

Trying to loop through a five record recordset and getting an infinite loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related