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

What If There Were No Ifs -- Use Select Case

Occam's razor is a wonderful scientific principle to help understand a variety of phenomena. It basically states that if you have competing theories as to why something happens, you're best off using the simplest explanation available.

The example I like to cite is the old heliocentric versus geocentric view of planetary motion (there's nothing like those old high school astronomy classes!) Under the geocentric theory, the planets and sun revolved around the earth (at one point in time this was the accepted theory). There's a problem with this approach however; when you view the motion of planets from the earth, at certain times they appear to move backwards. Scientists call this retrograde motion.

Within the geocentric theory of planetary motion, scientists developed mathematical formulas to predict where the planets and the sun would be, even taking into account retrograde motion. As you can imagine, the math was complex.

Under the heliocentric theory, where planets revolved around the sun, planetary motion became much easier to predict mathematically, since they essentially moved in a smooth ellipse rather than periodically changing direction.

Copernicus was the first scientist who put forth the theory of geocentric planetary motion. Rulers at that time were very unhappy with him since his theory suggested that man was not the center of the universe; he was ultimately forced to renounce his theory of geocentric planetary motion. Future scientists agreed with him, and because the application of Occam's razor, we now accept that planets travel around the sun rather than the earth.

In a like way, when developing code or logic, you gain long term benefits by choosing the least complicated approach.

This month we'll take a look at one method for making life simpler - choosing the Select Case method of managing cases over the If Else method.

In this Issue
  • Shameless Self-Promotion Department
  • The simple If, End If; the Complex Else If
  • The Select Case Method
  • Conclusion
  • Tip of the Month - F5: An Easy Way to Get from Design View to Run View

  • The simple If, End If; the Complex Else If

    The use of the If and End If in code in most cases is quite straightforward:

    If some condition exits then
       Do something
       Do something else
    End if

    If things are a bit more complex, you might be tempted to use the Else If approach.

    Take the following routine:

    Sub DemoIf()
    If some condition exits then:
        Dim lngValue As Double
        lngValue = 2
        If lngValue = 1 Then
          MsgBox "1"
           ElseIf lngValue = 2 Then
          MsgBox "2"
          ElseIf lngValue = 3 Then
          MsgBox "3"
          MsgBox "Something Else"
        End If<.br> End Sub

    You can see that the goal here is simply to establish which of the series of steps to take in each case. The code basically says, if the value equals"1", then display a message box showing 1. If it's not equal to 1, move on to the next test on the list, and if it's not equal to that, continue down the list.

    The Select Case Method

    Rather than the code above using the ElseIf construct, take a look at this code using the Select Case method:

    Sub DemoSelectCase()
       Dim lngValue As Double
        lngValue = 2
        Select Case lngValue
          Case Is = 1
          MsgBox "1"
          Case Is = 2
          MsgBox "2"
          Case Is = 3
          MsgBox "3"
          Case Else
         MsgBox "Som ething Else"
        End Select End Sub

    This code basically does the same thing as the Else If approach, however you'll see that the first critical line

    Select Case lngValue

    Says Select the Case of our variable, lngValue

    Case is = 1

    In the event that the variable equals one, execute the next line (MsgBox "1").

    You'll notice that you don't repeat the variable name nor must you include the Then statement. The result is the same with Select Case as with Else If (the user sees the message box), but using the Select Case method streamlines the code.

    Note that I've also used a Case Else statement. I recommend that you always include a Case Else when you use Select Case so that you cover all your bases with code that properly handles all possible situations. Even if you think that you'll never have another case, it's always a good idea to accommodate all options, giving you stronger, more flexible code.


    By using Select Case, rather than the If , Else If approach, you'll find your code easier to write, easier to read, and easier to debug -- all of which will save you time and effort.

    Tip of the Month - F5: An Easy Way to Get from Design View to Run View

    When creating forms you'll likely spend a lot of time going back and forth between design view and form view. Although you can use the menu to get from one to the other, it's much easier to toggle between views using the F5 key. This will take you right to your form as your users will see it. If you're a keyboard user (like me), this is much faster than fiddling with your mouse.

    This tip will also work when designing reports.

    Shameless Self-Promotion Department

    On October 17 I'll be speaking before the Boston Area Access Developers Group in Waltham, MA. See htt p://www.bostonaccessday.com/index.cfm for details.

    I'll be showing the group how to create lists on steroids, with a focus on managing multiple lists. If you're a developer, you'll see how adding this approach will empower your users to make changes themselves rather than relying on you.

    This event is free and open to the public, so if you're in the area, please drop bye and say "hi".

    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