My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Conditional Formatting, based on username...
|
|
 Jan 7 2008, 06:02 AM
Post
#1
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England.  |
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 |
 |
 
|
|
Jan 7 2008, 06:12 AM
Post
#2
|
|
|
UA Editor + Utterly Certified Posts: 22,722 From: Melton Mowbray,Leicestershire (U.K) |
Hi Ad
Some thing different about tLookup()..? CODE lngDepartment = [color="red"]tLookup[/color]("StaffDepartment", "tblStaff", "Login='" & strLogin & "'") (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif) |
|
|
|
|
Jan 7 2008, 06:14 AM
Post
#3
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
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 |
|
|
|
|
Jan 7 2008, 06:24 AM
Post
#4
|
|
|
UtterAccess VIP Posts: 7,395 From: Oadby Leics, UK |
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 |
|
|
|
|
Jan 7 2008, 06:35 AM
Post
#5
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
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 |
|
|
|
|
Jan 7 2008, 06:46 AM
Post
#6
|
|
|
UA Editor + Utterly Certified Posts: 22,722 From: Melton Mowbray,Leicestershire (U.K) |
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) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif) |
|
|
|
|
Jan 7 2008, 06:57 AM
Post
#7
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
Hey Larry (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif) 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 |
|
|
|
|
Jan 7 2008, 07:48 AM
Post
#8
|
|
|
Retired Moderator Posts: 19,667 |
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. |
|
|
|
|
Jan 7 2008, 07:51 AM
Post
#9
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
mmmmm... yes, like your thinking Van, thank you!! I might give that a whirl.
Ad |
|
|
|
|
Jan 7 2008, 07:56 AM
Post
#10
|
|
|
Retired Moderator Posts: 19,667 |
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) |
|
|
|
|
Jan 7 2008, 08:01 AM
Post
#11
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
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. |
|
|
|
|
Jan 7 2008, 08:06 AM
Post
#12
|
|
|
Retired Moderator Posts: 19,667 |
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 |
|
|
|
|
Jan 7 2008, 08:22 AM
Post
#13
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
Thanks Van. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif) Very much appreciated!!
Ad |
|
|
|
|
Jan 7 2008, 08:34 AM
Post
#14
|
|
|
UtterAccess VIP Posts: 7,395 From: Oadby Leics, UK |
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). |
|
|
|
|
Jan 7 2008, 11:10 AM
Post
#15
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
Sorted. It's blimming slow (using the CF with a OR statement) - but working fine.
Thanks for all the help folks... Ad |
|
|
|
|
Jan 7 2008, 06:48 PM
Post
#16
|
|
|
Retired Moderator Posts: 19,667 |
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 |
|
|
|
|
Jan 8 2008, 04:24 AM
Post
#17
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
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!! |
|
|
|
|
Jan 8 2008, 12:21 PM
Post
#18
|
|
|
Retired Moderator Posts: 19,667 |
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 |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 24th May 2013 - 08:16 AM |