Why does my Access 2007 Form fail to see the full underlying recordset?
Posted on 2008-11-10
I am trying to develop an Access 2007 (.adp) "project" as a front-end to tables held in a SQL Server 2005 Express database, but am encountering problems when working through records in a Form.
My "TBL_Stock" Table currently has 25,733 stock item records in it. In my Access "frmStock" Form's "On-Current" procedure I set my own "record counters" fields as :
Me.txtCurrentRecordNumber = Me.Recordset.AbsolutePosition
Me.txtRecordCount = Me.Recordset.RecordCount
When I first launch Access and open my "frmStock" Form, these values agree with those shown in the bottom border of the form in the "Navigation Buttons" section. (ie intially "1 of 25733" then "2 of 25733" etc. as I step through the records). If I use my own "Last>>" command button
(which executes "DoCmd.GoToRecord , , acLast"), I am taken to the last record and my own record counters correctly show "25733 of 25733". Great - just as I would expect.
If I then exit from my "frmStock" Form, then immediately re-open it, my own record counting fields will show something like "1 of 6950", but Access's "navigation" values will show the correct "1 /of 25733". (My own "wrong" maximum number will vary). If I then click on my own "Next>" button in the Form, my own counters "correct" themselves to show "2 of 25733", but if I then click on my "Last>>" button, I am only taken to a record partway through the recordset, eg "9051 of 25733". If I then try my own "Next>" button, I get an Access error message telling me I "cannot go to the specified record".
Now here's the strange thing : although my "wrong" maximum record number always appears at record 1 then corrects itself at record 2, the jump to the "Last>>" record works OK if I am also showing the Access Form's "Navigation Buttons", but always seems to fail as described above when I do not show the Form's "Navigation Buttons"!
So, two issues here :
1. Why does my first record show the wrong "maximum" number (after the first session in the form)?
2. Why does my "Last>>" command only seem to work when the Form's "Navigation Buttons" property is set to "Yes", displaying Access's record counters?
Many thanks. Colin.