A Live, changing counter of a database

[mobile2]

I have not been into this very helpful website for a while. Have changed jobs so not up to scratch with code. (I am not a programmer, as I do not use these skills in my current job) I have searched for over an hour and cannot find the simple solution to my question? How do I run an application that counts the total of records in a database. I want the application to show a live count i.e. constantly changing, like a scoreboard. I would love to do a demo, whether it access, excel to show this in action. I do not have access to the data as I work for a government organisation, who have an IT provider. We are very focused on what work comes into the building daily because of our targets i.e. no of applications submitted for registration and the number of applications despatched (completed). This code must be used for so many things and believe that it could be so simple that I am just searching under the wrong words. I would really appreciate some guidance. If I can show how it works then I'm sure I can gain knowledge from our IT provider on how to put in place. I am fed up with people manually running round the office the check the total of intakes and despatches. Our reporting department run weekly reports but to have something "live" that counts records would be fantastic. I have used MS Access/Crystal/Excel so would hope to be able to set-up a ddemo database and have a seperate report that shows a live count as the database is updated.

 

[dhookom]

I'm not sure why this would be very valuable. Perhaps in your workplace continuous live totals are needed. If so, you could create a form and use the timer event to run code.

First create a query [qselTableNames] that returns all your table names:

SELECT msysobjects.Name AS TableName
FROM msysobjects
WHERE (((msysobjects.Type)=1 Or (msysobjects.Type)=6) AND ((Left([Name],4))<>"msys"))
ORDER BY msysobjects.Name;

Then create a form with a label control named "lblTotalRecords". Set the Timer Interval to something like 10000 for 10 seconds. Add code to the form of:

Option Compare Database
Option Explicit
Dim db As DAO.Database

Private Sub Form_Open(Cancel As Integer)
    Set db = CurrentDb
End Sub

Private Sub Form_Timer()
    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim intTableCount As Integer
    Dim lngRecordCount As Long
    strSQL = "SELECT TableName FROM qselTableNames"
    Set rs = db.OpenRecordset(strSQL)
    With rs
        Do Until .EOF
            intTableCount = intTableCount + 1
            lngRecordCount = lngRecordCount + DCount("*", !TableName)
            .MoveNext
        Loop
        .Close
    End With
    Me.lblTotalRecords.Caption = intTableCount & " tables and " & lngRecordCount & " records."
End Sub

Duane
Hook'D on Access
MS Access MVP