ACC: Sample Function to Capitalize Words and Phrases

MORE INFORMATION

   '**********************************************************
   ' Declarations section of the module
   '**********************************************************
      Option Explicit
   '==========================================================
   ' The following function is designed for use in the AfterUpdate
   ' property of form controls.
   ' Features:
   '    - Leading spaces do not affect the function's performance.
   '    - "O'Brian" and "Wilson-Smythe" will be properly capitalized.
   ' Limitations:
   '    - It will change "MacDonald" to "Macdonald."
   '    - It will change "van Buren" to "Van Buren."
   '    - It will change "John Jones III" to "John Jones Iii."
   '==========================================================
   Function Proper (AnyValue As Variant) As Variant
      Dim ptr As Integer
      Dim TheString As String
      Dim currChar As String, prevChar As String

      If IsNull(AnyValue) Then
         Exit Function
      End If

      TheString = CStr(AnyValue)
      For ptr = 1 To Len(TheString)         ' Go through each char. in
                                            ' string.
      currChar = Mid$(TheString, ptr, 1)    ' Get the current character.

         Select Case prevChar               ' If previous char. is a
                                            ' letter,'this char. should be
                                            ' lowercase.
         Case "A" To "Z", "a" To "z"
            Mid(TheString, ptr, 1) = LCase(currChar)

         Case Else
            Mid(TheString, ptr, 1) = UCase(currChar)

      End Select
      prevChar = currChar
      Next ptr
      AnyValue = theString
   End Function

How to Use This Function in the AfterUpdate Property of a Control

1. Create a form based on a table or query.
2. Type the following line in the AfterUpdate property of a text box control:
   
   =Proper(<control name>)
   
   For example, if the ControlName is [Full Name], type the following:
   
   =Proper([Full Name])

How to Modify This Function for Expressions and Calculated Controls

1. Enter the code shown above to create the Proper() function.
2. Modify the following section of code
   

            AnyValue= theString
         End Function

   
   To read as follows:
   

            Proper=CVar(theString)
         End Function

   
   

Examples

   Field: Full Name:Proper([Last Name] & " " & [First Name])

   This will concatenate the first and last names and capitalize the
   first letter of each.

   TextBox
   ---------------------------------
   ControlName: AddressP
   ControlSource: =Proper([Address])

   NOTE: In calculated fields, the ControlName must be unique.

   Action: SetValue
   Item: Screen.ActiveControl

   Expression: Proper(Screen.ActiveControl)

   NOTE: You can call this macro from the AfterUpdate property of a control
   on a form. This has the same affect as the first version of the Proper()
   function.