movefirst moves to next record - a hair puller!

G

Guest

I am not binding my table to the form. Prefer the learning experience of
coding everything myself. When I initially populate the text boxes on the
form with data from the first record, I get data from the next record ( or
the last record as there are only two records in the table for now). I then
rewrite to MoveLast ( the opposite of above) and it returns the first record!
The Index of the table is the auto number field SessionID which the first
record is 1 and the next record is a greater number. In fact all the indexes
should return the first record.

Public Function PopulateForm(frm As Form) As Boolean

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblSessions")

If rec.RecordCount = 0 Then
PopulateForm = False
Exit Function
End If
rec.MoveFirst
frm!txtSessionDate.Value = rec("SessionDate")
frm!txtGame.Value = rec("Game")
frm!txtHours.Value = rec("Hours")
frm!txtWinLoss.Value = rec("WinLoss")
frm!txtCasino.Value = rec("Casino")
frm!txtBankroll.Value = rec("Bankroll")
curLastBank = rec("Bankroll")
If Not rec("BankUpdate") Then
rec("BankUpdate") = True
End If
frm!txtSessionID.Value = rec("SessionID")
Debug.Print rec("sessionID")
PopulateForm = True

End Function

Here's the data from the table:
SessionID SessionDate Game Hours WinLoss Casino Bankroll BankUpdate
1 Monday, July 25, 2005 Limit Holdem: Limit 8/16 1.5 $167.00 Bicycle
Casino $10,167.00 Yes

11 Tuesday, July 26, 2005 Limit Holdem: Limit 6/12 1.25 $154.00 Hawaiian
Gardens Casino $10,321.00 Yes

Anyone have any ideas? As it is, I am unable to write the record navigation
code to attach to the form buttons. First Previous Next Last New that
disables and enables the buttons based on where the record pointer is at.
 
D

Dirk Goldgar

DaBartman said:
I am not binding my table to the form. Prefer the learning experience
of coding everything myself. When I initially populate the text boxes
on the form with data from the first record, I get data from the next
record ( or the last record as there are only two records in the
table for now). I then rewrite to MoveLast ( the opposite of above)
and it returns the first record! The Index of the table is the auto
number field SessionID which the first record is 1 and the next
record is a greater number. In fact all the indexes should return the
first record.

Public Function PopulateForm(frm As Form) As Boolean

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblSessions")

If rec.RecordCount = 0 Then
PopulateForm = False
Exit Function
End If
rec.MoveFirst
frm!txtSessionDate.Value = rec("SessionDate")
frm!txtGame.Value = rec("Game")
frm!txtHours.Value = rec("Hours")
frm!txtWinLoss.Value = rec("WinLoss")
frm!txtCasino.Value = rec("Casino")
frm!txtBankroll.Value = rec("Bankroll")
curLastBank = rec("Bankroll")
If Not rec("BankUpdate") Then
rec("BankUpdate") = True
End If
frm!txtSessionID.Value = rec("SessionID")
Debug.Print rec("sessionID")
PopulateForm = True

End Function

Here's the data from the table:
SessionID SessionDate Game Hours WinLoss Casino Bankroll BankUpdate
1 Monday, July 25, 2005 Limit Holdem: Limit 8/16 1.5 $167.00 Bicycle
Casino $10,167.00 Yes

11 Tuesday, July 26, 2005 Limit Holdem: Limit 6/12 1.25 $154.00
Hawaiian Gardens Casino $10,321.00 Yes

Anyone have any ideas? As it is, I am unable to write the record
navigation code to attach to the form buttons. First Previous Next
Last New that disables and enables the buttons based on where the
record pointer is at.
Click to expand...

I hope you enjoy your "learning experience", because it's sure doing
things the hard way. As for your question, who determines what is the
"first record", and what is the "second record"? You haven't specified
any sort order in your recordset, so Access isn't bound to present the
records in any particular order.

If your table design specifies SessionID as the primary key of the
table, I'd *expect* a recordset opened directly on the table to return
records in primary-key order, which would be SessionID order, because
that's what Access normally does. So I'm a little surprised that it
doesn't seem to be happening in this case. Are you sure you specified
SessionID as the *primary key* of the table, not just as any old indexed
field?

Still, there's nothing in your code that specifies the recordset's sort
order, so Access is allowed to return the records in any order it finds
convenient. If you want the records returned in SessionID order, and
setting SessionID as the table's primary key doesn;t do it, you should
change this:
Set rec = db.OpenRecordset("tblSessions")
Click to expand...

to this:

Set rec = db.OpenRecordset( _
"SELECT * FROM tblSessions ORDER BY SessionID")
 
Top