Loop Through Records Access VBA

Question

I am attempting to classify certain individuals into "Flights". There can be no more than 10 people in a flight, and I am looping through a recordset to determine which flight each individual should be in based on the count of the number of people in the existing flights.

So it should ideally look at the table, do a dynamic count of the number of people in Flight 1 if that number is <=10 then go ahead and write the record, if not increase the flight number and go again until all the athletes are assigned.

The recordset doesn't seem to indicate when its reached the EOF and keeps jumping back to the Move First, and its looping through the records endlessly.

Option Compare Database

Function TrampUpdate()
Dim DB As Database
Dim Session1 As DAO.Recordset
Dim Session1Master As DAO.Recordset
Dim Flight As Integer
Dim AthleteID As String
Dim FlightCount As Integer
Set DB = CurrentDb()
Dim SSql As String





Set Session1 = DB.OpenRecordset("qrySession1Tramp")

Session1.MoveFirst
 Flight = 1


Do While Not Session1.EOF
    AthleteID = Session1.Fields("AthleteID")
 SSql = "Insert Into tblSession1(AthleteID, TrampFlight) Values('" & 
AthleteID & "',  " & Flight & ")"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (SSql)
    DoCmd.SetWarnings True


   FlightCount = DCount("[TrampFlight]", "tblSession1", "[TrampFlight] = " & 
Flight & "")

   If FlightCount >= 10 Then
   Flight = Flight + 1
   End If
   Session1.MoveNext

Loop



Session1.Close

End Function

I have tried it a variety of ways, and at this point, I'm stuck as to why it's not working correctly. I feel like its something super obvious but I've been looking at this so long I can't tell anymore.

At the end of the loop all the records (15 or so) should be written to the new table with a flight, 10 of them will be in Flight 1 and 5 should be in flight 2. Right now it's duplicating them and just adding them 1-10 over and over and over again.

Thanks for your help in advance.

You say "15 or so" - is it 15 or can it be more or less? What should happen if more or less? The recordset will always return "15 or so" records? — June7
It can be more or less. If more it should continue until the end of file for the recordset. Essentially I want to assign every item in that recordset to a flight regardless of how big the recordset is. So if the recordset contains 20, then we would have 20 records, 10 for Flight 1, 10 for Flight 2 but no more than 10 for each Flight number. — CNH Meal Prep
And if there are 21 records, which flight gets 11? Never more than 10 for flight 1? — June7
Each Flight gets 10 but no more than 10, so if there are 21 records Flight 1 would have 10, Flight 2 would have 10, Flight 3 would have 1. — CNH Meal Prep

June7 June7 · Accepted Answer · 2017-12-03T00:23:58

Consider:

Flight = 1
While Not rs.EOF
    If FlightCount = 10 Then
        Flight = Flight + 1
        FlightCount = 0
    End If
    FlightCount = FlightCount + 1
    If Not rs.EOF Then
        CurrentDb.Execute "Insert Into tblSession1(AthleteID, TrampFlight) Values('" & AthleteID & "',  " & Flight & ")"
        rs.MoveNext
    End If
Wend

Loop Through Records Access VBA

1 Answers