Red Light/Green Light on form

  • Thread starter Thread starter Judy Ward
  • Start date Start date
J

Judy Ward

I would love to display a form with a red/green light to reflect the status.
I have searched, and can't seem to find any help on how to accomplish this.
I have the status stored in a table as "Y" or "N", but I have no idea how to
come up with either the green/red light objects or a circle that I can change
the color of.

Thank you in advance for any help you can give me.
Judy
 
You may be in luck.

I have been working on an application for another member of the community
for a couple months in my spare time and building a dashboard like what you
need.

First, there are a couple ways that you can place icons on an Access form.
You can load them from a table by having the images' filenames in your
database or you can place them physically in the forms. Most experts
discourage the latter but if they are of really small size they may not bloat
your application too much, assuming that you are running Access 2003 or
earlier. (I understand that Access 2007 has addressed this issue. My
application was started on a PC win Access 2003 and so I haven't tried using
Access 2007 functionality yet.)

In either scenario you'll need to use a graphics program, my favorite is MS
PhotoDraw 2000, to draw the lights and circles. Or you can download the
icons from the Net. If you subscribe to the AcccessAdvisor there is an
article in it that contains exactly the images that you are looking for.

Now if you place the icons on your form and want to turn them on and off,
you can use the .Visible method in a statement like if Status ="Yes" then
Me.GreenIcon.Visible = True

(My syntax may be wrong but this should get you started.)

The easiest way to place simple icons on a form is to use the rectangle tool
and then use Conditional Formatting to set the color to red, green, etc.
Here something like Expression is [Score]>70 works. I use red,
yellow, light green and dark to indicate Status in one of my applications. I
also have a colored horizontal line on the form and place all of the Poor
Status boxes below the line and all of the Good Status boxes above the line.
An executive can then quickly see if a project is on track or not.

On a more complex dashboard, I have a gauge that I created in PhotoDraw.
The status values will load the appropriate gauge based on its value (e.g.,
10% is really poor and 90% is really good). There are 12 gauges and their
filenames are stored in a table in the application. The main part of the
gauge is identical, only the needles change position to create the dashboard
look. Code for this functionality is on the MSDN website.

The problem with this implementation of an Access dashboard is that the
images should be bmp or gif if you want to hide a message that says 'Loading
the image file - Status.jpg'. Jpgs look better than bmps and gifs to
me.

Another problem that you may have is using Continuous forms. Code as an
OnCurrent event will synchronize all of the images to the same record. In
other words, only one image will display in a Continuous form, which is not
what you want. A real bummer!

I haven't gotten around to creating a dashboard Report yet so I have no
experience with them.

Good luck with your application. I have found few examples of dashboards in
Access. There was a recent article in the AccessAdvisor with 'dashboard' in
the title but it was about placing several charts on the same form/report.

And working with checkboxes has been a nightmare. The way that I handled
them was brute force and so I hesitate passing on bad advice to you. It
worked but there is better way -- I have been told.

David
 
Judy,

I just realized that I could have confused you when I said I place
rectangles on my forms, I actually place Text Boxes and remove the labels.
The Text Box control is the only one that I have found that works with
Conditional Formatting.

It would be great if MS would allow users to change the shapes of Text Boxes
or to use other controls with Conditional Formatting. Circles and octogons
would also be good additions to the toolset IMHO.

David
 
roccogrand said:
Another problem that you may have is using Continuous forms. Code as an
OnCurrent event will synchronize all of the images to the same record. In
other words, only one image will display in a Continuous form, which is not
what you want. A real bummer!
Click to expand...

Hi David,

'Scuse me butting in. Use conditional formatting for the continous form
situation. If you are unhappy with coloured text or a rectangular shape,
design a cut out (stencil) and put this in front of an empty textbox. Use
conditional formatting to change the backcolor of the textbox. This color
will then show through your cut out.

Regards,

Rod
 
Not a problem Rod but my Continuous Forms always display the image for the
current record. I have hidden Text Boxes as you suggest but only simple,
single ones.

If you know of a way to place three or more boxes on a form and use values
to display the correct one, I would very much know how to do that. It has
driven me crazy on a couple recent applications.

Thanks.

David
 
Hi David,

My suggestion was that the cut out acts as an opaque mask - it is the same
for every record - it is just the background color of the textbox that
changes and shows through the cut out/stecil part of the mask. You don't
want anythig too elaborate or large in a continuous form display.

I know of two ways to place variable, non table data in continuous form
lists - but you won't like either of them as they are both messy.

The first is to build a work table with extra columns and then populate that
table from original data adding what variable data is needed. The obvious
problem here is that if the user is allowed to update information a
comparison is needed between the original data and the work table and the
fact that built-in data synchronisation and integrity functionality of Access
are by-passed. A network of user defined events could be used to overcome
this.

The second method has all the inherent faults of the the first but avoids
the necessity to build a work table. It involves unbinding the recordset
from the underlying object and then adding columns and data to that
recordset. I have used this technique but in fairly specialised
circumstances. In my case I had a query that computed stock levels (note
these levels were computed and not held on file). I detached the recordset
from the query, added another column and duplicated the stock level figures.
This additional column was the only one the users were allowed to update with
figures of actual counts. The benefit was the users could see computed versus
actual figures at all times. When finished a scan of the recordset generated
stock adjustment transactions as necessary; there was no need to update any
table data directly.

Is there a simpler solution? I don't know of one. I have also embedded a
single control in the header, footer or on the main form that is updated from
the On Current event of the continuous form. Here the concept is that the
icon shown in this single control applies to the current, highlighted record
in the continuous form.

Sorry, not too much help I fear.

Rod
 
Back
Top