VBA RecordSet if EOF

[adhoustonj]

Hey AWF,
I'm wondering how I can cycle through a set number of lets say 10 seats numbered 1-10, with 10 alphabetic names of A:J, and they are left to right.
In this case, people will shift a seat to the right, but not everyone, and I need to record their new seat number.

Is there an efficient way to do this for the below desired station_after_move compared to station_id?

I feel I need to walk a recordset with something like...

Private Sub ReOrderStationAssignments()
Dim last as Integer
Dim lastlast as Integer
strSQL = "SELECT station_id, person, order, move, station_after_move FROM tblStationPerson"
set rs = db.openRecordset(strSQL)
    with rs
        .movefirst
        if .EOF = False Then
            if .move & "" = "" Then
                last = .order + 1
                lastlast= .order
            Else
                .moveNext

So for the code below: I'm struggling because what if I had 4 & 5 as no_move... Do i Just need to do a count and then walk backwards and also count if multiple no_moves are in a row?

station_id	person	order	no_move	station_after_move
1	A	1	X	1
2	B	2		3
3	C	3		4
4	D	4		2
5	E	5	X	5

 

[Mike Krailo]

This is confusing me. Are we talking real people here? What is the overall goal you are trying to achieve? If people are sitting in seats that never move, won't you end up with people sitting on other peoples laps? What are the rules or end goal?

 

[Gasman]

Why the hard colour to read for the code?
That was very hard to read on my tablet, better on my laptop.

 

[Josef P.]

If you remove the fixed data from the recordset, you only need to move the station_id from one record to the next record.

strSQL = "select ... from tblStationPerson where move = True Order By order desc"
' .. or no_move = False ... depending on what is correct.
' It would be nice if so much effort was invested in an example that at least the data fields match.

set rs = ...
with rs
    .MoveLast
    NewStationID = .Fields("station_id").Value
    .MoveFirst
    Do while not .EOF
       .Edit
       .Fields("station_after_move").Value = NewStationID
       .Update
       NewStationID = .Fields("station_id").Value
       .MoveNext
    Loop
    .Close
end with

 

[arnelgp]

or you can just use an Update query to update your table:

update yourTable Set station_id = station_after_move;

 

[ebs17]

About the technology:

with rs     
   .movefirst
   if .EOF = False Then

The order is wrong. If the recordset is empty, a MoveLast/MoveFirst will generate an error.

 

[Gasman]

I tend to use

Do While NOT rs.EOF

 

[gemma-the-husky]

I tend to use

Do While NOT rs.EOF

I use that syntax, as if the record set is empty it still works correctly.

Repeat...until will have a problem with the first record if there are no records.

 

[adhoustonj]

That color was because it thought the code was SASS, just noticed the tag and fixed.

This is real people and stations - There is crosstraining/station rotation throughout the day, but in some cases an associate is fixed to the 1 station for the entire shift, and other stations will all rotate every 2 hours.

Thanks @Josef P.

Public Sub RollOut3_JP()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim NewStationID As Integer

Set db = CurrentDb()

strSQL = "SELECT order, station_after_move, station_id, no_move FROM tblStationPerson WHERE no_move = false ORDER BY tblStationPerson.order DESC"
Set rs = db.OpenRecordset(strSQL)
With rs
    .MoveLast
    NewStationID = .Fields("station_id").Value
    .MoveFirst
    Do While Not .EOF
       .Edit
       .Fields("station_after_move").Value = NewStationID
       .Update
       NewStationID = .Fields("station_id").Value
       .MoveNext
    Loop
    .Close
End With

strSQL = "UPDATE tblStationPerson SET station_after_move = station_id WHERE no_move = TRUE"
db.Execute strSQL

Set rs = Nothing
Set db = Nothing

End Sub

Public Sub RollOut2()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim iMin As Integer

Set db = CurrentDb()

strSQL = "SELECT station_id FROM tblStationPerson " & _
         "WHERE tblStationPerson.order = (SELECT min(order) FROM tblStationPerson WHERE no_move = false)"
Set rs = db.OpenRecordset(strSQL)
iMin = rs!station_id

strSQL = "SELECT order, station_after_move, station_id, no_move " & _
         "FROM tblStationPerson ORDER BY tblStationPerson.order DESC"
Set rs = db.OpenRecordset(strSQL)
With rs
    Do While Not .EOF
        .Edit
        If !no_move = True Then
            !station_after_move = !station_id
        Else
            !station_after_move = iMin
            iMin = !station_id
        End If
        .Update
        .MoveNext
    Loop
End With

rs.Close

Set rs = Nothing
Set db = Nothing
   
End Sub