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.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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I think you'd be far better off opening a recordset to get your Count than you would be using the Form's Recordset. You could use the AbsolutePosition, of course, but I'd get the Count like this:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT Count(*) As RecCount FROM YourTable", CurrentProject.Connection

Me.txtRecordCount = rst("RecCount")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vadim RappCommented:
I tried this on my Access 2003 / sql 2000; I can explain the first part - why Access shows lower number on the first record. It's because your oncurrent code runs while Access is still fetching the records from the database, so the number represents the number of records fetched at the moment. Perhaps you can fine-tune your code by looking at the various properties of the recordset, looking for the one that would tell if it's already complete or not. If there's none, then I would code  a loop like this:

z = -99
do until recordset.recordcount=z
   z = recordset.recordcount
label.caption = z

(it does not explain though why this does not happen on the 1st run - in my case, it does, of course).

As for the effect of not going past initially-fetched record, I could not reproduce it; and actually, I don't understand how it's related to the fact that you assign label.caption some value - I mean, even if you "looked" at recordset.recordcount, why does it "fix" it for Access as some crossing line? I suspect this may be something in your own code that prevents going beyond the value you fetched - look carefully. Does it happen if you remove this code?
colinasadAuthor Commented:
Thanks for your suggestions.
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.
Vadim RappCommented:
> if Me.Recordset.RecordCount isn't actually changed in the "doevents" session we still stop prematurely

More robust way would be to engage form's timer, with interval, say 100ms, comparing recordcount to the one previously saved in static variable.

> 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.

In Access 2002, looking at the recordset was causing crash of Access soon after. That is, statement like N = recordset.recordcount runs, and Access is doomed - you make couple of clicks here and there, and it crashes. In 2003, this was fixed, but maybe something still remains. Though we don't know for sure, Access seems to maintain some parallel set of objects internally, and probably synchronizes them with the "public" objects like recordset etc. See for instance my comment at http:Q__23888696.html#22916925 - this is obvious proof that  Access using for its native controls (navigation buttons in this case, filtering in that one etc.) not what's exposed to the user, but something else. And wherever there's synchronization, there are bugs. I would definitely open support incident with Microsoft professional support and pressed not just for some workaround but for complete explanation. Since Access is obviously working here not as expected, it's safe to say that support fee would be refunded in the end.
colinasadAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.