Navigation Issue with RecordsetCount (1 Viewer)

Solo712 · Mar 20, 2015

Has anyone noticed that in A2007 if you disable the Navigation Buttons on the form the Me.RecordCount will be set to 1 when the form opens ? I replace the Nav buttons with my own set but my "Go Next" button will not work untill I hit "Go to Last". This apparently restores the recordset count to the correct number of records.

Does anyone know about this behaviour and how to fix it ? I really don't like having the nav bar there when I don't need it.

Best,
Jiri

spikepl · Mar 20, 2015

This is a known feature that .RecordCount>0 is not indicative of teh actual count until you do .MoveLast so as to make sure the entire recordset is loaded. RecordCount=0 tells you there are no records. .recordCount> 0 tells you there is one or more records.

RainLover · Mar 21, 2015

So where is the problem.

I don't think it is with 2007 Nav Pane. I believe it is wider than that.

I was told once to do a Move Last, Move First for any record count.

Also this is another way that could be faster in a loop. Instead of each loop checking to see if it is at the EOF use doUntil you reach the correct record count.

Comments by others would be appreciated as I have never tested this theory.

BTW This is using 2003 not 2007. They both have the same problem.

MarkK · Mar 21, 2015

What you can do if you make your own nav bar, is navigate to the the end of the recordsetclone to get your recordcount. This saves you navigating in the actual recordset, which your user will see (because the form is bound to it).

Code:

private m_recCount as integer

private sub form_load
   with me.recordsetclone
      .movelast
      m_recCount = .recordcount
   end with

   me.tbNavBar = Me.recordset.absoluteposition + 1 & " of " & m_recCount
end sub

RainLover · Mar 21, 2015

Mark

If I read you correctly, when the form is opened we move to the Last Record. Then we do a Record Count.

This however has no affect on the actual position in the Record Set that currently has the focus.

So we go to the last record from anywhere, Count the number of records, then return the record position we currently are at.

To take this further, would it not be Best Practice to always employ this method when using Record.Count etc. If not we could not guarantee that the returned value is correct. I am referring to situations other than those attached to a Form.

If I am correct then I will expand this to ask about BOF and EOF compared to counting of record until it matches the Record.Count. Maybe I need to explain myself better, which I will do if I am on the right track.

spikepl · Mar 21, 2015

If I read you correctly, when the form is opened we move to the Last Record.

Not really. We move to the last record of the recordset clone which does not affect the current position of th original recordset.

Then we do a Record Count.

Yes

This however has no affect on the actual position in the Record Set that currently has the focus.

It's not question of focus. The recordset clone has a record pointer that you can move without affecting the record position for the original for the cloning.

So we go to the last record from anywhere, Count the number of records, then return the record position we currently are at.

No. The current record position in the original recordset is untouched. The record pointer of the clone has been moved to last record and is not moved further.

To take this further, would it not be Best Practice to always employ this method when using Record.Count etc. If not we could not guarantee that the returned value is correct. I am referring to situations other than those attached to a Form.

Essentially, yes. Unless you can reason that the recordset is fully loaded, in which case this move is redundant. Not sure what "etc." covers.

If I am correct then I will expand this to ask about BOF and EOF compared to counting of record until it matches the Record.Count.

If you insist on employing RecordCount just after loading, then you have no other option. But many functionalities can be satisfied by eg. EOF, obviating the need for counting:

Code:

Set rst=CurrentDb.OpenRecordset("SomeQuery")

With rst
   Do While Not .EOF
      'do stuff
   Loop
End With

RainLover · Mar 21, 2015

Spike

Your explanation is much better than the way I posed the questions. I believe I am in agreement and thank you for your clarification.

But there is more.

Set rst=CurrentDb.OpenRecordset("SomeQuery")

With rst
Do While Not .EOF
'do stuff
Loop
End With

In your code you are checking to see if you are at the end of the file before moving onto the next loop. Nothing wrong with that.

As an alternative could we do the following which may be a little faster. Not a lot faster so this is for the benefit of my own enquiring mind.

Instead of doing things your way. That is checking your position every loop. Why not do an A = A + 1. If there are 100 records the loop knows that it does not have to do anything different or special until A = 100

It should follow that waiting for 100 is slightly faster than EOF

Then again, maybe I am getting carried away with nothing and that either method is just as good as the other.

spikepl · Mar 21, 2015

In any loop you need to check for each execution whether or not you have done the desired amount of looping.

Whether checking for .EOF is faster than comparing the loop counter with the loop limit I don't know, but I'm sure that that is not what slows down code.

Using .EOF has the small advantage that you do not need to load the whole set before getting going and you can always get the .RecordCount after having finished the loop.

RainLover · Mar 21, 2015

Thanks Spike.

I can't see any advantage in following this line of thought. You have explained the points as well as possible.

Time to move on.

Maybe someone else has a question or two.

spikepl · Mar 21, 2015

As far as I know, VBA compiler has no optimiser. So if you have a loop like this

Code:

For I = 0 To SomeComplexFunction
'do stuff
Next I

where SomeComplexFunction calculates the value for the loop limit, it is called for each loop, which can be costly if the function demands many resources.

An optimiser would remake this (invisibly to the user) into:

Code:

tempVar=SomeComplexFunction
For I = 0 To TempVar
'do stuff
Next I

so that the SomeComplexFunction is only called once, but since VBA doesn't have one, you have to do such things yourself

RainLover · Mar 21, 2015

An optimiser would remake this (invisibly to the user) into

Now you have got me running to do some research.

spikepl · Mar 21, 2015

In such case you may want to check this: http://www.access-programmers.co.uk/forums/showthread.php?t=228946

RainLover · Mar 21, 2015

That will make life easier.

Ta Muchly

Solo712 · Mar 21, 2015

spikepl said:
This is a known feature that .RecordCount>0 is not indicative of teh actual count until you do .MoveLast so as to make sure the entire recordset is loaded. RecordCount=0 tells you there are no records. .recordCount> 0 tells you there is one or more records.

It is interesting though that this rule does not apply when the nav bar is on. Also, if you order the set by a field other than the PK the RecordCount is loaded correctly.

Best,
Jiri

spikepl · Mar 21, 2015

So you've identified a few aberrations, where the thing serendipitously does give you the true count even if you wouldn't expect it.

To be honest I would not bet my code on aberrations

Solo712 · Mar 21, 2015

MarkK said:
What you can do if you make your own nav bar, is navigate to the the end of the recordsetclone to get your recordcount. This saves you navigating in the actual recordset, which your user will see (because the form is bound to it).

Code:

private m_recCount as integer private sub form_load with me.recordsetclone .movelast m_recCount = .recordcount end with me.tbNavBar = Me.recordset.absoluteposition + 1 & " of " & m_recCount end sub

Thanks, Mark. This is a good suggestion but I am not sure I want to use it. I prefer to rely on the recordcount to read correctly than to create extra overhead in having the app do the chore of counting the records as they are added or deleted. This is a glitch which goes away as soon as you manipulate the record set. I created my own set of nav buttons to control better what the user does. The Next button which does "DoCmd.GoToRecord , , acNext" will execute even if the there is no existing record. I wanted to prevent the user of accidentally tripping into a blank new record (this is a signle record form) so I read the poisition relative to the end of the recordset before executing the command. As this is library code I need to rethink the strategy before committing to a new adventure.

Best,
Jiri

Solo712 · Mar 21, 2015

spikepl said:
So you've identified a few aberrations, where the thing serendipitously does give you the true count even if you wouldn't expect it.

To be honest I would not bet my code on aberrations

I think the shoe is on the other foot. Obviously, having to execute a physical move the last record of the record set to get the recordcount right was not the real intention of the Access designers.

Best,
Jiri

spikepl · Mar 21, 2015

I would not dare divine the intentions of those guys of ages ago. The move is to the end of file, which ensures that all records are in before a count proclamation can bemade.

You are demanding miracles. How is the record count to be known before all records are in? Or would you want the .RecordCount property be synchronous and load the entire recordset automatically and make you wait?

Solo712 · Mar 21, 2015

spikepl said:
I would not dare divine the intentions of those guys of ages ago. The move is to the end of file, which ensures that all records are in before a count proclamation can bemade.

You are demanding miracles. How is the record count to be known before all records are in? Or would you want the .RecordCount property be synchronous and load the entire recordset automatically and make you wait?

Again, at the risk of stating the obvious: You don't have to move anywhere to know the number of records in your record set (as indeed is the case with the nav bar on). If Access does the counting internally with the nav bar on (and with recordsets ordered by other than the PK) then asking it provide this function reliably in all recordsets regardless of settings, is not "demanding miracles". At any rate. We are done. Thanks for responding.

Jiri

RainLover · Mar 22, 2015

Solo

As this is library code I need to rethink the strategy before committing to a new adventure.

What do you mean by Library.

Is this just a place to store snippets of code for future reference or is it part of a Mulit User Database, where the various Functions are called by the various users.

They could be calling code for use in one or more Databases. The idea being to change code in one place and it ripples down through all DBs.

Navigation Issue with RecordsetCount (1 Viewer)

Registered User.

Eledittingent Beliped

VIP From a land downunder

bit cruncher

VIP From a land downunder

Eledittingent Beliped

VIP From a land downunder

Eledittingent Beliped

VIP From a land downunder

Eledittingent Beliped

VIP From a land downunder

Eledittingent Beliped

VIP From a land downunder

Registered User.

Eledittingent Beliped

Registered User.

Registered User.

Eledittingent Beliped

Registered User.

VIP From a land downunder

Similar threads

Users who are viewing this thread