Unanswered: Finding field value of next record in recordset
Using VB6 and and Access Database, I want to find the value of a field in the
record following the one being used. Here's the setup:
Access table name: tblOrders
Table Fields: OrderID, OrderFilled, and other fields omitted for brevity
OrderID is a long integer (autonumber)
OrderFilled is a boolean (Yes/No)
I use the following to open an ADO recordset and show the unfilled orders:
Dim rst as New ADODB.RecordSet
' open the recordset from an active connection (cnConnection)
rst.open "SELECT * FROM tblORDERS WHERE OrderFilled=False", cnConnection, adOpenDynamic, adLockOptimistic
' bind the recordset to the form's ADODC1 control
set ADODC1.Recordset = rst
When the user changes the value of OrderFilled to True,
I refresh the recordset to fetch only those records where OrderFilled=False.
I want to show the next record in the recordset (after the one that was just changed by the user). How do I get the next value of OrderID without moving the display data forward and back?
The OrderID's are not consecutive long integers.
I also need to keep in mind that if there are no records where OrderFilled=False,
I should show a MsgBox to this effect and unload the data display form.