Record count not working properly?

[Zak14]

I've got the following code on the current event of the form to display "[CurrentRecord] of [TotalRecords]" in txtRecordCount:

    With Me
        !txtRecordCount = .CurrentRecord & " of " & .RecordsetClone.RecordCount
    End With

It used to work fine, but now, when I close and open the form, the record count shows "[CurrentRecord] of [CurrentRecord + 1]" and after a couple next record clicks, it starts working fine again.

 

[CJ_London]

to ensure you get the correct recordcount you need to go to the last record to force it to load the entire recordset.

This is apparent in large recordsets when opened in datasheet view and you have the navigation buttons visible at the bottom. The data displays very quickly but it can take a few seconds to populate the '1 of 200000' in the navigation window.

 

[pr2-eugin]

Sometimes to give a better efficiency the Recorset will only be partially loaded, after a few seconds it will load the complete rescordset. Hence it is not 100% reliable to start with, but if you really really need it, use something like.

    With Me
        .RecordsetClone.MoveLast
        .RecordsetClone.MoveFirst
        !txtRecordCount = .CurrentRecord & " of " & .RecordsetClone.RecordCount
    End With

Be warned, this might make the loading of the form a bit slow !

 

[Zak14]

I don't want it to be too slow. How is it that Access can immediately display the record count in the navigation bar, but not here.

 

[pr2-eugin]

I don't want it to be too slow. How is it that Access can immediately display the record count in the navigation bar, but not here.

Normally, it does take a few (sometimes less than a second, sometimes two to three) seconds to load the actual record count. Unless the dataset is measly 100's then the record count might be easier to load. Read the comment by CJ_London.

 

[Zak14]

So, how do I go about fixing this issue. Can I maybe set the form to automatically refresh the count after a couple seconds? Or maybe set access to open the last record first? Or something.

 

[pr2-eugin]

I mentioned that already in Post#3, check again.

 

[CJ_London]

another suggestion is if possible to reduce the size of your recordset both in terms of 'width' and number of rows.

For example rather than bringing through an entire recordset and then filtering, modify the form recordset criteria to only bring through the record you want to view - but then the record x of y will be irelevant.

What is the benefit to the user of knowing which record x of y they are looking at?

 

[Zak14]

Oh, I just thought I needed it because it's in the navigation bar below. I guess a simple record X is okay rather than X of Y.

Thankyou.

 

[vbaInet]

Or use a DCount() function directly in the Control Source of your textbox. Test both to see which one is faster. There are other tricks that you can use too which could result in a much faster result.

 

[CJ_London]

Basically the X is meaningless - if you sort the records in a different order X will refer to a different record. The only possible use is to tell you how far you are from the beginning or end of the recordset.

 

[vbaInet]

Basically the X is meaningless - if you sort the records in a different order X will refer to a different record. The only possible use is to tell you how far you are from the beginning or end of the recordset.

I suppose it's just aesthetics. But if it was completely pointless I don't think Microsoft will display exactly this in the Navigation bar. Which brings me to my question to Zak14, do you realise users can see this information in the Navigation bar below?

 

[CJ_London]

But if it was completely pointless I don't think Microsoft will display exactly this in the Navigation bar

I wasn't suggesting it was pointless, just meaningless - in much the same way an autonumber value is meaningless - the point about an autonumber value is that it uniquely identifies a record. The only time I use it is if I want to get to say the middle record of a recordset for some reason - I can can easily navigate to it from the navigation bar.

For example say I have 100000 records and want to copy them to excel and haven't built a routine to do this. I can only copy about 65000 rows so I go to the 60000th record and copy and paste into excel the records before this then return to that record (or the one after to be precise) and copy and paste the records after.

The point the OP should be taking on board is why is he replicating the navigation bar but without any functionality. Hence my question

What is the benefit to the user of knowing which record x of y they are looking at?

 

[vbaInet]

My bad! I read to be "pointless" instead of "meaningless" I guess there are some cases where it can be meaningful. For example, a user that has a set list of tasks that are strictly worked through from top to bottom can now how many s/he has completed and how many's left. The same analogy also relates to when you're reading a novel, sometimes you want to know how many pages you've read.

The point the OP should be taking on board is why is he replicating the navigation bar but without any functionality. Hence my question

I concur.