Counting records from multiple queries

[TheTubaGeek]

I feel the need to preface this post with the following:

:banghead::banghead::banghead::banghead::banghead::banghead::banghead::banghead::banghead::banghead:

I have a form that I created to pull the results of multiple queries in an Access database. While browsing for coding solutions, I came upon the VBA Code shown below in this forum:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim rsCount As Integer
Dim queryNameOrSQL As String

queryNameOrSQL = "<Query Name or the SQL Statement goes here>"

Set db = CurrentDb
Set rs = db.OpenRecordset(queryNameOrSQL)

rsCount = rs.RecordCount

As a reference, I currently have a Public Function setup as follows:

Public Function qCount(arg As String) As Long
    Dim qd As DAO.Recordset
    rst.MoveLast
    Set qd = CurrentDb.OpenRecordset(arg)
    qCount = qd.RecordCount
End Function

I then have a Private Sub Form_Load() that makes each text box pull the qCount for each associated query (txtTextBox = qCount("qQueryName")). Unfortunately, this shows each value as "1".

What is the best approach to have the appropriate values load, as well as make it where if I click on the associated Text Box label, a pre-generated report loads?

I appreciate the assistance!

 

[plog]

What is the best approach to have the appropriate values load, as well as make it where if I click on the associated Text Box label, a pre-generated report loads?

Define 'appropriate values'. You provided a lot of non-working code, but you never told us what it is you want to accomplish.

 

[pbaldy]

That isn't completely reliable as is:

http://www.baldyweb.com/RecordCounts.htm

DCount() would be simpler:

DCount("*", "QueryName")

 

[TheTubaGeek]

Define 'appropriate values'. You provided a lot of non-working code, but you never told us what it is you want to accomplish.

The queries have breakdowns of the data and I am trying to pull the record counts of those queries into the form.

 

[plog]

Then use a DCount as the source of the forms input--no VBA necessary

 

[TheTubaGeek]

Issue resolved.