Table showing number or records in each report for each day

[Cbold]

I'm using access 2010
I need a table that will show the report name, number of records and the date processed. Some of the reports are daily, weekly, monthly, quarterly, etc.

I can add a field to the report to obtain the number of records using count(field).
How do I populate the table for each report...there are at least 15 reports a day and some do may not have records every day.

Will use a date range on my switchboard to obtain monthly,quarterly cross tab reports.

Greatly appreciate any responses....

[jforbes]

First I would make a method to log the execution of the Report. Something like this would insert a record into the ReportHistory table whenever it is called:

Public Sub logReportExecution(ByRef sReportName As String, ByRef dRunDate As Date, ByRef lRecordCount As Long)
 
    Dim sSQL As String
 
    sSQL = ""
    sSQL = sSQL & "INSERT INTO ReportHistory ("
    sSQL = sSQL & "  ReportName "
    sSQL = sSQL & ", RunDate "
    sSQL = sSQL & ", RecordCount "
    sSQL = sSQL & ") VALUES ( "
    sSQL = sSQL & "  '" & sReportName & "' "
    sSQL = sSQL & ", #" & dRunDate & "# "
    sSQL = sSQL & ", " & lRecordCount & " "
    sSQL = sSQL & ") "
 
    CurrentDb.Execute(sSQL)
 
End Sub

Then wherever you a running a Report, you can call it like this:

call logReportExecution("test", now(), 33)

So then, if you call your Report with a Filter, you can do something like this (there are probably some typos):

    Dim sWhere As String
 
    sWhere = "Thingies>100"
 
    DoCmd.OpenReport "SomeReport", acViewNormal, , sWhere 
    Call logReportExecution("SomeReport", now(), DCOUNT("ID", "SomeReportBaseTable", sWhere ))
 

You could also call the logReportExecution() from within your Report after you have your Record Count.

[ADezii]

1. My approach was very similar to jforbe's one.
2. Create a Table named tblReportLog:
   1. [RName]-{STRING}
   2. [Records]-{LONG}
   3. [Processed]-{DATE/TIME}
3. Copy-N-Paste the following Sub-Routine into a Standard Code Module:
   

   Public Sub LogReportInfo(strReportName As String, lngNumOfRecs As Long, dtePDate As Date)
     CurrentDb.Execute "INSERT INTO tblReportLog ([RName],[Records],[Processed]) VALUES ('" & _
                        strReportName & "'," & lngNumOfRecs & ",#" & dtePDate & "#)", dbFailOnError
   End Sub

4. For every Report you wish to Log, Copy-N-Paste the following Code into the Close() Event of the Report:
   

   Private Sub Report_Close()
     Call LogReportInfo(Me.Name, DCount("*", Me.RecordSource), Now())
   End Sub

5. Sample OUTPUT using three Reports from the Sample Northwind Database:

PK    RName                    Records    Processed
1     Customer Address Book    29        8/17/2016 11:53:01 AM
2     Top Ten Biggest Orders   10        8/17/2016 11:54:44 AM
3     Customer Phone Book      29        8/17/2016 11:54:50 AM
 

P.S. - The Code is based on Reports requiring no Parameters.

[Cbold]

Thanks for the replies. I actually used something very similar to ADezii
I'm now working thru the reports that don't have any records and dealing with sub reports.