UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Conditional Formatting With Vba, Access 2016    
 
   
Kim
post Sep 16 2019, 02:52 AM
Post#1



Posts: 21
Joined: 25-July 18



I have an applications which uses lots of conditional formatting. Different fields have different requirements, but in multiple instances there are close to 50 conditional format conditions. I would like to do this with code if possible to make it easier to set up and to maintain.
I am trying to figure out the best way to do this so that the formats are available across all of my forms and reports. It really comes down to two questions:

1. I have read in multiple places that you don't want to use too many Public Variables. I am not sure what the cutoff is and what the ramifications are. Would the variables I am creating be too big of a hit if I create them as public variables? My variables are all type long and I have 7 variables and 2 arrays, 1 with 11 elements, and 1 with 4. If it is not advisable to do this with public variables, what is another direction I could go with it?

2. My forms are datasheets, but I am thinking of switching them to continuous forms so I can use VBA instead of conditional formatting. The issue will be that for every field on every row of the form, I will have to either format it with the conditional formatting colors or format it with the default colors, since the colors or fonts may have been changed on a previous row. I am not sure how conditional formatting works, and I am wondering what the performance hit will be in both instances. Is conditional formatting changing the font of the control and then changing it back for each row (like I would have to do with code), or is it somehow changing the color displayed without changing the default color for the control which would be less of a hit performance wise?

This is the procedure that reads the format colors from a table and stores them in variables:

CODE
Public Sub SetFormatColors(ByRef arrRevHighlight() As Long, ByRef arrHighlight() As Long, _
    lngFontOwner As Long, lngFontAllow As Long, lngFontSubEst As Long, _
    lngFontVEandBidOptions As Long, lngFontVEandBidOptionsSelected As Long)
On Error GoTo Err_Handler

Dim strSQL As String
Dim rstHighlightAndRevisionColors As Recordset

'Set the revision highlight colors
strSQL = "Select FontOrHighlightIdentifier, ColorRGB from Bid_plHighlightAndRevisionColors " & _
    "Where ColorApplication = 'Conditional Format Highlights' AND NotUsed = 0"
Set rstHighlightAndRevisionColors = CurrentDb.OpenRecordset(strSQL)

With rstHighlightAndRevisionColors
    .MoveFirst
    Do While Not .EOF
        intArrayIndex = Val(!FontOrHighlightIdentifier)
        arrRevHighlight(intArrayIndex) = Eval(!ColorRGB)
       .MoveNext
    Loop
End With

'Set the highlight colors
strSQL = "Select FontOrHighlightIdentifier, ColorRGB from Bid_plHighlightAndRevisionColors " & _
    "Where ColorApplication = 'End-user highlights' AND NotUsed = 0"
Set rstHighlightAndRevisionColors = CurrentDb.OpenRecordset(strSQL)

With rstHighlightAndRevisionColors
    .MoveFirst
    Do While Not .EOF
        intArrayIndex = Val(!FontOrHighlightIdentifier)
        arrHighlight(intArrayIndex) = Eval(!ColorRGB)
       .MoveNext
    Loop
End With

'Set the font colors
strSQL = "Select FontOrHighlightIdentifier, ColorRGB from Bid_plHighlightAndRevisionColors Where ColorApplication = 'Font'"
Set rstHighlightAndRevisionColors = CurrentDb.OpenRecordset(strSQL)

With rstHighlightAndRevisionColors
    .MoveFirst
    Do While Not .EOF
        Select Case !FontOrHighlightIdentifier
            Case Is = "FontColorOwner"
                lngFontOwner = Eval(!ColorRGB)
            Case Is = "FontColorAllow"
                lngFontAllow = Eval(!ColorRGB)
            Case Is = "FontColorSubEst"
                lngFontSubEst = Eval(!ColorRGB)
            Case Is = "FontColorVEandOptions"
                lngFontVEandBidOptions = Eval(!ColorRGB)
            Case Is = "FontColorVEandOptionsSelected"
                lngFontVEandBidOptionsSelected = Eval(!ColorRGB)
        End Select
       .MoveNext
    Loop
End With

Exit_Handler:
    rstHighlightAndRevisionColors.Close
    Set rstHighlightAndRevisionColors = Nothing
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SetFormatColors, line " & Erl & "."
    Resume Exit_Handler
End Sub


This is the calling procedure I put in one of my reports for initial testing:

CODE
Option Compare Database
Option Explicit
Dim arrRevHighlightColor(1 To 11) As Long
Dim arrHighlightColor(1 To 4) As Long
Dim lngFontColorOwner As Long
Dim lngFontColorAllow As Long
Dim lngFontColorSubEst As Long
Dim lngFontColorVEandBidOptions As Long
Dim lngFontColorVEandBidOptionsSelected As Long
Dim lngBackColorWhite As Long
Dim lngFontColorBlack As Long
Dim intArrayIndex As Integer
Dim strType As String

Private Sub Report_Load()

Call SetFormatColors(arrRevHighlightColor, arrHighlightColor, lngFontColorOwner, lngFontColorAllow, _
    lngFontColorSubEst, lngFontColorVEandBidOptions, lngFontColorVEandBidOptionsSelected)
lngBackColorWhite = RGB(255, 255, 255)
lngFontColorBlack = RGB(0, 0, 0)

End Sub
Go to the top of the page
 
June7
post Sep 16 2019, 03:52 AM
Post#2



Posts: 885
Joined: 25-January 16



1. Global variables lose their values in a run-time error - which can be a nuisance in debugging. Instead of global variables for colors, use constants which will not lose assigned value. Can define custom constants; however, VBA has eight intrinsic color constants
vbWhite
vbBlack
vbBlue
vbGreen
vbYellow
vbRed
vbCyan
vbMagenta

2. Using VBA to change formatting properties will not work for form in Datasheet or Continuous view - cannot be dynamic per record, all records will reflect the setting. For report, code would be in OnFormat event which only runs for direct to printer or PrintPreview.

It is possible to use VBA to set Conditional Formatting rules. This was of real value back when Access allowed only 3 rules to be defined.

This post has been edited by June7: Sep 16 2019, 04:17 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
projecttoday
post Sep 16 2019, 04:18 AM
Post#3


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


Why do you need dynamic formatting? Why store them in variables? What is the layout of the formats table?

What's the purpose of the database?

--------------------
Robert Crouser
Go to the top of the page
 
Kim
post Sep 16 2019, 12:22 PM
Post#4



Posts: 21
Joined: 25-July 18



ProjectToday:

I don't need dynamic formatting, per se. I have a lot of report and form controls to format. In addition, there are a lot of conditions to be met on the different controls. Conditional formatting is very time consuming and it is a black box, whereas with code it is easy for me to apply similar formatting on multiple controls and to see and adjust the formatting on every control all in one place. Using code gives me the ability to look it over to be sure I have the correct formatting on all of the controls and to change it easily if a new condition is introduced.

I want to put the colors in variables so that I can use the same colors throughout my application with all of the color info stored in one place, so if I need to make a change as I am designing I can do it easily. Also to make sure my formatting is consistent in all of my forms and reports.

Formats Table:
ColorID = Autonumber
FontOrHighlightIdentifier = This may be a number or text depending on whether the field is being used as an index for an array or for a single variable 'Key field to identify the correct color row for the revision, font color, etc.
ColorDescription = "Olive" 'Descriptive color for end-user
ColorApplication = "Rev Color Highlights" 'Filter to use for combo box selection of formatting and for applying different formatting in code
ColorRGB = RGB(168, 196, 156)


I work for a general contractor and the database is for our bids. There are several ways in which conditional formatting is used:

1. After the first bid, there is the possibility of several bid revisions. Every field on the bid has a corresponding "Updated in Revision" field. When the value of a field changes, the "Updated in Rev" field is also changed to be the ID of the revision in which it was changed. Each revision uses the ColorID field to tie a highlight color to it, so that any field that was changed in that bid revision will be highlighted with that revision's color. As you go through multiple revisions there will be fields from past revisions highlighted in the color of those revisions, in addition to fields highlighted with the color of the current revision. At the top of the bid is a list of revisions w/ their dates highlighted in the color tied to that revision, which acts as a key for the colors.

2. There is also the possibility that an entire row will be highlighted, which would override the revision color.

3. There are a couple of fields where the font color will change based on the value of the field.

4. There are rows that will be formatted with a different font color if they have been "Selected".

5. There are two subforms/subreports where all of the fields will have a blue font as the default, and then be changed according to 1, 2 and/or 4.
Go to the top of the page
 
projecttoday
post Sep 16 2019, 02:09 PM
Post#5


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


It doesn't make sense to me but if you know what you're doing ... Is the database properly normalized?

--------------------
Robert Crouser
Go to the top of the page
 
Kim
post Sep 16 2019, 03:06 PM
Post#6



Posts: 21
Joined: 25-July 18



June17:

Thanks for the input. I forgot that continuous forms would behave in the same way as a datasheet, so that was helpful to save me the time of testing that path.

I am going to do some research on using VBA to set up conditional formatting. That would be the most straightforward way to go because I could use it for both my datasheets and my reports.
Go to the top of the page
 
June7
post Sep 16 2019, 03:09 PM
Post#7



Posts: 885
Joined: 25-January 16



Here's a start https://blogs.msdn.microsoft.com/frice/2004...ccess-with-vba/

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Kim
post Sep 16 2019, 03:25 PM
Post#8



Posts: 21
Joined: 25-July 18



Thanks! This is exactly what I needed.
Go to the top of the page
 
Kim
post Sep 16 2019, 07:25 PM
Post#9



Posts: 21
Joined: 25-July 18



June7:

This was running along really great until I hit a couple of controls that would not accept more than 3 conditions. I tried it for nine controls - four worked and five said: "Runtime Error 7966: "The format condition number you specified is greater than the number of format conditions." When I check the control there are only 3 conditions created. Some are comboboxes, some are text boxes, some are bound, some are unbound. I even changed one of the working textboxes into a combobox and it worked as a both. The only commonality of the five controls is that their datasource is a numeric value. Just to be sure I changed one of my controls that was bound to a numeric field and I bound it to a text field. It worked when it was bound to a text field, but not when it was bound to a numeric field. I wouldn't have thought my report would be checking at this point to see what datatype was bound to the control.

I found a similar question being asked here: https://www.UtterAccess.com/forum/index.php...owtopic=2034035, posted in January 2016. They came to the conclusion that "MS forgot to update the VBA side when they updated the UI side in 2010".

It is odd that it works for certain controls and not others. Has anyone run into this and been able to solve it?
Go to the top of the page
 
June7
post Sep 16 2019, 09:27 PM
Post#10



Posts: 885
Joined: 25-January 16



It's doubly odd that it work when control was bound to text field but not number field. I have never used this technique so have no advice.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
theDBguy
post Sep 17 2019, 07:40 AM
Post#11


Access Wiki and Forums Moderator
Posts: 76,409
Joined: 19-June 07
From: SunnySandyEggo


Hi Kim. Can you post a sample of the code you're using? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
strive4peace
post Sep 17 2019, 07:50 AM
Post#12


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Kim,
> "until I hit a couple of controls that would not accept more than 3 conditions"
I assume this might happen with controls that were created before Access could have more format conditions.

in those cases, you can:
1. create a new control
2. transfer all relevant properties
3. copy control name of original control
4. delete original control
5. rename new control with original control name



This post has been edited by strive4peace: Sep 17 2019, 07:51 AM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
Kim
post Sep 17 2019, 12:19 PM
Post#13



Posts: 21
Joined: 25-July 18



Crystal:

This project was created in Access 2016 so nothing was created in a previous version of Access.
This post has been edited by Kim: Sep 17 2019, 12:39 PM
Go to the top of the page
 
Kim
post Sep 17 2019, 12:38 PM
Post#14



Posts: 21
Joined: 25-July 18



It had occurred to me last night that I could add an unbound field to my form, create the conditional formats on that field and then use the paintbrush to copy the formatting to my actual fields. That worked! It was a nuisance, but easier than doing the whole thing manually.

It struck me in the middle of the night that I could take that a step further by removing the source property from the fields in question to make them unbound, add the conditional formats and then restore the source property, all as part of my code. That would allow me to create the conditional formats without any manual intervention. I tried it his morning and Hooray!!! It worked. I will have to use this workaround on any of my controls where the source is a numeric datatype, but I am just a happy camper that I can make it work with code.

I am still baffled by the indication that it won't work based on the datatype of the underlying field. As I use this code through the rest of my forms and reports, I will report back if I find any anomalies that disprove or expand upon my current understanding of it.

I have included the code that I used for a couple of the fields in question.

Thanks so much to everyone for their input!!!



CODE
Sub FormatClassAndTypeFields(arrRevHighlightColor, arrHighlightColor, lngFontColorOwner, lngFontColorAllow, _
    lngFontColorSubEst, lngFontColorVEandBidOptions, lngFontColorVEandBidOptionsSelected)
Dim objControlToFormat As Object
Dim ofc As FormatCondition
Dim strExpression As String
Dim iArrayIndex As Integer

'---------------------------------------------------------
'Conditional Formatting for Class
'---------------------------------------------------------
Set objControlToFormat = Application.Reports("Bid_BidReport").Controls("cmbClassID")
objControlToFormat.FormatConditions.Delete

'VBA cannot add more than 3 Conditional Formats if the source field is a numeric datatype
'Remove the source field to make it unbound, add the conditional formats, then add the source field back in at the end
objControlToFormat.ControlSource = ""

'Highlight and Type = "Owner" Conditions
For iArrayIndex = 1 To 4
    strExpression = "[cmbHighlightRowID].Column(3)=" & iArrayIndex & " and cmbClassID.Column(2)='Owner'"
    Set ofc = objControlToFormat.FormatConditions.Add(acExpression, , strExpression)
    ofc.BackColor = arrHighlightColor(iArrayIndex)
    ofc.ForeColor = lngFontColorOwner
Next iArrayIndex

'RevHighlight and Type = "Owner" Conditions
For iArrayIndex = 1 To 11
    strExpression = "[cmbClassUpdatedInRevision].Column(3)=" & iArrayIndex & " and cmbClassID.Column(2)='Owner'"
    Set ofc = objControlToFormat.FormatConditions.Add(acExpression, , strExpression)
    ofc.BackColor = arrRevHighlightColor(iArrayIndex)
    ofc.ForeColor = lngFontColorOwner
Next iArrayIndex

'Highlight Conditions
For iArrayIndex = 1 To 4
    strExpression = "[cmbHighlightRowID].Column(3)=" & iArrayIndex
    Set ofc = objControlToFormat.FormatConditions.Add(acExpression, , strExpression)
    ofc.BackColor = arrHighlightColor(iArrayIndex)
Next iArrayIndex

'RevHighlight Conditions
For iArrayIndex = 1 To 11
    strExpression = "[cmbClassUpdatedInRevision].Column(3)=" & iArrayIndex
    Set ofc = objControlToFormat.FormatConditions.Add(acExpression, , strExpression)
    ofc.BackColor = arrRevHighlightColor(iArrayIndex)
Next iArrayIndex

'Restore the source field after creating the conditional formats
objControlToFormat.ControlSource = "ClassID"

'---------------------------------------------------------
'Conditional Formatting for Type
'---------------------------------------------------------
Set objControlToFormat = Application.Reports("Bid_BidReport").Controls("cmbTypeID")
objControlToFormat.FormatConditions.Delete

'VBA cannot add more than 3 Conditional Formats if the source field is a numeric datatype
'Remove the source field to make it unbound, add the conditional formats, then add the source field back in at the end
objControlToFormat.ControlSource = ""

'RevHighlight and Type = "Owner" Conditions
For iArrayIndex = 1 To 11
    strExpression = "[cmbTypeUpdatedInRevision].Column(3)=" & iArrayIndex & " and cmbTypeID.Column(2)='Owner'"
    Set ofc = objControlToFormat.FormatConditions.Add(acExpression, , strExpression)
    ofc.BackColor = arrRevHighlightColor(iArrayIndex)
    ofc.ForeColor = lngFontColorOwner
Next iArrayIndex

'RevHighlight and Type = "Allow" Conditions
For iArrayIndex = 1 To 11
    strExpression = "[cmbTypeUpdatedInRevision].Column(3)=" & iArrayIndex & " and cmbTypeID.Column(2)='Allow'"
    Set ofc = objControlToFormat.FormatConditions.Add(acExpression, , strExpression)
    ofc.BackColor = arrRevHighlightColor(iArrayIndex)
    ofc.ForeColor = lngFontColorAllow
Next iArrayIndex

'RevHighlight and Type = "Sub Est" Conditions
For iArrayIndex = 1 To 11
    strExpression = "[cmbTypeUpdatedInRevision].Column(3)=" & iArrayIndex & " and cmbTypeID.Column(2)='SubEst'"
    Set ofc = objControlToFormat.FormatConditions.Add(acExpression, , strExpression)
    ofc.BackColor = arrRevHighlightColor(iArrayIndex)
    ofc.ForeColor = lngFontColorSubEst
Next iArrayIndex

'Highlight Conditions
For iArrayIndex = 1 To 4
    strExpression = "[cmbHighlightRowID].Column(3)=" & iArrayIndex
    Set ofc = objControlToFormat.FormatConditions.Add(acExpression, , strExpression)
    ofc.BackColor = arrHighlightColor(iArrayIndex)
Next iArrayIndex

'RevHighlight Conditions
For iArrayIndex = 1 To 11
    strExpression = "[cmbTypeUpdatedInRevision].Column(3)=" & iArrayIndex
    Set ofc = objControlToFormat.FormatConditions.Add(acExpression, , strExpression)
    ofc.BackColor = arrRevHighlightColor(iArrayIndex)
Next iArrayIndex

'Restore the source field after creating the conditional formats
objControlToFormat.ControlSource = "TypeID"

End Sub
Go to the top of the page
 
theDBguy
post Sep 17 2019, 01:27 PM
Post#15


Access Wiki and Forums Moderator
Posts: 76,409
Joined: 19-June 07
From: SunnySandyEggo


Hi Kim. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Kim
post Sep 18 2019, 10:26 AM
Post#16



Posts: 21
Joined: 25-July 18



I have tested my code on all of my reports and forms, and the only other thing I discovered is that the code also won't work for controls bound to date fields. So with controls bound to numeric or date type fields it is necessary to remove the source to do the formatting and then put the source back on. With that piece added it is working great and I am so, so glad to have finally gotten it done. Thanks again to all of you!

Is this something that one of you Access gurus could report to Microsoft so it will be fixed in a future version? I am using Access 2016. I couldn't say if it works in 2019 or 365.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st October 2019 - 05:36 AM