Changing the background colour of a form with a subform
- Microsoft Access Conditional Formatting Examples Excel
- Conditional Formatting In Access Table
- Access Conditional Formatting Examples
- Access Report Conditional Formatting
I recently had a client who wanted to know when their data met certain criteria.
Best mix feed formulation software. 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.
This section contains examples of expressions that you can use to create a calculated field in a query or to supply criteria to a query. A calculated field is a column in a query that results from an expression. For example, you can calculate a value, combine text values such as first and last names, or format a portion of a date. For example, you could select 'Field Value Is,' 'less than,' enter '0,' and then select red formatting to format any value less than 0 with red text. Select the formats that need to be applied to values that meet the conditions and click OK. Access applies the conditional formatting that you specified. Watch this video to learn how to Highlight data on forms by using conditional formatting. Watch this video to learn how to Use conditional formatting on reports. Open the form or report in Layout view, and select the control where you want to apply the conditional formatting. For information on creating forms see, creating an Access form.
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:
|Height / Width||To fill the detail section|
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.
Microsoft Access Conditional Formatting Examples Excel
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()
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:
Conditional Formatting In Access Table
- Which is the best event to respond to in order to trigger our code?
- What property or properties should we set in response to this event?
- What should we set these properties to?
- 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:
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.
Access Conditional Formatting Examples
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)
Me.Section(“Detail”).BackColor = RGB(255, 255, 100)
Me.ChildForm.Form.DatasheetBackColor = RGB(255, 255, 100)
Me.ChildForm.Form.DatasheetAlternateBackColor = RGB(255, 255, 100)
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!
Access Report Conditional Formatting
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?