Show totals on Switchboard?

[Guest]

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!

 

[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()

 

[Guest]

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!

 

[Arvin Meyer [MVP]]

Since you want to see the results on the switchboard, I'd put the code
either in the switchboard, or in a standard module. Add a textbox to the
switchboard and set its controlsource to:

= CountRecords()

When the switchboard is opened it will run the function. If you anticipate
the tables recordcount changing, you might want to add a timer event or a
button to rerun the code every now and then.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thanks, Arvin! Worked like a charm.