Can't get reliable recordcount

  • Thread starter Thread starter Peter Row
  • Start date Start date
P

Peter Row

Hi,

I have an Access 2002 (XP) ADP project in 2002 format linking to SQL Server
2000 SP3.
I offer users a search form where they can select values from multiple drop
downs and the find all records that match and also get a count of how many
matches.
A dynamic SQL string is built up and set as the recordsource of a subform.
At it's most basic, (and most common) the query will be tell me which (and
hence how many) records have the StatusID field = XX (where XX is a value
selected by the user from a combo)

However depending on cosmic alignments, time of day, whether it's a full
moon tonight, the total number of records that I display in a textbox
underneath the grid, gives wildly inaccurate results or slightly inaccurate.

I have tried using both the RecordsetClone.Recordcount and
Recordset.Recordcount properties of the subform, as well as doing a
movefirst followed by a movelast but still it is not accurate.

Any ideas on how I might get an accurate answer of total number of records
would be most appreciated.

Regards,
Peter
 
Hi,

I have an Access 2002 (XP) ADP project in 2002 format linking to SQL
Server 2000 SP3.
I offer users a search form where they can select values from multiple
drop downs and the find all records that match and also get a count of
how many matches.
A dynamic SQL string is built up and set as the recordsource of a
subform. At it's most basic, (and most common) the query will be tell me
which (and hence how many) records have the StatusID field = XX (where
XX is a value selected by the user from a combo)

However depending on cosmic alignments, time of day, whether it's a full
moon tonight, the total number of records that I display in a textbox
underneath the grid, gives wildly inaccurate results or slightly
inaccurate.

I have tried using both the RecordsetClone.Recordcount and
Recordset.Recordcount properties of the subform, as well as doing a
movefirst followed by a movelast but still it is not accurate.

Any ideas on how I might get an accurate answer of total number of
records would be most appreciated.

Regards,
Peter
Click to expand...

!!!!Possibly!!!!
..... this is because an ADP form, on opening, loads an arbitrary number of
records to display (A), displays these, and then, when the form is drawn
and appears to be complete, loads the remaining records (B).
Procedures which examine the form's recordset between A and B may garner
innaccurate information about that recordset.
A solution is to remove the form's recordsource in design mode, and in the
form's open event, to create a corresponding recordset entirley
indpendently of the form and set the form's recordset to that.
An example may be found at:
http://groups.google.ca/[email protected]
&rnum=1
!!!!Possibly!!!!
 
I would have expected the Recordcount to be correct after a MoveLast, but
then I don't have a lot of experience with ADPs. If that doesn't work try
something like the following instead, using the same criteria in the SQL
statement that you use to filter the subform ...

Public Function HowManyRecords() As Long

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open "SELECT COUNT(*) AS TheCount FROM Categories WHERE
CategoryName LIKE 'C%'"
HowManyRecords = .Fields("TheCount")
.Close
End With

End Function

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Have you considered a count query?
ie
SELECT COUNT(*) FROM TABLE1 WHERE STATUSID=XX AND ...

HTH

Pieter
 
Hi,

I've thought about it , but it just seems such a waste to have to run
effectively the same query
twice. Bairing in mind that the query has 7 or more (can't remember off the
top of my head)
joins.

However if I can't get it to work with some of the other respondees
suggestions I may
be forced into doing so (in the same way I was forced to use crappy Access
in the first
place).

Thanks for the reply,
Peter
 
Hi,

The recordsource is not set at design time. It is set at run time. The user
selects various
criteria, clicks go which causes a dynamic SQL string to be built up and
then that is set
as the recordsource.

However your independant recordset idea is worth giving a try. I don't fancy
(as other
responses have suggested) running the query a second time except with a
COUNT(*)
in just to get an accurate figure.

Thanks for the idea,
Peter
 
Hi,

I eventually found that due to the query used some records were being
missed.
However after my users updated there data to provide information that was
missing they still claim that the record count differs wildly, i.e.
recordcount should
be 283, but actually is reported as 200.

I have tried the suggestions of using a recordset variable setup using the
query
instead of the recordsource but this doesn't have any effect.

I don't want to use the COUNT(*) suggest just yet as the query is quite
complex and takes a few seconds to perform and hence I don't want to run
it a second time just to get the count.

Any other suggestions much appreciated.

Regards,
Peter
 
Back
Top