Why does my Access 2007 Form fail to see the full underlying recordset?
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.AbsolutePosit
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.
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.AbsolutePosit
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry I've been away from this for a week or so. Thanks for your time and thoughts; I've tried to share the points fairly. I still haven't fully solved my problems but your suggestions will let me develop some sort of workarounds. Many thanks.
ASKER
I tried vadimrapp1's loop that waits until things settle down and that seemed to help but was not 100% successful. I guess if Me.Recordset.RecordCount isn't actually changed in the "doevents" session we still stop prematurely. I placed this code at the very end of my Form's "On_Current" procedure to try and make it happen as late as possible but it still often gave wrong readings when the first record was displayed.
I guess because I'm doing this "On_Current", it has definetely sorted itself out by the time we move to the second record.
However I still have this strange effect that if my reading of Me.Recordset.RecordCount is wrong on record 1, even though it is corrected at record 2, an attempt to travel to the "Last" record only ever takes me to near the originally wrong recordcount. (I've tried it with a new command button that does a very simple "DoCmd.GoToRecord , , acLast" call).
This is puzzling for two reasons :
1. My "reading" of Me.Recordset.RecordCount is corrected when a second record is displayed and as far as I'm aware, I'm not interfering with the recordset, merely reading a couple of its values (RecordCount and AbsolutePosition) and storing them in a couple of text boxes. I use these values purely for "display" and do not work with them at all.
2. This jump to the "Last" record always works OK if I am displaying the Form's "Navigation Buttons", even if I had the wrong values displayed in record 1. I don't understand how "seeing" Access's values would have such an effect, when I've also read the same values by the time I'm in record 2.
Unless anyone can come up with any other suggestions I will just have to live with displaying the Form's "Navigation Buttons" (to allow the jump to "Last" record) and either remove my own record counters (because Access's will be dispayed now anyway) or explain to my users that with a large number of records, the count isn't always correct on the first record.
Many thanks again. Colin.