Display a count of records on Form or Report

[rdougherty]

I have a requirement to set up a form or report to count and sum values from a particular table. I do NOT wish to display any of the records from that table as the Record Source. I simply want this one-page form to show me a few variables or results of functions called.



I am having some trouble deciding which method to use for counting records meeting criteria.

Count records in table where field1 = "PAYENM"

DCount("[field1]", "raw_text_file", "[field1]" = "PAYENM")

this will be our count of payees, which we are QC'ing and documenting on a log sheet before uploading to an eBanking system.

Once I can get tihs to work, I'll probable be able to figure out more things to count and/or sum.

Thanks,

[pbaldy]

See if this helps:

http://access.mvps.org/access/general/gen0018.htm

[HiTechCoach]

I do dashboards to display totals and summaries.

The dashboard form is unbound. I uses unrelated sub form(s) and/or sub report(s) to display totals, summaries, and graphs.

[rdougherty]

thanks for the info!

As a follow up question, what is the best way to prevent the controls on the form from calling the variables before another button on the form populates a table that these variables are evaluating themselves based upon?

For instance, I do not want these controls either showing #error etc. or giving the "Improper use of Null" which a DSUM variable is throwing, because it doesn't have anything to sum.

Would it be to make them all invisible when the form loads, and then make them visible after the import button populates the table?

OR

would it be best to make these form controls not use the variables as control sources, but define them with one of the variables as a source immediately after the import button is finished and table populated?

Please advise! (thanks in advance)

[HiTechCoach]

thanks for the info!

As a follow up question, what is the best way to prevent the controls on the form from calling the variables before another button on the form populates a table that these variables are evaluating themselves based upon?

For instance, I do not want these controls either showing #error etc. or giving the "Improper use of Null" which a DSUM variable is throwing, because it doesn't have anything to sum.

Would it be to make them all invisible when the form loads, and then make them visible after the import button populates the table?

OR

would it be best to make these form controls not use the variables as control sources, but define them with one of the variables as a source immediately after the import button is finished and table populated?

Please advise! (thanks in advance)

When importing the data to a temp table(s) for the dashboard I would also make sure that there is always records even if the numbers are all zero (o). Then your DCount() will also work.

I also do as much of the totaling in the temp tables as possible. This allow for simple bound sub forms and sub reports.


About screen updating, this is another reason why I use a bound sub report or sub form whenever possible. Before the import, I turn off screen updating. Import the data. Requery the form. turn on screen updating.

I avoid using Domain Functions like DCount() and DSum(). As you are experiencing, they require extra VBA code to validate that they will return something before using them. I prefer to gather all my statics with a query.

You might also find this helpful: Using Dashboard Graphics in Access

[rdougherty]

follow up: the problem ended up being that the values were not technically Null, but zero rows from which to evaluate Null.

The solution was actually very simple and uses very little additional code:

(this tests whether a table is populated True/False)

Public Function textfile() As Boolean
    Dim r As DAO.Recordset
    
    Set r = CurrentDb.OpenRecordset("raw_text_file")
    If r.RecordCount > 0 Then
    textfile = True
    Else
    textfile = False
    End If
    
End Function

Then use that Y/N to evaluate whether each count, sum, should occur or not.

Public Function PaymentSum() As Double
    If textfile = True Then
    PaymentSum = DSum("[field5]", "raw_text_file", "[field1] = 'PMTHDR'")
    Else
    Exit Function
    End If
End Function

[HiTechCoach]

Thanks for the update and sharing your solution so other may benefit.