The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
August 2011

Information: Better to Have it than Not

We have pet cockatiel named Rosie. I'm not sure if Rosie realizes she is really a bird. When she hears other birds outside, she becomes very alert and her crest stands way up. Occasionally we get wild turkeys in our backyard. When Rosie sees one of those, she freaks out. Perhaps she thinks they are dinosaurs.

Every once in a while, Rosie will begin to tweet insistently, and will continue to tweet until one of us shows up to find a what's going on. It's like one of those old Lassie shows where Lassie comes to the farmhouse door and barks and barks and barks and somehow mysteriously communicates that Timmy has fallen down the well and needs to be saved. Rosie's tweets are also meant to communicate, but perhaps in a much more mundane fashion.

When Rosie is into one of her come now tweet, tweet, tweet routines, it usually means something is going on. But unlike the emergency of saving Timmy, it is more on the order of one of us is getting home and Rosie wants to have all of us to get just as revved up with excitement and anticipation as she is.

Frivolity aside, the important thing is that she is sharing information, something that might not be as earth shattering as she thinks it is, but information nonetheless.

So it is better to have information than not. In this month's Wizard we will look at how to make sure that important information will appear automatically, both upon opening an Access application as well as when important information in the database changes.

In this Issue
  • Tip of the Month - Have More Than One Instance of Access Running
  • The Infrastructure
  • Making the Label Dynamic
  • What Makes the Information Dynamic
  • The Beauty of Information
  • An Aside about the Picture

  • The Infrastructure

    Below is a picture of the opening menu of one of my Applications called BTS.

    In addition to the standard menu selections on this form, you can see that there are two items in yellow. The first one speaks to issues that need attention, while the second is a reminder of the immediate data set the user has open. Both of these items are nothing more than labels that update as the data in application changes. They make this shift via code that is called as various events happen in the application.

    When we go behind the scenes this is what appears:

    You can see that, in the design mode, it says 272 pending transactions and nothing about KK review. How does this happen.

    Making the Label Dynamic

    The on open event of the form has a line of code in it called SetMenuLabels. This subroutine, which acts to makeour labels dynamic, is shown below.

    Sub SetMenuLabels()
        Dim frm As Form
        If CurrentProject.AllForms("fmnu").IsLoaded = False Then Exit Sub
        Set frm = Forms!fmnu
        With frm
            .lblVersion.Caption = GetVersion
            .lblRegion.Caption = "Region " & Region
        End With
        Set frm = Nothing
    End Sub

    The first line simply sets up a variable frm as a form.
        Dim frm As Form

    Then next line

        If CurrentProject.AllForms("fmnu").IsLoaded = False Then Exit Sub

    tests to see whether or not the menu form fmnu is loaded. If it isn't in memory, then the routine exits. The logic here is that if the form is not open, then there is nothing for this routine to do.

    If the form is open, then the form variable is set to the name the form:
        Set frm = Forms!fmnu

    The next four lines is where all the action takes place:
        With frm
            .lblVersion.Caption = GetVersion
            .lblRegion.Caption = "Region " & Region
        End With

    You can see that the caption property of the two labels call functions that return a string. This string is what shows up on the form.

    What Makes the Information Dynamic

    I mentioned earlier that this particular routine is called from the form's on open event. Because the construction of subroutine, SetMenuLabels is independent of the form. It can be called from any place within the BTS application. This provides the characteristic of making the information on the menu form dynamic. Embedded in critical places in the application are calls to this routine. When something changes that would affect the information on this opening menu, the subroutine is called and the information on the form updates.

    As the user is doing his or her work within BTS, the program guides them back to the menu, where this information is always displayed and updated as appropriate.

    The Beauty of Information

    The nice thing about this type of approach is that the user is alerted to changes in the data as he or she works. There is no need for them to go out of their way to see what is going on. Like Rosie's insistent chirp, the user is free to ignore the information or can react to it as needed.

    An Aside about the Picture

    The BTS application is actually the successor to another application I built, called Budgie. At the time Budgie was built, the norm within the group was to name applications after animals. Since the new application was designed as a budgeting tool, the project manager chose the name Budgie, a fun cutesy name.

    As it turned out, Budgie grew from a simple and straightforward database into a sophisticated and important application. The Directors of the organization would demonstrate the software as part of helping other organizations learn how to solve various budgeting issues. I learned that it became somewhat embarrassing when they had to tell fairly high level folks that the name of the application was Budgie.

    In its new incarnation, the name was changed from Budgie to BTS (Budget Tracking System). The original picture on Budgie was actually a picture of a budgie. As the program morphed into something more sophisticated with a more serious name, the project manager was still willing to have some fun and allow a budgie-like picture, which happened to be a cocktail, a bird that my family and I like a lot.

    Tip of the Month - Have More Than One Instance of Access Running

    If you work in Word or Excel you may be used to having multiple instances of either of those programs running simultaneously. In Access, if you try to open one Access application while you have another in memory, you may find that the original database is replaced by the new one.

    If you need to have more than one database open simultaneous, don't open the second application by clicking on the file open menu or from Windows Explorer. Instead click on your Access icon. This will open up a new instance of Microsoft Access and allow you to have two (or more) databases running simultaneously.

    Be careful though, Access tends to chew up a lot of resources. If you have too many open databases, you may find that your computer slows to a crawl.

    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