Why does my Access 2007 Form fail to see the full underlying recordset?

Posted on 2008-11-10
Medium Priority
Last Modified: 2013-12-05
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.

Question by:colinasad
  • 2
  • 2
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 600 total points
ID: 22926291
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")
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 900 total points
ID: 22930754
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?

Author Comment

ID: 22934343
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.
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 900 total points
ID: 22935191
> 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.

Author Closing Comment

ID: 31515290
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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question