Show totals on Switchboard?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pat Dools - 11 May 2007 03:19 GMT

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!

Signature

Pat Dools

Reply to this Message

Arvin Meyer [MVP] - 11 May 2007 06:28 GMT

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

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Is there a way to have calculated fields on a switchboard-type of form
> that
[quoted text clipped - 10 lines]
>
> Thanks!

Reply to this Message

Pat Dools - 11 May 2007 14:03 GMT

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!

Signature

Pat Dools

> No, you don't need to open a form, just do a recordset and recordcount
> (aircode):
[quoted text clipped - 29 lines]
> >
> > Thanks!

Reply to this Message

Arvin Meyer [MVP] - 14 May 2007 05:31 GMT

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.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> 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
[quoted text clipped - 41 lines]
>> >
>> > Thanks!

Reply to this Message

Pat Dools - 15 May 2007 03:28 GMT

Thanks, Arvin! Worked like a charm.

Signature

Pat Dools

> 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
[quoted text clipped - 50 lines]
> >> >
> >> > Thanks!

Reply to this Message