Saturday, January 25, 2014

MISSING: The lowdown on VBA Project references

Doug Steele explains why Access applications suddenly stop working, or work on some machines but not others. What this means in a technical sense is: VBA Project references. Read about it here and find the download for the article on References here.  Doug includes quite a few links to other websites on the page as well. has been fully categorised

In these days where search is everything, people tend to forget that categorising is also important for people who don't know exactly what they are searching for. So over the hot summer break (apologies to those suffering severe cold at the moment), I went through and categorised many of the articles that were stored in chronological order.   Here is a link to the Queries category so you can read about queries without joins, iif group by queries and other important topics that you will never find in the Access help.

Thursday, January 23, 2014

The Quality of Your Database Work

In 2005, Peter Vogel wrote in an editorial

"What about all the criteria that we, as developers, use to rate our work? Clean code, good object design, fully normalized database designs, timely delivery, and so on. These are all admirable goals and would be very useful if your work was being judged by other programmers. However, all of these criteria are really just a means to an end and not an end in themselves. That end is your ability to make enhancements to your application and to find and fix bugs in it (should there be any). Those goals are only relevant if your users have enough faith in the quality of your product to have you back again. 

Read the editorial on quality here or all of Peters great editorials here

Infrared Keyboard

This infrared Bluetooth virtual keyboard might be a great after dinner conversation piece because it will work on the dining table.  Not hugely expensive and should hook up to smart phones and pc's and maybe even a TV. Don't forget the usual bugbear ~ "charging" .

Apply The Same Condtional Format Equations Across Multiple Fields

In this short article, I demonstrate how the Access 2010 Ribbon allows you to apply the same conditional Formats across multiple fields.

Thursday, January 16, 2014

My Favourite Conditional Format + Listing Conditional Formats

My favourite conditional format is one we use in our invoicing system that highlights the old invoices that haven't been paid. This shows invoices that are a month overdue with a purple box, invoices that are 6 weeks old with a red box and invoices that are 6 weeks old where we have had a follow up conversation with a yellow box.  See this in figure 1. In figure 2, I illustrate the conditional formatting expressions for the coloured box.

Figure 1 - Highlight mature invoices with Conditional formatting

Figure 2 -Conditional formatting expressions for the coloured box

Multiply conditional formatting 10 times and what do you have ?

As you can see in the figure 2, conditional formatting can get out of hand really quickly and you soon will wish that you could document and program conditional formatted fields in VBA. We found out this week that we couldn't find an elusive piece of code to document the actual expression in the conditional format equation. Finally after much digging, I found the code in the sample database that Rick Dobson submitted for his 2005 Smart Access article on Conditional Formatting.  I have now published that elusive code in this new article on how to list conditional format expressions

and to get into this, you will need to download the Access 2007 sample database here

Tip: To make a colourful highlight box as in figure 1, put the forms index field in second text field and lock it. The conditional format will need to show Red text on a Red background for the Red condition. You can work out the rest.

Monday, January 13, 2014

A Huge 860 Megs for $ 3 grand

I found this old Aussie magazine from 1995 which actually looks a lot like the latest Dick Smith material that gets dropped in the mail box.  In those days I would dial up my special MSN email account that only worked in Microsoft's special sheltered internet. Two years earlier than this I purchased 16 megabytes of memory for 1500$.