Creating a Dashboard of Global Values

  • Thread starter Thread starter Frank Camean
  • Start date Start date
F

Frank Camean

I'm trying to create a dashboard for my Access
application. On that dashboard I want to have a global
representation of the entire DB. I have my own Real
Estate application with multiple tables. On that
dashboard I want to have TOTAL # OF CONTACTS, TOTAL # OF
PROPERTIES, TOTAL # OF TASKS, TOTAL # OF TENANTS, etc...

I have a simple query that counts the number of records in
each table. For instance, on my CONTACTS table, I have a
query that COUNTS all the ID #'s for that table. It
successfully returns a value. So the query is fine.
However, on the DASHBOARD FORM, I created a TEXT BOX field
and in the properties under CONTROL SOURCE, I linked it to
the QUERY result. But when I run the form, I get #Name?
as the value. The QUERY of course only has one record. I
also created my own DASHBOARD Table as another approach.
And for the TOTAL CONTACTS field, I linked it to the QUERY
result as well. But again, I get #Name?

In short, how can I create a DASHBOARD form that can give
me a global representation of my DB? I want to gather the
total number of records for each table and present that on
my dashboard. Is there a way to do this? Please help.
thanks.

Frank
 
Frank Camean said:
I'm trying to create a dashboard for my Access
application. On that dashboard I want to have a global
representation of the entire DB. I have my own Real
Estate application with multiple tables. On that
dashboard I want to have TOTAL # OF CONTACTS, TOTAL # OF
PROPERTIES, TOTAL # OF TASKS, TOTAL # OF TENANTS, etc...

I have a simple query that counts the number of records in
each table. For instance, on my CONTACTS table, I have a
query that COUNTS all the ID #'s for that table. It
successfully returns a value. So the query is fine.
However, on the DASHBOARD FORM, I created a TEXT BOX field
and in the properties under CONTROL SOURCE, I linked it to
the QUERY result. But when I run the form, I get #Name?
as the value. The QUERY of course only has one record. I
also created my own DASHBOARD Table as another approach.
And for the TOTAL CONTACTS field, I linked it to the QUERY
result as well. But again, I get #Name?

In short, how can I create a DASHBOARD form that can give
me a global representation of my DB? I want to gather the
total number of records for each table and present that on
my dashboard. Is there a way to do this? Please help.
thanks.

Frank
Click to expand...

To get a value from your query, seeing as how your form isn't bound to
that query, you'll have to use a DLookup expression; for example,

=DLookup("TotalContacts", "qryCountContacts")

But you don't really need to create a separate query for each of these
text boxes. You can use the DCount function in ControlSource
expressions in each of the four text boxes.

=DCount("*", "CONTACTS")

=DCount("*", "PROPERTIES")

=DCount("*", "TASKS")

=DCount("*", "TENANTS")

One thing to be aware of is that, as you add or delete records from
these tables, these text boxes won't automatically update. You must use
code to recalculate them whenever that occurs; e.g.,

Forms!DASHBOARD.Recalc
 
Dirk,

My dashboard form will always be sitting in the
background. To get it to update, you said I need to write
code as follows....

Forms!DASHBOARD.Recalc

Dirk, do I add that to the "ON CURRENT" event? Or do I
add that elsewhere? In short, where and how do I add this
code to make sure my form is constantly
refreshed/recalculated?

I'm thinking of adding a REFRESH BUTTON to do the
refresh. What do u think?

Thanks.

Frank
 
Frank Camean said:
Dirk,

My dashboard form will always be sitting in the
background. To get it to update, you said I need to write
code as follows....

Forms!DASHBOARD.Recalc

Dirk, do I add that to the "ON CURRENT" event? Or do I
add that elsewhere? In short, where and how do I add this
code to make sure my form is constantly
refreshed/recalculated?
Click to expand...

There's the rub. If you want the dashboard to be always accurate, then
you have to do it in every event that might change the values the
dashboard displays. That's doable, if your application maintains tight
control over user actions, but you have to put code in several places in
several forms. Frankly, I don't like that approach very much -- it's a
lot more work on your part, for relatively little gain.
I'm thinking of adding a REFRESH BUTTON to do the
refresh. What do u think?
Click to expand...

I think that's a fine idea. The click event of that button (presumably
on the dashboard itself) would just have

Me.Recalc

in its Click event procedure. The only trouble is that users have to
understand that the reported counts won't be accurate unless they click
the button. But if you put a big REFRESH button right out there in
plain view, users are going to understand and click it.

Here are two other ideas you might consider, depending on how you'd
prefer it to work:

1. Use the dashboard form's Activate event to recalculate it. That way,
while the form sits in the background its values won't be accurate, but
whenever a user clicks on it to bring it to the foreground, the values
will be refreshed.

2. Use the dashboard form's Timer event to recalculate its values
periodically -- say, every minute, or every 5 minutes. If you do this,
be aware that you ought to close this form while you are doing actual
development work in the database, especially if you're writing VB code.
Weird things happen when a form's Timer event fires while you're working
in the VB Editor.

You could even combine more than one of these three options (button,
Activate, Timer) to get the behavior that suits you.
 
Back
Top