SELECT CASE: Great Routine for Simplifying "If....Then" Statements

Sunday, March 10, 2002 20:19:15

As an example, suppose you want different code to execute based on a value selected in a combo box. There are about 5 different possibilities. You realize in this case a bunch of "If...then" Statements really makes the code quite long & drawn-out, especially if there are IF...THEN statements nested within IF...THEN statements. Messy, messy, messy.

As one simple example, here is how "Select Case" would look, say, for executing different code based on the data entered in a field named LAST NAME:

Select Case [LastName].value
        Case "Doe"
            msgbox "Hello Mr.Doe"
        Case "Bush"
            msgbox "Hello Mr. Bush"
        Case Else
            msgbox "Not Found"
End Select

Here is some actual code I used for executing different routines based on values entered in a combo box named DISPOSITION:

Derived from "Keycode" Database, March 2002


Private Sub disposition_Change()
Select Case [disposition].Value
   Case "Key Code Denied"
        If Not IsNull(Me.keycode) Then
              ' The next 4 lines "reclaim" this keycode
            Dim stQt As String
            stQt = """"
            sSQL = "UPDATE tbl_key_codes SET tbl_key_codes.used = No WHERE Keycode = " & stQt & Me.keycode & stQt
            CurrentDb.Execute sSQL
        End If
        '   Next 5 Lines Enable All Controls on Form
        For Each cntrl In Me.Controls
            If cntrl.ControlType = acTextBox Or cntrl.ControlType = acComboBox Then
              cntrl.Enabled = True
            End If
        Next cntrl
        Me.keycode = Null
        Me.keycode.Enabled = False
        Me.reason = Null
        Me.reason.Enabled = False
        Me.verification = Null
        Me.verification.Enabled = False
        DoCmd.GoToControl "denial_reason"
        SendKeys "{F4}"
   Case "Key Code Issued"
        '   Next 5 Lines Enable All Controls on Form
        For Each cntrl In Me.Controls
            If cntrl.ControlType = acTextBox Or cntrl.ControlType = acComboBox Then
              cntrl.Enabled = True
            End If
        Next cntrl
        Me.denial_reason = Null
        Me.denial_reason.Enabled = False
        DoCmd.GoToControl "company_name"
   Case "Document Requested"
        If Not IsNull(Me.keycode) Then
              ' The next 4 lines "reclaim" this keycode
            Dim stQt As String
            stQt = """"
            sSQL = "UPDATE tbl_key_codes SET tbl_key_codes.used = No WHERE Keycode = " & stQt & Me.keycode & stQt
            CurrentDb.Execute sSQL
        End If
        '   Next 5 Lines Enable All Controls on Form
        For Each cntrl In Me.Controls
            If cntrl.ControlType = acTextBox Or cntrl.ControlType = acComboBox Then
              cntrl.Enabled = True
            End If
        Next cntrl
        Me.denial_reason = Null
        Me.denial_reason.Enabled = False
	  Me.keycode = Null
        Me.keycode.Enabled = False
        DoCmd.GoToControl "company_name"
   Case Else
        ' code to execute for any option BESIDES Key Code Denied
        '   Next 5 Lines Enable All Controls on Form
        For Each cntrl In Me.Controls
           If cntrl.ControlType = acTextBox Or cntrl.ControlType = acComboBox Then
           cntrl.Enabled = True
           End If
        Next cntrl
        Me.denial_reason = Null
        Me.denial_reason.Enabled = False
        DoCmd.GoToControl "company_name"
End Select
End Sub