Show number of records returned by SQL

[Robert5833]

Good day all;

There’s probably a very simple fix/solution for my issue, but after hours of
searching and reading in the discussion group here, such a solution eludes me.

I have a form with an unbound text box where a key word can be entered for
text searching of stored records in a table. From the form’s module the value
in the unbound text box is passed to a separate module, which then builds an
SQL string and returns those values to the form’s sub.

All of that works fine, and all I want to do is *display the number of
records returned* by the SQL string in another unbound text box on the form.
(This is to show the user; me, how many records are available to scroll
through; 1 or 10,000, so maybe the keyword search should be narrowed or
expanded, etc.)

As always; I appreciate any help/suggestions you may offer.

Best regards

 

[Arvin Meyer [MVP]]

If you are using a RecordSet, you'd refer to the recordcount, like:

Me.txtCount = Me.RecordsetClone.RecordCount

 

[Robert5833]

Hi Arvin,

Thank you for the prompt reply and suggestion. I tried the code you
provided, but it only returns the record count of the recordset of the form’s
query rather than the text search query.

Based on knowledge I’ve gained from this discussion group I have figured out
how to *count* the records in a recordset, but I think my confusion is on
just that; a recordset.

To help me understand this (and I am trying); is it fair to say that the SQL
statement in the text search Function is a parameter type query and is
returning a *filtered* recordset, but that recordset exists only in the
domain of the *Find First* and *Find Next* button click events (sub routine),
whereas the SQL for the form is creating a recordset in the form’s domain?

If that is an accurate statement, how do I reference the text search SQL
recordset for counting purposes, versus the form’s SQL recordset in the
Recordset.RecordsetCount method?

I can post all or part of the code in the two modules if it helps.

I know I’m missing something very simple here…

Thank you in advance for your help and assistance!

Best regards,
Robert

 

[Albert D. Kallal]

Hi Arvin,


I can post all or part of the code in the two modules if it helps.

Well, you simply have to reference the recordset in that code module. This
might mean you need to declare that reocrdset as public. You also could
perhaps modify the routine you call to "return" the number of records. Which
approach is better will be much an issue of your coding style, and how the
code you have now works.

You could also "modify" the search code to place/set the text box in your
form.

eg:

forms!NameOfForm!SomeTextContorl = MyreocrdSet.RecordCount

So, you can "modify" that search code and right after the recordset is
loaded and populated, then you can put the one line of code as per above
into that code module...

 

[Arvin Meyer [MVP]]

Find First is a single record, as is Find Next. If you are returning records
from that they are not filtered, unless the form is filtered. A filtered
recordset still uses the record count property, so if you are truly
filtering a form, (i.e. using a query or sql to limit the records to the
search criteria) you can run the same query and get the record count.

I have a feeling that what you want is to see how many records there are
that fit the search criteria that you are using when you do a Find
First/Find Next. The correct answer is always 1. But if you actually filter
them or use a recordset to find out how many there are which fit the
criteria, you can do that, not by using Find First, but by actually running
the query using the same criteria.

Function FastLookup(strFieldName As String, _
strTableName As String, _
strWhere As String) As Variant
'Arvin Meyer 4/9/1997 Modified 2/17/2009

Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

strSQL = "SELECT " & strFieldName & " FROM " & _
strTableName & " WHERE " & strWhere & ";"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

rst.MoveLast
rst.MoveFirst

MsgBox rst.RecordCount

If rst.RecordCount <> 0 Then
FastLookup = rst(strFieldName)
Else
FastLookup = Null
End If

End Function

Now by running the above function with all the arguments supplied (using
form variables in the Where clause, if necessary), you will get a msgbox
that returns how many records you have that fit the criteria.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com