Counting the number of records on a form

[Guest]

Where a table acts as the record source for a form, the records are normally selected via an SQL statement or a query. The criteria associated with the latter determine which records are chosen. Normally these will represent a subset of those present in the source table

I have a number of forms where I need to calculate the number of records selected by the SQL statement or query. This figure is, of course, displayed in the navigation bar total but is not accessible to the form designer. Since this number is dynamically variable, I am finding some difficulty in writing appropriate VBA code to return the desired value. Could someone please oblige me? It would be particularly convenient if the source SQL statement or query could be made to return the total number of records selected. DCount at first seemed a likely candidate but appears to be ruled out by the fact that the source queries, for the forms in which I am interested, generally take variable parameters

 

[damien]

Try;

Me.RecordsetClone.RecordCount

You may have to MoveLast, MoveFirst to get an accurate
count. eg:

With Me.RecordsetClone
.MoveLast
.MoveFirst
lngCount = .RecordCount
Me.lblCount.Caption = lngCount & " records"
End With

Let me know how you get on.


Damien

-----Original Message-----
Where a table acts as the record source for a form, the

records are normally selected via an SQL statement or a
query. The criteria associated with the latter determine
which records are chosen. Normally these will represent a
subset of those present in the source table.

I have a number of forms where I need to calculate the

number of records selected by the SQL statement or query.
This figure is, of course, displayed in the navigation bar
total but is not accessible to the form designer. Since
this number is dynamically variable, I am finding some
difficulty in writing appropriate VBA code to return the
desired value. Could someone please oblige me? It would
be particularly convenient if the source SQL statement or
query could be made to return the total number of records
selected. DCount at first seemed a likely candidate but
appears to be ruled out by the fact that the source
queries, for the forms in which I am interested, generally
take variable parameters.

 

[Guest]

Damien

I am sorry that it has taken me a while to let you know the outcome of your suggestions, as you requested. I have had telephone line troubles which have restricted my Internet access for the last few days

Your solution worked admirably, which brings me to the reason underlying my previous question:

The standard navigation bar on Access forms is not user-amendable nor is it automatically scalable. As a result, it cannot be increased in size to accommodate those with less than 20/20 vision – a somewhat ambiguous situation for Microsoft, which is otherwise keen to display its accessibility credentials. Very recent consultation with Mr. Gates’ worthy lieutenants yielded the categorical statement that there is indeed no way around this limitation. (Prior to that, I submitted a couple of questions on this topic to the newsgroup. I now understand why no solution was forthcoming.

Another less than satisfactory consequence of this situation is that, unlike a form’s resident fields, the navigation bar appears to retain its original absolute dimensions when displayed on a monitor of different size from that on which the form was designed. Thus a navigation bar which looks perfectly alright on a large monitor can be truncated when displayed on a smaller screen. ‘Record…4…of 1024’ can be rendered as ‘Record…4…of 10’, or even ‘Record…4…of 1’, for example

Using your code, I have designed and tested a custom navigation bar which overcomes both the above limitations. I would like to apply this to my current project which, at the last count, had 74 forms. Admittedly, not all of these will require the custom navigation bar - many do not display a navigation bar - but, even applying the new code to a significant subset of those forms, will involve a lot of work, which I would like to minimise. An ideal way to do this, it seems, would be to create a single navigation bar object, incorporating the required new features, and then apply this to the appropriate forms via a common module, taking the requisite parameters. At present, each of the control buttons - First Record, Next Record, Last Record, New Record etc. - has its own subroutine, supplied by the control wizard when the navigation bar was created. The current record identifier and the total field count also appear as calculated fields, which are updated in the On Current sub. It would be nice to avoid replicating all this code in every form on which the new navigation bar is to appear but, as yet, my VBA is not good enough to ensure that I can create a module of the desired sophistication without some guidance. My code is often not as succinct as it might be, due to ignorance of some of the more esoteric techniques. That is not so bad if the application is only used once or twice but amending, or consolidating, up to 74 clumsy modules could be a bit of an undertaking! It would be better to get it right first time

Could you, or some other kind soul, map out the sort of approach I need to take