$Account.OrganizationName
The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
June 2011

"Are You Done Spitting at Me Too?" - Getting Users to Cooperate the Easy Way

In 1974, the comedy team Cheech and Chong put out a record called Earache My Eye. It begins with a soundtrack of a teenager listening some to some loud rock music. His father storms into his room and says "Turn that thing down!" followed by a tirade of complaints about the son's behavior and performance in school. In exasperation, he says to his son.

"I've talked to you and talked you and talked to you! Now I'm done talking to you!"

The son says "Good does that mean you're done spitting at me too?!"

From here, the father and son devolve into a battle at the end of which the father beats his son.

If you develop database applications for other people, you realize that, as much as you would like them to cooperate with your intent, at the end of the day, users will do what they want to do. I've seen developers take the tack of complaining about their users. This has about as much productivity as the father yelling at his son in the Cheech and Chong record cut.

If you know much about people, you know that it's easier to get cooperation by setting an environment that guides them toward productivity rather than yelling or complaining at them. In a database, user cooperation can be a critical aspect of getting good data into a database and keeping the bad data out. Although it feels good to tell users that they should put data in a certain way, you will find that they will do what they're going to do, rather than what you want them to do. Life is better for everyone if you accept that fact, and have it work in your favor.

This month, we take a look at a method of handling what, for users, can be a difficult task and make it easy for them by predicting a mistake that they are likely to make. And, rather than allowing bad data to enter the database or complaining that the users just don't cooperate, we set environment so that, if they do something that is less than ideal, we help them recover from it seamlessly.

In this Issue
  • Tip of the Month - Automate Code Selection when Creating Events
  • A Common Data Entry Issue: Entering Percents
  • A Better Approach: Handle Input with an After Update Event
  • The Code to Make it Happen
  • An Alternate Approach and Conclusion
  • Trap of the Month - Controlling Data Entry Should Consider All Reasonable Options

  • A Common Data Entry Issue: Entering Percents

    Frequently in a database the user will have to put in a percent. We all know that a percent is a proportion, an expression of a part of a whole. Mathematically it is typically between 0 and 1. The issue then is trying to predict whether the user is going to put in a decimal point, or a whole number.

    For instance, let's say that a manager was recording the monthly sales as a percentage of the annual budgeted sales. She might put in 15%, she might put in .15 or she might put in 15. The first two cases work just fine, but the final case 15 means 1,500%

    I've seen instances where developers handle this situation by not allowing a user to put in 15, warning them that acceptable values are between zero and one. Although this is effective, it falls into the category of what I call "annoying the user" - not a good practice. I have also seen developers try to handle the situation by clearly formatting the cell so that the data entry issues are visible. For instance, if the user were to put 15 into a percent field the resulting value would be 1,500%.

    If the user is paying attention, she'll catch it. However, somebody doing heads-down data entry might not notice and, as a result, bad data will creep into the database.

    Wouldn't it be better to help the user recover gracefully from a data entry error rather than annoying them or letting the error just slide by? Why not alert them that might be a problem, clarify what's expected, and help them adjust it so that they enter what they intend? By taking this approach, you will have a much friendlier database application.


    A Better Approach: Handle Input with an After Update Event

    Each time a user types in a text box, Access is aware that the user has made an entry and a number of events fire in the code base. Most of these typically have no noticeable effect; however you can take advantage of these events. The one that we will use to solve our problem is the After Update Event. By adding just a little bit of code, we can automatically adjust an entry to a reasonable number, regardless of whether the user has entered .15, 15%, or 15.

    For instance, let's say we have a text box called txtPercentOfSales that we want to make error resistant with some code that fires after the user makes an entry. Take the following steps:

    1. Open the form in design view.
    2. Right-click on the textbox.
    3. You will see a pop-up box. Click properties.
    4. You will get another pop-up box with a series of tabs across the top. Click on the events tab.
    5. Click on the After Update Event.
    6. You'll see a builder selection box, choose Code Builder.


    The Code to Make it Happen

    This will bounce you to the code window with a skeleton code of

    Private Sub txtPercentOfSales_AfterUpdate()

    End Sub

    Fill in the code so that you have the following

    Private Sub txtPercentOfSales_AfterUpdate()
      Dim varRate As Variant
      varRate = Nz(Me.txtPercentOfSales, 0)
      If IsNumeric(varRate) = False Then
        MsgBox "Please enter Percent as a number"
        Me.txtPercentOfSales = Null
        Exit Sub
      End If

      If varRate = 0 Then Exit Sub
      If varRate > 1 Then
        varRate = varRate / 100
        Beep
        Me.txtPercentOfSales = varRate
      End If
    End Sub

    Let's review what happens with our code.

    The first thing we do is establish a variable varRate, to hold the contents of the entry in the text box. We set varRate as a variant variable, which means essentially we're not sure exactly what we're going to get so we're prepared for anything.

    Why not set it as a number rather than as a variant? In 99% of the cases the user will put in a number, but if he doesn't you want the code to handle it gracefully. Rather than throw an ugly error message which will confuse the user, we are prepared for a non-numeric entry and can react appropriately.

    The next line

      varRate = Nz(Me.txtPercentOfSales, 0)

    takes the contents of what the user has typed in and puts it into our variable. If the user has wiped everything out, we default the entry to 0.

    The next section handles the case where the user has entered text rather than a number.

      If IsNumeric(varRate) = False Then
        MsgBox "Please enter Percent as a number"
        Me.txtPercentOfSales = Null
        Exit Sub
      End If

    Here we use the function IsNumeric, which tests whether the user has entered a number. If not, we give the user information, set the entry to nothing, and exit from the routine.

    The final section is where the real action takes place:

      If varRate = 0 Then Exit Sub
      If varRate > 1 Then
        varRate = varRate / 100
        Beep
        Me.txtPercentOfSales = varRate
      End If

    This bit of codes says that, if we have a zero, we accept it and exit the routine. From here, if the number entered is greater than one, we take the number entered and divided by 100. So if a user had entered 15 we would turn that into .15. If this is the case, we beep at the user to let them know something has happened and then set the text box to our revised value.

    If the user has entered a number that is less than or equal to one, then we accept that as a valid entry.


    An Alternate Approach and Conclusion

    Another way to handle this problem is to format the text box to show percents and then adjust the text box property to make sure that the entry is between selected values. I tend to avoid this approach because it puts too many handcuffs on the user. For instance, if you format it as percent, this removes the opportunity for the user to enter a percent without hitting the decimal point. Certainly one goal of any software should be to make the user's life as simple as possible. If they want to skip the decimal point, they should be able to skip the decimal point.

    Using the method shown here to ensure valid data entry makes life easier for both the person putting the data in as well as the person that will use and analyze the data. If the person doing the data entry prefers to omit decimal points, he can easily do so. If the user likes putting in decimal points, he can put in decimal points. There is an issue or two with the code as it stands. See this month's trap for more information about things to look out for when you doing this kind of data manipulation.


    Trap of the Month - Controlling Data Entry Should Consider All Reasonable Options

    In this month's Wizard, we see a method for helping a user get reasonable data into the database. Let's say that the user had an excellent sales month and wanted to enter 105% of sales. With our existing code, this would be translated into 10.5%, which clearly doesn't work.

    If you have to handle this type situation, your job is little bit more complicated. You have to allow the user to enter something outside the typical bounds. You could write quite complex code and perhaps ask them to enter it again and on a second entry accept the value. However, this would get to be annoying for the user. A better approach is to provide an override in the form of a check box. If checked, it would allow the user to enter amounts greater than 100. Your code would simply have to have a provision that would look at that checkbox and, if it were checked, omit the step of dividing the entry by 100.

    If you have ever developed an application for other users, you probably do your best to predict what they will do. However, you will find that no matter how comprehensive your thought process, someone will come up with something unexpected. When that happens, you have a choice to make. You can either code your way around the new behavior, or ask the user to do things differently. If your user base is sufficiently small, asking them to modify their behavior can be a cost-effective technique.


    Tip of the Month - Automate Code Selection when Creating Events

    When you're coding for events within forms, the default behavior is to allow you to choose one of three builders: expression, macro, or code. As you do more and more development in Access, you will begin to migrate to code for anything other than the most simple tasks. You can avoid the Builder prompt and always go straight to code by changing one option in the program. How you do this depends on your version of Access.

    For Access 2003 and earlier, choose Tools from the menu at the top of the screen.
    In Access 2007, choose the Home button.
    In Access 2010, choose the File Ribbon.

    In Access 2003 and earlier, choose Options.
    In Access 2010/2007, choose Object Designers | Form/Report Design View.

    Then click on Always use Event Procedures. Now every time you choose to add an event to a control, you will be presented with and an ellipsis (aka "..."), click on the ellipsis and you'll be brought to the code window without having to choose the builder.

    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