UtterAccess Discussion Forums > Conditional Formatting, based on username...

Full Version: Conditional Formatting, based on username...

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

adaytay

Jan 7 2008, 06:02 AM

Hi all,

A couple of our teams here use a form I've set up to view all their files in one go. Until not I've always had conditional formatting to turn the "last chased" date to red if the date was more than 5 days in the past, however I've now been asked by one of the departments to change the "switchover" so that, just for their files, the "last chased" date changes to red if the date is more than 10 days old.

Obviously I can't use normal conditional formatting as I need to look up the department ID based on the user (it's stored already so no problems). I've done the following code, but it's not working right?

Incidentally, the code is sitting behind the Form_Current event of the subform (which is a continuous form) - I'm not sure it's in the right place....

Any ideas?

Cheers,

Ad

CODE

Private Sub Form_Current()

    'Code fires on Form_Current.  We have a login selected and can find out the department from this.



    Dim strLogin As String, lngDepartment As Long

    strLogin = Me.Login



    'Find the department

    lngDepartment = tLookup("StaffDepartment", "tblStaff", "Login='" & strLogin & "'")

    Select Case lngDepartment

        Case 4  'Tech & Legal

            'It's Tech / Legal, so format based on 10 day cutoff

            If Me.LastChased < AddDays(Today(), -10) Then

                'Last chased is more than 10 working days ago - format accordingly

                With Me.LastChased

                    .BackColor = vbRed

                    .FontBold = True

                    .ForeColor = 10092543

                End With

            Else

                'Last chased date is less than 10 days old.

                With Me.LastChased

                    .BackColor = 10092543

                    .FontBold = False

                    .ForeColor = vbBlack

                End With

            End If

        Case Else

            'Otherwise set it as 5 days...

            If Me.LastChased < AddDays(Today(), -5) Then

                With Me.LastChased

                    .BackColor = vbRed

                    .FontBold = True

                    .ForeColor = 10092543

                End With

            Else

                With Me.LastChased

                    .BackColor = 10092543

                    .FontBold = False

                    .ForeColor = vbBlack

                End With

            End If

        End Select



End Sub

Larry Larsen

Jan 7 2008, 06:12 AM

Hi Ad
Some thing different about tLookup()..?

CODE

lngDepartment = [color="red"]tLookup[/color]("StaffDepartment", "tblStaff", "Login='" & strLogin & "'")

adaytay

Jan 7 2008, 06:14 AM

Hey Larry - long time no "see", how's things??

Yeah I use tlookup rather than DLookup - it does the same thing, it's just quicker!

Ad

Peter46

Jan 7 2008, 06:24 AM

You can not simulate conditional formatting by using code.
All that will happen is that all instances of the textbox will follow the formatting applicable to the current record.

You would need to introduce a more complex condition into your conditional formatting rules.
Create a function to get the department.
then use something like this in the conditional formatting expression:

fnGetDpt()= 4 and (Date()-[LastChased])>10

adaytay

Jan 7 2008, 06:35 AM

You can't? Ack, that's a pain!!

Never mind - Thanks Peter.

Luckily there are only 2 departments that currently use this screen and require different dates - which means 3 levels of formatting... This ultimately leads to my next question however - how would I adapt your solution to allow for 3 or more departments - bearing in mind I've got a maximum of 3 conditions?

(Just throwing this one "out there"...)

Ad

Larry Larsen

Jan 7 2008, 06:46 AM

Hi Ad
Doing fine thanks..
Health is well but the old golf swing is a little frustrated at the moment..

It been a while since I referenced the alternative option to using DLookUp()..

Peter seems to have stopped you in your tracks.. with a little bit of sad news.
(he's alternative option is worth pursuing)

adaytay

Jan 7 2008, 06:57 AM

Hey Larry

Glad to hear you're doing well!! I really must get into this golf game, don't have time for it unfortunately these days!! That's a major shame too as I live like 3 miles from a really nice course!!

I can't remember where I got tLookup from originally... it will have been this site, will dig out the link for you.

It's a shame that I can't mimic conditional formatting. I'm just trying to figure out the alternative...

I've created the function to return the department (that was a no-brainer). The problem is I really want to include my custom function (AddDays) as this will correctly age the claim, taking bank holidays and weekends into account.

Trouble is, I'm having a hard time balancing the equation... maths isn't my strong point (a bit daft, me being a programmer and all... lol).

CODE

If Me.LastChased < AddDays(Today(), -10) Then

The above is what I used in the function - me.lastchased is a date, AddDays is a function that returns a date. I want to format it so it looks like Peter's example, ie

CODE

Date()-[LastChased]>10

- however am having trouble rejigging the equation to get it balanced... can anyone help?

Cheers,

Ad

vtd

Jan 7 2008, 07:48 AM

Hi Ad

The 5 days, 10 days, etc ... do not have to be explicit values which require different format conditions.

You can simply include the days applicable to each Department as a Field in the tblDepartment and obtain this value in the format condition expression. This way, you can use one format condition for different values for different Departments.

adaytay

Jan 7 2008, 07:51 AM

mmmmm... yes, like your thinking Van, thank you!! I might give that a whirl.

Ad

vtd

Jan 7 2008, 07:56 AM

Today() function in your partial If statement???

Not sure but try:

AddDays(Date() - 10) - [LastChased] > 0

(you may need to change > operator to >= operator if appropriate)

adaytay

Jan 7 2008, 08:01 AM

Think I've got it... this seems to work:

CODE

AddDays(Today(),-10)-"LastChased">=0

A bit messy but seems to do the job, if the formula result is 0, then it'll apply the formatting.

vtd

Jan 7 2008, 08:06 AM

Actually, I think Stephen Lebans wrote somewhere that the max. 3 Conditions is the limit of the GUI, not Conditional Formatting feature. I can't remember what the real limit is but it is certainly much larger than 3. The only trouble is that you have to use VBA to create the additional Conditions.

OTOH, you need only 1 Condition (plus the default) as previously discussed.

Cheers

adaytay

Jan 7 2008, 08:22 AM

Thanks Van.

Very much appreciated!!

Ad

Peter46

Jan 7 2008, 08:34 AM

You can create cf conditions in code but you are limited to a max of 3 operative conditions at any time.
So in a single view form you could change the 3 conditions to a completely different set as you moved from record to record, but only 3 condtions would be available for each record. But if you have a single view form, there are a million other ways available (slight exaggeration there).

adaytay

Jan 7 2008, 11:10 AM

Sorted. It's blimming slow (using the CF with a OR statement) - but working fine.

Thanks for all the help folks...

Ad

vtd

Jan 7 2008, 06:48 PM

You're welcome ... Glad you worked it out ...

If possible, post the database apd specify where to look and see if anyone can improve the speed of C.F. processing.

Cheers

adaytay

Jan 8 2008, 04:24 AM

Hi Van...

Would do... although the FE is 20 MB (170 forms), and the BE currently weighs in at 350MB.

So... I think I'll pass? Lol!!

vtd

Jan 8 2008, 12:21 PM

Hi Adam

Np problem. If you think it is worth a try, you can:

1. If the database doesn't have confidential data (which is hard) and you have somewhere you can put the zipped file up for downloads, post details here and I can download the files.

2. Alternatively, strip the BE of most of the data except for a small set of de-sensitized sample data ... and send the zipped files to VanThienDinh AT hotmail DOT com (I think hotmail allows 10 MB attachment) and other volunteers if they want.

Cheers

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.