The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
December 2008

Building an Application from the Ground Up (All Because I Love Italian!)

I recently started a course in Italian, and I'd like to tell you why. First, I'm half Italian-my maternal grandmother and grandfather came over on the boat. Second I would love to be able to converse with my clients, friends and acquaintances in Italian. Third, Italian is the most beautiful language in the world (I know some of you might argue with this last point and might make a case for languages such as French or that language they speak in Britain, but for my money there is no more beautiful spoken word than the language of Italy).

One of things about learning a new language is that there is a very clear component of memorization. Among other things, you have to learn a whole new vocabulary and a whole new set of syntax rules. Unless you're in the happy circumstance of being immersed in a culture where the language is spoken, memory plays a critical role.

Back when I was a full-time student, I found that one of the most effective ways to memorize discrete facts was through the use of flash cards --those 3 x 5 cards with a question on one side and the answer on the other.

Being a software guy, I really wanted a program for my computer that would create electronic (or virtual) flashcards.

I looked around for a bit on the Internet to see if there was already an application that would meet my needs. I was especially looking for a Microsoft Access application since I've found that every time I buy a canned program there are features that I'm either not very happy with or which I think are really important that are missing from the program. Almost inevitably I end up writing my own software in Microsoft Access.

With this month's newsletter I intend to begin the process of showing you how to build an application from the ground up. I'll share with you my best practices and rationale for making various decisions along the way. I believe in this way you'll be able to see how an effective and efficient application is built.

In this Issue
  • Tip of the Month -- Give Your Forms a Caption
  • Infrastructure
  • Table Design - The Foundation
  • Conclusion & Next Steps
  • Trap of the Month - Importing Excel Zip Codes - Beware Of Leading Zeros

  • Infrastructure

    When I begin a new application I start with a template that has evolved based on past projects. This approach helps me make sure that I don't reinvent the wheel for every task I need to accomplish.

    With this template I'll get a standardized look for my forms and reports; the template will include a customized tool set (many of which have been described in prior versions of the Access Wizard) that will help me manage lists within the application. This template also contains generic code that will make my development more efficient. I won't be going into great detail about these tools in this series of articles, however know that what I'm presenting represents the core elements of building an effective application.

    The other important factor is the name for the application. As a working name I'm going to use FashCards.mdb. The version that I'll build this in is Microsoft Access 2000. Although this is neither the most recent nor most powerful version of the Access, I've chosen 2000 because I may wish to share this with other people. Access 2000 tends to be a version that runs with most Access installations today. An application developed in Access 2000 will run on newer versions, but if I develop the application in a newer version it is not likely to run on previous versions.

    Table Design - The Foundation

    If you're a regular Access Wizard reader, you know that I'm an absolute fanatic for designing the tables correctly. This comes before building forms or reports and before writing code. I believe that getting the tables right is as critical to a database application as building a solid foundation is in the construction of a new home.

    So before we begin to design the tables we have to give some thought to what we want our application to do.

    First we want to be able to store words in both English and Italian. As it turns out, my wife is going to Chile in January. She is in the process of learning Spanish, so one of the things I'd like to do with this application is to use it for more than one language, or even go beyond languages to things like math facts.

    Another goal is to have stacks of words and then to test that stack as a group.

    Third, I'd like to incorporate functionality so that as a set of words is tested, I won't see words repeated if I've translated them correctly; however, if I've gotten the word wrong, I'd like that particular word to come up again in the stack and keep coming up until I get it correct (after all, repetition is important in language learning!)

    Fourth, when I finally master particular words, I'd like a mechanism that would allow me to indicate that I've learned this word and have no need to see it again.

    Lastly, I'd like to be able to test the translations in both directions: from Italian to English and English to Italian.

    Conclusion & Next Steps

    To summarize, I've determined the major functionality for the program, given it a name, and selected the Access version that I'll use for development. The next installment of this series will be to design our tables and to begin testing them.

    Trap of the Month - Importing Excel Zip Codes - Beware Of Leading Zeros

    If your work involves importing addresses from Excel, you may find that when you import zip codes from Excel into Access you get numbers rather than text.

    The reason this happens is that Access will look at the first few lines of your imported data and make a decision as to what that data is. If it looks like a number and has no special formatting, Access will assume your data are numbers.

    In fact, zip codes are really not numbers, they're digits. The leading zero for zip codes in New England is indeed important (a zip code must have 5 digits, as we know), so Access should treat these digits as text rather than numbers. One easy way to fix this problem is make a small change in Excel before you import the data. Simply highlight the column containing ZIP codes, then choose Format | Cells | Text. When you import the ZIP codes into Access with the data formatted as text, your leading zeros will be preserved.

    Tip of the Month -- Give Your Forms a Caption

    When you create a new form, the caption that shows up in the blue banner at the top of the form, by default, is the form name you give it when you create the form.

    If you are using a naming convention, such as starting the form name with "frm" and using mixed case, then it's unlikely that the form name will be something that is user-friendly.

    This is something that you may easily fix. In the design view of the form, select the form itself (you can do this by clicking on the gray box in the upper left-hand corner of the form), open the properties box (View, Properties), select the format tab, and type a revised title for the form in the caption area.

    Make it a habit to give your forms meaningful titles; your users will appreciate it!

    Quick Links...

    If you can't see all the Pictures Click Here for Online Version

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    Safe Unsubscribe
    This email was sent to jim@custom-software.biz by jim@custom-software.biz.

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