The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
June 2010

How to Begin Exactly the Same Way Every Single Time

Tennis is the greatest sport in the world. There are some who might say that another sport such as basketball, football, baseball, or tiddlywinks is the greatest sport -- they would be wrong.

What makes tennis such a great game? It's pure one-on-one competition. Luck is minimized. If your skills, your execution, and your head space are better than those of your opponent, you will win. This is not true of other sports; especially team sports, where issues such as leadership and teamwork become critical. Part of the beauty of tennis is its purity.

If you're interested in a great book about this great sport pick up Levels of the Game, by John McPhee. It describes a match between two top American players of the 1960s, Arthur Ashe and Clark Graebner. The book delves into not only the mechanics each player brings to the game, but also the inner workings of the players' minds as well

Each point in tennis begins with a serve. Arthur Ashe contends that, in order to serve properly, your toss should be executed as if you're machine - the same motion every single time. How does one makes something like that happen? For Arthur Ashe, the answer is practice; thousands and thousands of practice tosses to get just the right placement of the ball so that the racket strikes in the sweet spot every single serve.

How do you make something happen in the same way every single time you start Microsoft Access? You could try doing the same thing manually every time but, because a human being is involved in the process, it won't be exactly the same every single time. One way to guarantee consistency from startup to startup is to take it out of your hands and put it into the hands of Access.

This month, we will talk about how to automate the startup process in Access. Like Arthur Ashe and his consistent serve, we'll configure Access to have a perfect start to suit your needs.

In this Issue
  • Tip of the Month - Prevent Opening Code from Firing
  • The Infrastructure
  • Details
  • Trap of the Month - Beware of Access Built-in Security

  • The Infrastructure

    Access provides for the selection of a form to come up when you begin. If you select tools startup from the menu, you will see the following screen:

    Note that the screen might be slightly different in various versions of Access. The options here allow you to make a number of selections. You can set the application title, what will show up in the blue banner at the top of your screen; an icon, essentially a picture that will show up at the bottom of your screen in your taskbar; and other things, such as menu bars, shortcuts, and the like.

    Our focus today will be on the display Form/Page option. By clicking on the drop down, you'll be presented with a list of forms in your database. I have a default form, which I always use for all my applications. My startup form (frmStartup) has only one purpose in life: To kickoff the startup code, which in turn will set the user environment.

    In that form, I have a routine that fires on the on-open event. You can get to that event by opening the form in design mode, choosing the form itself, right-clicking, then choosing properties.

    At that point, your screen will look something like the following:

    Notice a couple things here. First, I have the form selected as can be seen by the small black square in the upper left corner of the form. Second, I've clicked on the Events tab, and then selected the On Open event. From there, you will see an ellipsis. If you click on the ellipsis, you will have several options; choose Code Builder.

    This will bring you to a code window where you create VBA code that will fire every time the startup form is opened. Since you have instructed Access that the startup form is the one you are working with, effectively this means every time that Access opens the application, it will fire your startup code.


    My on open event code in the form is very simple - it calls another routine named "startup".

    This startup subroutine is part of my standard modules where I put the details of the startup process. Depending upon the needs of the application, the process will range from the simple to complex.

    I always have the startup procedure to do two things: Close down the startup form (it has done its work) and open up a menu. From there, my user is able to read the screen and begin to work in whatever way he or she needs to.

    My startup form, by default, is not visible; it is one of the properties of the form that you will find under the format tab. My goal here is to not have too much activity on the screen while the application is starting up.

    If you're not comfortable with code, you can also select a macro for your startup process. I'm not a big fan of macros, but it's an option.

    With a startup form and startup code in place, you will be on your way to consistency in opening your application. Now your startup process will be like Arthur Ashe's serve, mechanical and flawless.

    Trap of the Month - Beware of Access Built-in Security

    Access provides you with the ability to invoke security through mechanism called workgroups. This is a dangerous approach. The use of workgroups for security results in the creation of a special file that needs to be backed up and ported if you're changing systems or locations.

    I've run across very unhappy people who used this approach and got locked out of their own database. I've even seen developers use this approach and then regretted it because they too became locked out.

    If you do choose the built in Access security, read the documentation thoroughly and have a plan in place to recover from a potential lockout.

    Tip of the Month - Prevent Opening Code from Firing

    Sometimes an application's startup opening code is quite complex and locks down an application so that you or your users are prevented from selected actions. For instance, you might be putting security in place to allow only certain views of the application or you might be hiding the database window. You can even prevent certain series of keystrokes. These are all reasonable things to do to control what happens in the application and to help resist user error.

    Sometimes though, you don't want these initial startup procedures to take place. To skip them, the trick is quite simple: Just press and hold the shift key during startup. This will signal Access to skip all startup processes and open the application in the default database screen. This will allow you to avoid all developer coded security and unusual settings that you have placed in your file.

    Since this is potentially an open door to security weaknesses, you may want to turn this option off. You can do this by deselecting the Allow Access Special Keys in the startup tool box. Be aware though that if you take this approach, you should have a plan in mind so that you, as the developer, will still be able to get in if needed.

    In my applications, I allow a path that consists of an obscure sequence of events that opens the door for me so that I have the ability to get into the file when needed.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886