Changing label colors according to dates

[Guest]

Hi, I have a main form with several labels on it (designed as buttons). These
all have numbers on them. When they are clicked it opens the record according
to the number selected (these are a bunch of truck numbers).

When the second form is opened there are several more labels (equipment
type). When I click on a specific label it opens the record corresponding to
the truck selected on the first form. The record has information such as
expiry date of equipment. I have the expiry date set up so that it turns
yellow when it is within 90 days of expiring, or turns red when it is
expired.

My problem right now is that I would like my labels on the first and second
form to change colors according to the retirement date of their equipment.
If the cable for truck 2279 is expired, I want the label "2279" to turn red
so that user knows that this truck has some expired equipment. Then on the
second form the label "cables" would also be red.

Is this possible to do at all?

Thank you very much for your help

 

[Guest]

Yes, it is possible, but I would not recommend it. You are already way down
the road with a poor design. You have locked yourself in so that each time a
new truck is acquired, you have to modify your application. If a truck is
retired, you have to modify your application. Any time any equipment is
acquired, replaced, or retired you have to change your application.
Now you want your form to be very slow to open. That is because to do what
you want to do, you have to construct a DLookup or a query for each truck to
determine whether any expiry dates are near or past.

I would strongly suggest you rethink your design. As to the specific, a
report to provide the information would be a better idea.

I don't mean to beat you up about the design, I really am wanting to spare
you a lot of future grief.

 

[Guest]

Thanks for the response
I agree that it is a poor design. I tried to explain that when I was asked
to do this, because as you said, as soon as there is a new truck added,
someone will have to fix it. Luckily the trucks usually stick around for 5
years at least, and as for the equipment I've set it up so that they can
change the numbers and dates on a form, so that should not be a problem.

If you have any suggestions as to how I could change my design I would love
some input.
It is supposed to be set up to be really easy to use, and look like a touch
screen. Nobody seems to care that there might be problems with it down the
road, as long as it is easy to use and looks good.
Thanks again for the response, if you wouldn't mind helping me out with some
more information on the query I'd have to set up that would be great. They
just want something that looks good and will impress the bosses.

 

[Guest]

Without knowing your tables and fields, I can't really help. If you can post
back with the information on the tables and fields involved, I'll see what I
can do.
As to the design issues, you have my sympathy.

 

[Marshall Barton]

Hi, I have a main form with several labels on it (designed as buttons). These
all have numbers on them. When they are clicked it opens the record according
to the number selected (these are a bunch of truck numbers).

When the second form is opened there are several more labels (equipment
type). When I click on a specific label it opens the record corresponding to
the truck selected on the first form. The record has information such as
expiry date of equipment. I have the expiry date set up so that it turns
yellow when it is within 90 days of expiring, or turns red when it is
expired.

My problem right now is that I would like my labels on the first and second
form to change colors according to the retirement date of their equipment.
If the cable for truck 2279 is expired, I want the label "2279" to turn red
so that user knows that this truck has some expired equipment. Then on the
second form the label "cables" would also be red.

Conditional Formatting is not really appropriate for this
kind of mulitple forms situation. Instead you can probably
use the third form's Current event to set the color for all
the form's using something like this air code:

Dim lngColor As Long

Select Case DateDiff("d", Date, expiredate)
Case Is <= 0
lngColor = RGB(255,0,0)
Case Is <= 90
lngColor = RGB(255,255,0)
Case Else
lngColor = vbWhite
End Select

Me.expiredate.BackColor = lngColor
Forms!equipment.lblforthisform.BackColor = lngColor
Forms!trucks.lblforthistruck.BackColor = lngColor

Obviously, you need to come up with a way to get from this
third form back to the specific label on the other forms.
This could be done by naming the labels on the other forms
in such a way that the third form can calculate the name.
For example, if the second form has a text box (named
txtTruck) with the truck number and the truck labels on the
first form are named like lblTruck1234 for truck number
1234, then the third line above could be:

Forms!trucks("lblTruck" & _
Forms!equipment.txtTruck).BackColor = lngColor

 

[Guest]

Thank you so much for offering your help.
I have about 8 different tables that all follow the same format (they are
just for a different type of equipment).
The different field are Truck #, Serial #, Part #, Date of First Use,
Retirement Date.
There are about 15 trucks in total and the equipment on the truck (these are
the different tables) are Upper Sheave, Lower Sheave, Hanger Bar, Tie-Down
Chains, Sling, Clevis, DualHook, Cables and Makeup Plate.

I understand that this will be a bit of a hassle because as you said we'll
need a query for each truck and piece of equipment. I would like to leave the
database as is, but if this will make them happy and get me a bonus, I'm
gonna try my best

So there is a main Form "Trucks" which has a label for each truck. Then when
you click on it the form "Equipment" is opened (with the Truck # being
displayed) and there are labels for the equipment. When those are selected a
form is opened and displays the record for the truck selected.

In total there are 10 tables, and 11 Forms. I can't think of what other
information you'd need. Hope this helps

Thanks a lot

Alex

 

[Guest]

Thanks a lot
I'll see what I can do with that

Conditional Formatting is not really appropriate for this
kind of mulitple forms situation. Instead you can probably
use the third form's Current event to set the color for all
the form's using something like this air code:

Dim lngColor As Long

Select Case DateDiff("d", Date, expiredate)
Case Is <= 0
lngColor = RGB(255,0,0)
Case Is <= 90
lngColor = RGB(255,255,0)
Case Else
lngColor = vbWhite
End Select

Me.expiredate.BackColor = lngColor
Forms!equipment.lblforthisform.BackColor = lngColor
Forms!trucks.lblforthistruck.BackColor = lngColor

Obviously, you need to come up with a way to get from this
third form back to the specific label on the other forms.
This could be done by naming the labels on the other forms
in such a way that the third form can calculate the name.
For example, if the second form has a text box (named
txtTruck) with the truck number and the truck labels on the
first form are named like lblTruck1234 for truck number
1234, then the third line above could be:

Forms!trucks("lblTruck" & _
Forms!equipment.txtTruck).BackColor = lngColor

 

[Guest]

Marshall, I've been trying to use that code but seem to be having problems
with it. I tried just using the first part on my text box, but it did not
seem to work.
I have
Dim lngColor As Long

Select Case DateDiff("d", Date, Retirement__Date)
Case Is <= 0
lngColor = RGB(255, 0, 0)
Case Is <= 90
lngColor = RGB(255, 255, 0)
Case Else
lngColor = vbWhite
End Select

And was just trying this out and it does not seem to work. Is there
something I am missing in here? I just want to get the color on the text box
right before I attempt getting the labels colored properly.

Thanks a lot

Alex

 

[Guest]

I found my mistake,
Thank you for all the trouble both of you have been through