Record count from a query available on a form

[sdavid74]

Is there a way that I can display the record count in a certain query on a main form page?

I have a main page that I use to navigate among query results, forms, and reports, and, from that form, I would like to display how many records are in each query.

 

[nabiS2K]

You can do it to ways...

MyQuery is the name of the query
MyField is a field in the query

1. The easy way with slow results:
Use the DCOUNT function without any specified criteria. DCount("[MyField]","MyQuery&quot

2. The harder way with faster results:
Use the following VBA Code (if Access 97)

Dim dbs as Database
Dim rst as RecordSet
Dim MyQuerysRecordCount as Long

Set dbs = CurrentDb
Set rst = dbs.QueryDefs("MyQuery&quot.OpenRecordSet

If rst.RecordCount > 0 Then
rst.MoveLast
rst.MoveFirst
End If

MyQuerysRecordCount = rst.RecordCount
rst.Close

 

[nabiS2K]

You can do it two ways...

MyQuery is the name of the query
MyField is a field in the query

1. The easy way with slow results:
Use the DCOUNT function without any specified criteria. DCount("[MyField]","MyQuery&quot

2. The harder way with faster results:
Use the following VBA Code (if Access 97)

Dim dbs as Database
Dim rst as RecordSet
Dim MyQuerysRecordCount as Long

Set dbs = CurrentDb
Set rst = dbs.QueryDefs("MyQuery&quot.OpenRecordSet

If rst.RecordCount > 0 Then
rst.MoveLast
rst.MoveFirst
End If

MyQuerysRecordCount = rst.RecordCount
rst.Close

 

[sdavid74]

Thanks, I used the easy way and it worked fine. Out of curiosity, where would I insert the code? Would I use a text box or what?

 

[BanditWk]

I used to code the RecordCount as NabiLikeDavid, but came upon an easier way, should you be interested:

Dim dbs as Database
Dim rst as RecordSet
Dim MyQuerysRecordCount as Long

Set dbs = CurrentDb
Set rst = dbs.QueryDefs("MyQuery&quot.OpenRecordSet

rst.moveLast
rst.moveFirst

MyQuerysRecordCount = rst.RecordCount

rst.Close


See, once the recordset is populated, DAO has a way of just giving you the record count.
Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)