Search The Web

Today's Headlines

Tuesday, November 2, 2010

Microsoft Access Tips & Tricks: Conditional Formatting

I wrote recently about highlighting form controls in Microsoft Access to make data entry easier. Right after I wrote that post, I realized that there is actually an easier way to do some of this without any coding at all. It is done by using conditional formatting.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields, finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, and highlighting form controls.

Using conditional formatting works for both forms and reports. It does not work in the grid view of tables or queries in Access. And it comes with some limitations, so it is not as versatile as using VBA. But if you are willing to live within its limitations, it is much quicker and more convenient.

So, what are the limitations of conditional formatting? Conditional formatting in Access works the same as in Excel, so you can do some formatting based on the value in a report or form control. In addition you can change the format of a control on a form when it has the focus. You can not use conditional formatting based on any other event on a form. And as for changes to what is in the control, you can bold it, italicize it or underline it, change its color and change the background color. You can not do any other changes to a control using conditional formatting (such as changing the font, or size). Most importantly, you can not do conditional formatting in Access 97. If you support a legacy application that still uses Access 97, then you have to use code as shown here to achieve the highlighting rather than using conditional formatting.

Let us assume that you can live within these limitations, particularly the kinds of changes you can do to a control with conditional formatting. How do you actually do it to ease data entry into a form? The steps below will show you how to do it. The screenshots are from Access 2003. In Access 2007 and 2010, you have to know your way around the ribbon to do these steps as shown below.

First, create the control on the form.
Create A Control On A Form
Next, choose the control (or controls) that you want to apply conditional formatting on, and select Format->Conditional Formatting from the menu bar. You can select multiple controls to apply the same format on by clicking them while holding down the Shift key, or you can use your mouse to select a bunch of them by drawing a rectangle around them.
Choose Conditional Formatting from Format menu
The dialog you get is very similar to the dialog you get when you select Conditional Formatting in Microsoft Excel. Set the default format first (the format when the control does not have the focus). Then, choose "Field Has Focus" from the dropdown menu (this is the only event in the dropdown menu, that is why I mentioned that conditional formatting can be used only to replace format changes based on getting and losing focus, not other events like mouse-click, double-click, update, change, etc.).

And I know, it says "Field" even though in the context of forms and reports, it should be "Control". I have no idea why they would use confusing terminology this way, but that is the way it is, you just have to get used to it, even though it grates on my nerves every time I see it!
Choose Field Has Focus in conditional formatting dialog
Then, set the highlight format. Once again, as mentioned earlier, you can change the background and foreground colors, bolding, underlining and/or italicizing text in the control. No other effects (such as changing the font or font size, changing the background image, changing the borders, etc., is possible with conditional formatting).
Apply conditional format of your choice
That is it. You are all done! As I said, there is no coding involved. It can be much quicker and more convenient to use conditional formatting than coding, but you have to remember the limitations and be willing to live within them.

Moreover, because there is no coding involved, it can become difficult to figure out where the format change is coming from. I was looking at an old Access application I wrote a while back, and the controls in its forms were changing color when I put the focus on them. I wanted to add another control to the form, and I could not figure out how to highlight that control when it got the focus. I spent an hour looking at the code behind the forms to try to figure out how I had done it for the other controls before I suddenly remembered that I had used conditional formatting!

Hope this post has been helpful in solving any problems you might have had with using conditional formatting for highlighting form controls in Access. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!

No comments:

Visitors Country Map

Free counters!

Content From

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!



Spelling Bee
difficulty level:
score: -
please wait...
spell the word:

Search The Web