complex conditional formatting question 2

[Phideaux]

I am trying to use conditional formatting to solve a problem in a MS Access Form.

I’m trying to turn a field’s conditional formatting on if a value passes a certain point based on the value set in another field. In this case it’s for Work Orders that have been open too long.

Each work order has a priority assigned. Each priority has a number of days it can be open and still be in a normal condition. Example: a priority # 1 is normal if it is closed in less than 4 days if it is open longer it is not normal and I want the back ground of the “Days Open” field on another form to change background or text color so that I know the w/o has been open too long.

Priorities are stored in a table named “Priority” each priority has a number of days maximum opening time stored in the priority record.

Example: “priority field” = 2 / the “days open field” = 4 . The days open field is the maximum number of days that any W/O’s assigned this priority can be open and still be normal.

The next record in the priority table might have a “Priority field” = 4 / “Days open” field = 20.

The actual field that I want to change is in a work order form. It has several fields in it. The fields that display the needed information are “Priority” and “number of days open”.
The priority field can range from 1 to ?? each priority number has a corresponding # of days ( as I outlines above) to be open and still be normal, if the number of days related to priority # 4 is listed in the priority table record as 20 or less I don’t want the conditional format of the “Days Open” field to change. If it is more I want the conditional format to change and the text to become a different color or the background to change color or some other mechanism to let me know at a glance that the work order has been open too long. (I’d prefer Bold type in Red if possible)

The “Days Open” field on the work order form is calculated from the date opened and today’s date to give a number of days since the W/O was opened.

This one is way over my head and I’ve read quite a bit of written info looking for a solution to this one. Any help is solving this problem will be greatly appreciated. Thanks in advance for taking a few minutes to read and think about how to solve this one.




Phideaux

The mind is the best toy.
Play with someone else's often

 

[willir]

Phideaux

You can use a function to perform the test. Or a subroutine within the form. You can use conditional formatting, or control formatting yourself via code as long as you are using a single form. (Conditional formatting is better for a continous firm)

If you want to use DaysOpen control text box, then setting the colour via code. For example the OnCurrent event procedure.

Dim intDaysCrtical as Integer

Select Case [priority field]

   Case 1
      intDaysCrtical = 1    'one day

   Case 2
      intDaysCrtical = 4    'four days

   Case 3
      intDaysCrtical = 10    ' or what ever you want

   Case 4
      intDaysCrtical = 20    ' do days right?

End Case

If DaysDiff("d", Date, DateOpen) > intDaysCritical Then
    Me.[Days Open].Backcolor = vbRed
Else
    Me.[Days Open].Backcolor = vbGreen
End

This is just a sample since I do not know the name of all your control boxes on the form, but hopefully, you get the idea.

Richard

 

[Lightning]

So, you want to change the display of a control on the Work order form, based on the values stored in the Priority table?

If there are more than three different priority ratings, the built-in Conditional formatting won't help you, as it is limited to three options only.

Try using DLOOKUP to get the value from the priority table and then use a Select Case statement to compare the value to your current record. Then set the control using the Forecolor and Backcolor properties. This should all be done in the On Current event of your form.

HTH
Lightning