Wednesday, February 29, 2012

A Timeclock Control for Microsoft Access 2007/10

I was involved in a truck weigh bridge entry system for a mining company in Australia. As part of that project, I developed a form used to enter times in an Access database (see below). This form ended up being used at a number of sites of a large Australian trucking company, to clock in the drivers and enter the times each job was begun and completed.    This article explains how to use it.

Sunday, February 26, 2012

Twitter @ smartaccess

For the last couple of years, I have posted every article that I have placed on into twitter. You can find me by searching for  twitter smartaccess.  Every article I post on twitter is one line of text and a link.  Maybe that will suit your lifestyle more than the wordier posts in my newsletter.

"Feel free to retweet my tweets"

Friday, February 24, 2012

Setting Up a Scatterplot in Access

Over the years a few people have shown in interest in the Scatterplots using Microsoft Graph.  Here is a simple page that shows one in action on an Access form..

Thursday, February 23, 2012

Jim Hungerford is heading to Peru

Jim Hungerford, the general manager of a company I worked for, left and went to manage a society that improves the lives of 100's of deaf kids. Now he is heading to Peru to climb a big mountain when he has rarely walked to the bus stop. Here is a challenge for you, the readers. If you sponsor Jim and his needy children, I will give away a free copy of the Gold Smart Access Collection 2004-2006.  All you have to do is make a donation and let me know by emailing me. You do not have to tell me how much you donated, 10$ will do.

Jim's trecking web site where you can make the donation is here.

Wednesday, February 22, 2012

Editorial - Out with the old and in with new - most of the time

The most important trait I like to see in my consultants is that they question what they are being asked to do. In many instances, we will tell the client that making those software changes is not in their best interest. Whilst we might kick ourselves later when we realise that we have lost some work, a week or two later the client thinks about it and comes back with a different approach or they mention that they like our honesty :-- vindication. The reason that I bring this up is because Peter Vogel wrote about this topic in one of his editorials back at the end of the last century. This is what he wrote..

Old Lamps for New

The title of this column comes from the story of Aladdin in "The Thousand Nights and One." At the point in the story where this phrase crops up, the evil wizard has come through town offering to trade brand new lamps for dirty old ones. Of course, what he's really after is Aladdin's magic lamp. The wizard does get Aladdin's lamp, which leads to much anguish and confusion and, of course, a more interesting story.

We often get to repeat this story in our business. The vendor comes into town offering to swap their brand-new shiny technology for our worn out, dirty, obsolete technology. And, of course, we do.

To switch metaphors in mid-stream, this is very different from the way a doctor works. A doctor will tell you, when prescribing medicine, what the possible complications are. Every medical practitioner realizes that each new cure comes with its own set of problems. The usual problems are unfortunate side effects, interactions with other medications, allergies, and a subset of patients who don't respond to the new therapy.

We seem to repeat the Aladdin story in our business. New technologies are presented as being free from problems. Each new software miracle is supposed to eliminate our backlog, increase our productivity, prevent bugs in our programs, clear up your acne, and get me a date on Saturday night. The reality is more like the doctor's story: Some things get better, other things get worse. More importantly, some things can't be done at all under the new regime. Lately, I've been thinking more about the side effects of new technology than I have in the past.

I remember installing a new Access system to replace an existing paper-based purchasing system. I had a lot of fun explaining all the benefits of the new system. One of the users, though, pointed out that they were giving up a significant ability by losing the paper-based system. One of the things that this group did frequently was pull out the file drawer and flip through the file folders, reviewing the orders due for the coming week. This was a simple thing to do and gave them a feeling for what was going to show up on the receiving dock in the next few days. I guess that, given the amount of time they'd had the paper system, I shouldn't be surprised that they used it very well.

No new technology ever supplies all of the features of the one it supplants. Users moving to Access from some other database are distressed to find that they can't add records to a table anywhere at all -- records must be added at the end of the table. These users had developed "legacy knowledge" around using their old toolset. A certain amount of training is required to "untrain" the users from their old expectations. After a while, they'll take it for granted that records are added at the end of a table.

Systems departments also have "legacy knowledge." If you have a systems department that's been using a particular toolset for a while, they'll be very good at using it. They'll understand all the error messages ("Oh, yeah, that message means that when you assigned the object variable in a previous line that you left out the Set command."). They'll know what does and doesn't work ("Well, the manual says you can do that -- but you can't."). And they'll know what you can do with the tool that isn't in the manual ("Let me show you a neat trick").

More importantly, no one in the department will be writing a totally new program. Instead, programs will be pulled together, at least in part, from bits and pieces of other programs. Sometimes those bits will be purely virtual ("Oh yeah, you write that routine like this") or just used as a starting place ("Take the order inventory program and turn it into"). While this is hardly code reuse, there's a lot of efficiency in that "legacy knowledge."

I think that abandoning our "legacy knowledge" is one of the major side effects of adopting a new technology. So far, for me, the benefits have outweighed the costs, so maybe it's just because I'm getting old (just got my first set of bifocals) that I'm thinking about this more. But, like good doctors, we need to consider the side effects and costs associated with any treatment.

by Peter Vogel - Smart Access April 1998

Some Sample Magazines and Send Me Your Search Words

I am as guilty as the next guy about putting together a not perfect website. Now when that website consists of 500 articles, improvements take a while. Over the last few months I have done a lot of things to remove the "daggy pages" on the website and I thank Adam Cogan for pushing me along in this endeavour. Here are two new innovations.

1) The page that describes the Smart Access collection that we sell has been simplified. There are 2 full sample editions of the magazine for you to review on this page

2) The Help and Manual search engine that I use on the site is a little slow but its pretty accurate. To assist you guys to find what you need I have setup a search interface using Google that features a number of key search terms. If you have key MS Access word/phrases that you think should be featured on this page, let me know using the email address

Tuesday, February 07, 2012

Drag and drop in Access

Many years ago, Microsoft wrote an article on how to drag and drop in Access 95, 97, 2000 and 2002. Then Doug Steele improved on that in an article for Smart Access in 2004. I have just tested the code in Access 2007 format and it works fine. If you like a bit of a coding challenge, the Access 2007 download and the samples are available here.

In the following diagram, you will see what happens when you select multiple entries from a List box and drag it to a Text box. Its pretty neat really. Well done Doug.

Figure1. Drag and drop from a list box to a text box

Thursday, February 02, 2012

Office 365

Here is a pretty good review from the crew at Office Watch on Office 365.  It will clue you in on how to test Office 365.