David has attended:
Access VBA course
VBA conditional formatting
Hi. I've put the code below in to my form and the ID field formats red, green and yellow fine, however, the time_limit field is purely red. Is it not possible to use dynamic conditional formatting in vba or is there something wrong with my code? Thanks. David.
Private Sub Form_Load()
Dim objFrc As FormatCondition
'delete existing formats
Me.ID.FormatConditions.Delete
Me.TIME_LIMIT.FormatConditions.Delete
'create three format objects and add them to the FormatConditions collection
Set objFrc = Me!ID.FormatConditions.Add(acFieldValue, acLessThan, 4)
Set objFrc = Me!ID.FormatConditions.Add(acFieldValue, acEqual, 4)
Set objFrc = Me!ID.FormatConditions.Add(acFieldValue, acGreaterThan, 4)
'create three format objects and add them to the FormatConditions collection
Set objFrc = Me!TIME_LIMIT.FormatConditions.Add(acFieldValue, acLessThan, DATE)
Set objFrc = Me!TIME_LIMIT.FormatConditions.Add(acFieldValue, acEqual, DATE)
Set objFrc = Me!TIME_LIMIT.FormatConditions.Add(acFieldValue, acGreaterThan, DATE)
'format the time_limit text box with each condition
With Me.ID.FormatConditions(0)
.BackColor = vbGreen
End With
With Me.ID.FormatConditions(1)
.BackColor = vbYellow
End With
With Me.ID.FormatConditions(2)
.BackColor = vbRed
End With
With Me.TIME_LIMIT.FormatConditions(0)
.BackColor = vbGreen
End With
With Me.TIME_LIMIT.FormatConditions(1)
.BackColor = vbYellow
End With
With Me.TIME_LIMIT.FormatConditions(2)
.BackColor = vbRed
End With
End Sub
RE: VBA conditional formatting
Hi David
Thank you for your question.
This question is rather hard to answer without seeing the database in question.
One possibility would be to run your code from the form's load method rather than the open method.
If this doesn't work please let me know, and I will explore other possibilities.
Regards
Stephen
RE: VBA conditional formatting
Hi David
Thank you for your question.
This question is rather hard to answer without seeing the database in question.
One possibility would be to run your code from the form's load method rather than the open method.
If this doesn't work please let me know, and I will explore other possibilities.
Regards
Stephen
RE: VBA conditional formatting
Hi Stephen
Thanks for looking into this for me. I've used both on open and on load method but both produce the same results.
In terms of the db; its actually just a test db to test the conditional formatting before I apply it to the main db. The table the form is looking at is 2 columns by 7 rows. Column 1 is the ID (1-7) and column 2 is a date field containing 7 dates that are around DATE, even to the extreme of one at 1974 and one at 2020.
As stated, the ID formatting works on the threshold of 4 in the code but the date is purely red.
Thanks
David
RE: VBA conditional formatting
Hi Stephen
I've had time to play around with this now, it looks like the conditional formatting is based on numbers only so I've done a CLng(DATE) to get the 40240 for today and the date field is now formatting red, yellow and green.
Thanks for your time on this one.
Regards
David
