Advanced Conditional Formatting
This tip on Advanced Conditional Formatting explains how to use VBA code to add add additional conditional formatting options to your Microsoft Access database. It is suitable for all versions of Microsoft Access.
One of my clients asked set up a database where the user could visually see where a particular process was up to. There were four stages. Each stage was assigned to a square object on a form, therefore there were four squares. As each stage was completed he wanted the square relating to that stage to change color, he wanted to achieve this by clicking on the square.
If no stages were complete – all four squares were white.
If the first stage was complete, the first square would be clicked and would turn yellow, with the remaining 3 squares white.
If the second stage was complete, the first square remained yellow and the second square changed to orange when clicked, with the last two squares remaining white.
If the third stage was completed then the first two squares remained yellow and orange respectively and the third square turned blue.
If the fourth stage (therefore all stages of the process) was completed, then all the squares turned green. This meant the process was finished and the product was ready.
Are you following so far?
This process is needed to be undertaken for each appointment throughout the day, therefore a continuous form was needed to display all the appointments, thus the need for advanced conditional formatting.
If it was just in a single form, then conditional formatting would not be needed, you could achieve the same result by updating the colours by code. This is not possible in a continuous form as not all the records have the focus; therefore it is impossible for formatting to change on all of the records without using conditional formatting and as this is a bit more complicated, we will need to use some code to achieve our result, thus the reason I have called it Advanced Conditional Formatting.
To set the above up, first:
Create a new table with the following fields:
ID – Autonumber
Time - Date/Time (Short Time)
StoreValue – Number
Add the some times, into the time section. I added all times in half hour intervals from midnight to midnight.
Build a continuous form based on this table. Include just the time field and the store value field.
Now we want four instances of the ‘StoreValue’ text field, you are most likely to only have one at present. Therefore copy and paste this field three more times so as you have four. Line these up all in a row beside each other. Change the names of each of these text fields to:
SV1
SV2
SV3
SV4
You can do this by right clicking on each of them, selecting ‘Properties’ from the drop down list and clicking on the ‘Other’ tab in the properties window, then next to ‘Name’ enter the above names. This will help keep things consistent.
Change the font to white, so as you cannot see the writing. This makes it look like you have 4 white boxes on each of the records on your continuous form.
The next step is disabling all the boxes except the first one, this is to control the user on which box they can click. Therefore set the following properties for the last three boxes (can be found under the ‘Data’ tab in the properties window)
Enabled = False
Locked = True
These text fields are our four squares.
The next thing we are going to do is to set the conditional formatting for each of the boxes depending on the value of ‘StoreValue’ as each box is linked to the one field ‘StoreValue’ then the value for each of the boxes in each of the records is the same at any one time. i.e. if the StoreValue has a value of 1, then 1 would be displayed in each of the four squares.
Click on your first square 'SV1', then on the menu bar, select ‘Format’ then ‘Conditional Formatting’ (Microsoft Access 2003 or earlier).
For Access 2007 or later you will find the conditional formatting button on the 'Design' ribbon, it is now just called 'Conditional'.
The conditional formatting window will open.
You will see the default is white on white. In the Conditon1 sections enter the following in the appropriate area:
Field Value Is Between 1 and 3
Choose ‘Yellow’ background and font.
Click on Add, then add the following to the next section:
Field Value Is Equal To 4
Choose ‘Green’ background and font.
This changes the square yellow, if SV1 value is 1, 2, or 3 and green if the SV1 value is 4.
Repeat for SV2 with the following scenarios:
Field Value Is Between 2 and 3 (Orange background and font)
Field Value Is Equal To 4 (Green background and font)
SV3
Field Value Is Equal To 3 (Blue background and font)
Field Value Is Equal To 4 (Green background and font)
SV4
Field Value Is Equal To 4 (Green background and font)
Once you have all that set up, we now need to add some coding to change the value of ‘StoreValue’ this will in turn change the colors of the squares. This is the Advanced Conditional Formatting part.
If you have not coded before do not worry as I will step you through it.
Select the first square SV1, open the properties window for this textbox and click on the ‘Event’ tab. Next to ‘On Click’ click on the ‘…’ button. You will may need to place your cursor next to ‘On Click’ for this button to appear.
Select ‘Event Procedure’ if prompted.
The VBA coding window will open with the following two lines of code:
Private Sub SV1_Click()
End Sub
In between these two lines of code type the following:
If Me!SV1 = 0 Then
Me!SV1 = 1
Me!SV1.Enabled = True
Me!SV1.Locked = False
Me!SV2.Enabled = True
Me!SV2.Locked = False
Me!SV3.Enabled = False
Me!SV3.Locked = True
Me!SV4.Enabled = False
Me!SV4.Locked = True
Else
If Me!SV1 = 1 Then
Me!SV1 = 0
Me!SV1.Enabled = True
Me!SV1.Locked = False
Me!SV2.Enabled = False
Me!SV2.Locked = True
Me!SV3.Enabled = False
Me!SV3.Locked = True
Me!SV4.Enabled = False
Me!SV4.Locked = True
End If
End If
Close the VBE window by clicking on the outer X.
Repeat for SV2, but this time add the following code to the ‘On Click’ event:
If Me!SV1 = 1 Then
Me!SV1 = 2
Me!SV1.Enabled = False
Me!SV1.Locked = True
Me!SV2.Enabled = True
Me!SV2.Locked = False
Me!SV3.Enabled = True
Me!SV3.Locked = False
Me!SV4.Enabled = False
Me!SV4.Locked = True
Else
If Me!SV1 = 2 Then
Me!SV1 = 1
Me!SV1.Enabled = True
Me!SV1.Locked = False
Me!SV2.Enabled = True
Me!SV2.Locked = False
Me!SV3.Enabled = False
Me!SV3.Locked = True
Me!SV4.Enabled = False
Me!SV4.Locked = True
End If
End If
Repeat again for SV3, but add the following code:
If Me!SV1 = 2 Then
Me!SV1 = 3
Me!SV1.Enabled = False
Me!SV1.Locked = True
Me!SV2.Enabled = False
Me!SV2.Locked = True
Me!SV3.Enabled = True
Me!SV3.Locked = False
Me!SV4.Enabled = True
Me!SV4.Locked = False
Else
If Me!SV1 = 3 Then
Me!SV1 = 2
Me!SV1.Enabled = False
Me!SV1.Locked = True
Me!SV2.Enabled = True
Me!SV2.Locked = False
Me!SV3.Enabled = True
Me!SV3.Locked = False
Me!SV4.Enabled = False
Me!SV4.Locked = True
End If
End If
Finally repeat for SV4, adding the following code:
If Me!SV1 = 3 Then
Me!SV1 = 4
Me!SV1.Enabled = False
Me!SV1.Locked = True
Me!SV2.Enabled = False
Me!SV2.Locked = True
Me!SV3.Enabled = False
Me!SV3.Locked = True
Me!SV4.Enabled = True
Me!SV4.Locked = False
Else
If Me!SV1 = 4 Then
Me!SV1 = 3
Me!SV1.Enabled = False
Me!SV1.Locked = True
Me!SV2.Enabled = False
Me!SV2.Locked = True
Me!SV3.Enabled = True
Me!SV3.Locked = False
Me!SV4.Enabled = True
Me!SV4.Locked = False
End If
End If
We have one final bit of code to add, this is to make sure you can click the appropriate square as the focus moves from record to record. This time we are going to add some code to the ‘On Current’ event for the form, this event is triggered when the focus of the record changes. To get to this event, right click in upper left hand corner of the form (where the two rulers meet) and select ‘properties’ from the list provided. Click on the ‘Event’ tab. Place your cursor next to ‘On Current’ and click on the ‘…’ button.
When the VBE window opens, add this code between the two lines of code already created:
Select Case Me!SV1
Case 0
Me!SV1.Enabled = True
Me!SV1.Locked = False
Me!SV2.Enabled = False
Me!SV2.Locked = True
Me!SV3.Enabled = False
Me!SV3.Locked = True
Me!SV4.Enabled = False
Me!SV4.Locked = True
Case 1
Me!SV1 = 1
Me!SV1.Enabled = True
Me!SV1.Locked = False
Me!SV2.Enabled = True
Me!SV2.Locked = False
Me!SV3.Enabled = False
Me!SV3.Locked = True
Me!SV4.Enabled = False
Me!SV4.Locked = True
Case 2
Me!SV1.Enabled = False
Me!SV1.Locked = True
Me!SV2.Enabled = True
Me!SV2.Locked = False
Me!SV3.Enabled = True
Me!SV3.Locked = False
Me!SV4.Enabled = False
Me!SV4.Locked = True
Case 3
Me!SV1.Enabled = False
Me!SV1.Locked = True
Me!SV2.Enabled = False
Me!SV2.Locked = True
Me!SV3.Enabled = True
Me!SV3.Locked = False
Me!SV4.Enabled = True
Me!SV4.Locked = False
Case 4
Me!SV1.Enabled = False
Me!SV1.Locked = True
Me!SV2.Enabled = False
Me!SV2.Locked = True
Me!SV3.Enabled = False
Me!SV3.Locked = True
Me!SV4.Enabled = True
Me!SV4.Locked = False
End Select
Close the VBE window
Hope you did not find this too complicated i.e. I hope I was clear. That ends the tip on Advanced Conditional Formatting.
Return from Advanced Conditional Formatting to Simply Access Home Page
or
Return to Microsoft Access Forms