Show totals on Switchboard?

P

Pat Dools

Is there a way to have calculated fields on a switchboard-type of form that
can loop thru a table/query and present totals (counts) orf records meeting
certain criteria? I am working with a clinical forms tracking database, and
we would like to show, how many records there are for a given form status
(e.g., 'Missing', 'Double-entered', etc.). Since the switchboard doesn't
have an underlying Record Source, do I have to create a button that actually
launches another form that has an actual table/query as its data source in
order to accomplish this?

Thanks!
 
A

Arvin Meyer [MVP]

No, you don't need to open a form, just do a recordset and recordcount
(aircode):

Function CountRecords() As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From tblWhatever Where Status = -1")

rst.MoveLast

CountRecords = rst.RecordCount

End Function

Now add a textbox to your switchboard and set its control source to:

=CountRecords()
 
P

Pat Dools

This is great, Arvin! Where do I put the following code below, in the
switchboard Form's 'On Open' properties? Or do I just put this into a new
Module that gets called by setting the Control Source of the Text Box on the
switchboard?

Thanks!
 
Top