Conditional format a Microsoft Access form

Changing the background colour of a form with a subform

I recently had a client who wanted to know when their data met certain criteria.

In their case, the situation was that they buy services (such as couriers, graphic design work and so on) for their clients and then as they get billed they charge this on to the client, with a mark-up, and they term this a “recharge”. The database was able to track the total amount being billed to my client, and the total amount being recharged on to their customer, and I’d used a simple conditional format to highlight the recharge if it came to less than the amount billed.

But my client said “That’s great – but I can see people missing the recharge amount going red… Can we find a way of highlighting the entire form in yellow or something if we’ve recharged less than we were billed?”

And of course, the list of what can have a conditional format applied is quite limited, and doesn’t include the form or detail section backgrounds. So, a different way was needed… Initially, I wanted to see if this could be done without using any code…. and it was almost possible, but not quite (at least not while having acceptable behaviour).

So, below I outline the two approaches I came up with. For the purposes of this demonstration, I am making use of a cut-down version of Northwind, and as the scenario, I want to highlight the background of the form for any order where the order was shipped after the required date. This can be seen in action in the first screenshot below, where order number 10643 was shipped almost a month before the required date, but order number 10280 was shipped the day after the required date.

Option 1 – using conditional formats

What? I thought we just said that this didn’t work?!

No, we said that we couldn’t apply a conditional format to the detail section or the form as a whole – so we need another object to which we can apply the format. Unfortunately, the list of objects which can accept conditional formatting is quite small – namely the textbox and combobox controls.

So, the first step was to add a textbox to a the form, which would take up the entire detail section, and send it to the back; the edge of the textbox is indicated by the arrow in this screenshot:
Conditionally formatting a form’s background based on the date fields. The textbox used for the format is shown by the arrow.

The properties set for the textbox are as follows:

Name: txtFormat
Height / Width To fill the detail section
Border style Transparent
Backcolor White (FFFFFF)

Don’t forget to send this textbox to the back, or it will obscure all the other controls in front of it!

The next step is to add the conditional format to this textbox. To do so, select the textbox and then from the design tab select the Conditional button. (In Access 2003 and earlier, conditional formatting can be found in the Format menu). In our case, we want to know about it when the shipped date is on or after the required date, so the criteria for highlighting is not directly relevant to the content of the textbox itself, meaning that instead of selecting “Field Value Is” for our condition, we must select “Expression is” and complete the dialog box as follows:
The Conditional Formatting dialogue box. Because the format to be used depends on the values of other fields, the first parameter must be set to ‘Expression Is’

Once this is complete, trying out the form in form view reveals that it more or less works. We get a background which changes colour as needed (navigating to record number 32 should result in a yellow background – it’s the record with order ID 10280 as in the first screenshot above. However, there are still a number of problems.

Firstly, it may be (depending on the order in which you’ve added the controls to the form) that txtFormat receives the focus immediately the form loads, which means that you won’t be able to see anything else, and that if it is being coloured due to the conditional formatting rule, those colours will be inverted.

We can resolve this by setting the Tab stop property (on the “Other” tab of the properties window) to No, meaning that the textbox cannot receive focus when the user hits the tab key.

But that doesn’t solve our second problem – if you click in the background, you are in fact clicking on the textbox, and so will give it focus, leading to the problems above. We cannot resolve this by simply setting the control’s Locked property to True (this doesn’t stop you clicking in it) or by setting Enabled to False (this does, but also removes all the colour, thus defeating the object of the exercise).

In fact, this is where the one necessary bit of code comes in. We need to respond to the txtFormat control getting focus by immediately sending the focus elsewhere – ideally to the first “real” textbox on the form, which in my case is called txtOrderID.

In order to do this, add the following code to the form’s module – as you can see, we’re responding to the txtFormat_GotFocus event.

Private Sub txtFormat_GotFocus()

   Me.txtOrderID.SetFocus

End Sub

This code should be pretty self-explanatory – we’re responding to the txtFormat control getting focus by simply sending that focus to the txtOrderID textbox instead. The use of the “Me” keyword is not required, but I find that it helps to reduce errors, as the editor’s “Auto list members” feature will respond to Me. by listing all the controls on the form.

Once this code is in place, the form will do what we want – it highlights the entire background of the form (or at least the detail section, which is what we wanted) when the data on the form meets our criteria.

But I’m not entirely convinced by this solution. Firstly, it strikes me that we’re storing up problems for later. What if at some point we choose to expand the size of the form to include other fields, other controls or whatever? We’ll need to remember to increase the size of txtFormat to match, or the result will be a bit ugly to say the least.

Secondly, what if we want to include controls which can’t have their background set to transparent to allow the yellow to show through? For example – what if we wanted a subform, showing (in this case) the details of each order? The result doesn’t look good:
Conditional formatting using a text box does not work particularly well when a subform is added.

As you can see – the background of the subform has not been changed, and thus results in a big white box in the middle of the form (in this instance the subform has been set to show its records using the datasheet view, meaning that it doesn’t really have a “background” any more, but the same issue would hold true if it was set to single or continuous forms)

Finally, it just doesn’t “feel right” to me to be adding a text box only to use for formatting. It’s a bit like using a sledgehammer to crack a nut – it’s not that it’s overkill, but if you use the wrong tool, you’re likely to end up with a hole in your desk at some point….

The alternative, then, to all these issues is to take the “code only” approach.

Option 2 – Using VBA

When using code to achieve our objective, we have essentially four things to consider:

  1. Which is the best event to respond to in order to trigger our code?
  2. What property or properties should we set in response to this event?
  3. What should we set these properties to?
  4. What conditionality must be incorporated into our code?

We need to colour – or not – our form on a record by record basis. The event that gets triggered each time a new record is loaded is the current event of the form, so it’s in this event procedure that we need to write our code. There are no parameters for this procedure, so the event procedure looks like this:

Sub Form_Current()

End Sub

Our code needs to set the background colour of the form – or rather its main “Detail” section to something noticeable. If we have a subform, it also needs to take care of changing that to match.

The objects can be accessed programmatically like this:

The background of the detail section of this form Me.Sections(“Detail”)
The background of the detail section of the subform Me.ChildForm.Form.Sections(“Detail”)
The rows in the datasheet of the subform (because our subform is set to Datasheet view) Me.ChildForm.Form.DatasheetBackColor
The alternate rows in the datasheet of the subform (not needed in Access 2003 as “striping” is not supported) Me.ChildForm.Form.DatasheetAlternateBackColor

As to what we should set these colours to – well, that’s largely down to personal preference. A balance needs to be struck between making the colours so vivid as to make users feel violently ill, while making them strong enough that those who are colour-blind or not sensitive to colour are able to see the difference. I tend to use the RGB() function to specify colours in amounts of Red, Green and Blue, largely because it provides more choice than the colour constants such as vbYellow, and because I find it relatively easy to specify and tweak colours this way. In this instance, I’ve gone for a slightly more subtle shade of yellow than is provided for by vbYellow – RGB(255,255,100)

Sub Form_Current()

Me.Section(“Detail”).BackColor = RGB(255, 255, 100)

Me.ChildForm.Form.DatasheetBackColor = RGB(255, 255, 100)

Me.ChildForm.Form.DatasheetAlternateBackColor = RGB(255, 255, 100)

End Sub

So, we’ve found the correct event, we’ve discussed what properties of which objects should be set, and we’ve looked at the values to which we might set these properties.

Last but definitely not least, we must consider the conditions under which these values should be set. We only want to highlight the form if the ShippedDate is on or after the RequiredDate, and in fact, if this isn’t the case, we’ll need to set the various properties back to their original values.

This means wrapping the above code into an IF… THEN… ELSE…. END IF block to allow us to switch the colours back and forth as needed.

The final code looks like this:
The final code which sets the form and the datasheet of the subform to yellow if the shipped date was on or after the required date, and back to white if not.

So that’s it! Our form should now work according to our needs.
The final form, showing the formatting of both the main form’s detail section and the datasheet of the subform to match

Not only does this second method get around the need to include a text box where none is needed, but it negates the need to resize an object to format the background effectively, and avoids the issue of an undesired control receiving focus. All in all, I think this is a much more elegant solution.

As always, feel free to add your thoughts below if you have a better / alternative way of achieving our goal!

And if you’d like to get more out of Microsoft Access, why not let us show you how on a customised Access training course?

By | 2017-02-23T12:01:31+00:00 February 10th, 2012|Development, Microsoft Access, Microsoft Access tips, Uncategorized|0 Comments

About the Author:

Andrew Richards is the Managing Director of TheIT Service. His background in training goes back to 2000, and he was involved in IT networking and support beyond that. Now he spends his time living and breathing the GDPR (fun!) and building databases.
When not doing these things, he can be found attempting to train his various sheep and chickens.

Leave A Comment