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

Posted on 2008-11-10
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
    LVL 84

    Accepted Solution

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

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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now