Conditional Formatting in Access Form

[DannyBoyGunner]

Hi Everyone,

I've got a DB that's setup to show a list of our all sub-contractors that we use. The main goal is to show details of each subbie and if they meet the approval criteria or not. This way our contracts team only place orders with approved subbies.

The main field is the 'Approval' field that is simply a YES / NO drop-down. Previously we've had simple conditional formatting on this to turn green if YES (approved) and red for NO (not approved). However, now we're wanting to push that a little further and have it reference another field called 'Date Received' which is the date we've taken the data in from the subcontractors. I presume we would setup three slightly tweaked conditional rules with the colour changes for each one and then parameters for the date field altered for each colour.

The additional formatting we're looking for is listed below:

Green – They have been approved (from the drop-list) AND the date in the 'Date Received' field is less than 12 months old

Amber – They have been approved (from the drop-list) BUT the data is between 12 months old and 24 months old and needs renewing

Red – They have not been approved and/or, the data is more than 24 months old and needs renewing and no orders can be placed


If anyone could offer any help that would be greatly appreciated.

Thanks

 

[stumac]

I would use 'Expression Is' in conditional formatting rules:

Green:

[Approval]="Yes" And DateDiff("yyyy",[Date Received],Now())=0

Amber:

[Approval]="Yes" And DateDiff("yyyy",[Date Received],Now())=1

Red:

[Approval]="No" Or DateDiff("yyyy",[Date Received],Now())>1

 

[DannyBoyGunner]

Spot On!!! I altered the names of the fields and it worked first time. Thanks very much for that!!

Hopefully this is now fit for purpose so I've passed it on to the Directors for approval before they role it out.

One other quick slightly related question....

The main form that we're getting everyone to use to display the sub-contract data it always appears to open on a particular client (no idea why) When it does this you can't use the navigation arrows to look through any other data until you've pressed the 'Clear' button that I created on the form to clear off all searches. Once this is pressed it goes back to the first client on the list under A and it works fine. Is there anyway to default this to always display the data in A-Z order?

Thanks

 

[stumac]

It sounds like you have a filter in the open form code or Macro.

Are you opening the form from another form? Have a look at the code or the macro running when you click to open the form:

code would be something like: docmd.OpenForm "MyForm",,"X","Y",,,

Where the X & Y would be your problem.

Alternatively it could be code running on the ****** or OnOpen event of the problematic form that is filtering it.

 

[DannyBoyGunner]

I managed to spot there was a filter for that specific company in the 'Filter' section on the property sheet so once that was deleted it works spot on now.

I've just heard back from the Director and he's worried that if a company is approved manually by us but then their details go over the 24 month period, although the conditional formatting turns the box RED, the actual text still says 'YES' for the approved status.

Is there anyway to add on to the current formula which will allow us to alter the text in the 'Approved' field to turn that from a YES to a NO once it goes over the 24 month period?

If this isn't possible then could we create a new field that we can populate with one of three sentences ie:

Green/Approved - Currently Approved
Amber/Approved - Approved but requires updating
Red/Not Approved - Not Approved, Do not use

Or similar.

Thanks again!

 

[DannyBoyGunner]

If anyone knows how to help on my last post that would be greatly appreciated.

 

[welshgasman]

I *think* you would need code in the current event to check the date part of that formula and change it to NO, and then it would be up to you to change the date to stop it happening again.? After all that is what is happening with the CF expression?
Alternatively run a batch update each day to do the same thing.? It really depends on how you work, or want it to work. the first method would only work if you go to that particular record.?

If you are relying on queries looking at that data, and not having visited that record, that is a non starter.?
For the batch method you would have to decide when it needs to run.?

FWIW I think the filter in the Property sheet, is just the last filter left on the form.? I've noticed that in my forms.

 

[DannyBoyGunner]

Thanks for the reply.

I'm really needing the code itself as I'm not well versed with forumula.

The idea would be for it to be automated, as the users of the DB would need it to tell them in real time if that sub contractor is approved or not with a colour to display how old their data is.

An admin would enter all the data initirally then the staff from the contracts dept would use it before placing orders with subbies to ensure they are

Thanks

 

[welshgasman]

In the On_Current event place code along the lines of

If DateDiff("yyyy",[Date Received],Now()) >1 Then
    Me.Approval = "NO"
End If

I do not think you need the Else statement.
It will be up to someone to change the Date Received to under two years, so that this code does not execute.

Thanks for the reply.

I'm really needing the code itself as I'm not well versed with forumula.

The idea would be for it to be automated, as the users of the DB would need it to tell them in real time if that sub contractor is approved or not with a colour to display how old their data is.

An admin would enter all the data initirally then the staff from the contracts dept would use it before placing orders with subbies to ensure they are

Thanks

 

[stumac]

You can calculate it on the form using a text box with the following expression (untested)

=IIf(Nz([Approval],"No")="No" Or DateDiff("yyyy",[Date Received],Now())>1,"Not Approved, Do not use",IIf(DateDiff("yyyy",[Date Received],Now())=1,"Approved but requires updating","Currently Approved"))

Then apply the same conditional formatting rules to this text box as you did with the above.

You should ensure that this same logic is applied if extracting data from the database using a query