Sharlee
.com

MS Access Database Programming

HOME BACK

Function ProperString
A function for automatic capitalization of proper words.
For MS Access 97/2000

This code can be applied to any text field to create automatic capitalization
of the first letter of each word entered to that field.

Example: mary b. stevens will be automatically updated to Mary B. Stevens

The code works with a table called u_PROPER_WORDS, which is used to collect proper
names that should not be converted. The code looks in this table and finds any
match. If it finds a match it uses the entry in the table rather than converting
only the first letter of the proper word found.

Example: macdonald would be found in the table and converted to its entry: MacDonald

Users can add new proper names to the table by pressing CTRL + A. This pops up an
input box into which they can enter the word to the table in its properly spelled form.

This code is intended for use in Microsoft Access 97/2000. It may not run properly in
earlier versions. Microsoft DAO 3.6 Object Library must be set as a Reference in the
database modules settings.

A) How to Add the Code to Your Database:

1. Make a new general module.
2. Copy the code in the window that follows to your new general module

Note: You will add this code only once to each database you want to use it in.

'*************************************************************************
' Copyright (C) 1999  by Sharlee Plett  ALL RIGHTS RESERVED
' EMail: webmaster@sharlee.com   Website: http://www.sharlee.com
'
' This code is free code; you can redistribute it and/or
' modify it under the terms of the GNU General Public License
' as published by the Free Software Foundation; either version 2
' of the License, or (at your option) any later version.
'
' This code is distributed in the hope that it will be useful,
' but WITHOUT ANY WARRANTY; without even the implied warranty of
' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
' GNU General Public License for more details.
'
' You should have been given access to the GNU General Public License
' along with this code; if not, write to the Free Software
' Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA
' or go to http://www.gnu.org/copyleft/gpl.html
'*************************************************************************

Public Function ProperString(tString As String) As String
Dim tLen As Integer, tFind As Integer, aFind As Integer, NewString As String, tWord As String
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM u_PROPER_WORDS;")

tLen = Len(tString)
tFind = InStr(1, tString, " ")
aFind = InStr(1, tString, "'")
NewString = ""
If tFind <> 0 Then
    Do
        tLen = Len(tString)
        tWord = Left(tString, tFind - 1)
            If Not IsNumeric(tWord) Then
                aFind = InStr(1, tWord, "'")
                If aFind = 0 Then
                rst.FindFirst "PROPER_WORD = '" & tWord & "'"
                    If rst.NoMatch Then
                        tWord = UCase(Left(tWord, 1)) + LCase(MID(tWord, 2, _
						tFind - 1))
                    Else
                        tWord = rst("PROPER_WORD")
                    End If
                ElseIf aFind = 2 Then
                    tWord = UCase(Left(tWord, 2)) & UCase(MID(tWord, 3, 1)) & _
                    LCase(Trim(MID(tWord, 4, 50)))
                ElseIf aFind > 2 Then
                    tWord = UCase(Left(tWord, 1)) & LCase(Trim(MID(tWord, 2, 50)))
                End If
            End If
        NewString = NewString + tWord + " "
        tString = MID(tString, tFind + 1, tLen)
        tFind = InStr(1, tString, " ")
            If tFind = 0 Then
            tWord = tString
                If Not IsNumeric(tWord) Then
                    aFind = InStr(1, tWord, "'")
                    If aFind = 0 Then
                    rst.FindFirst ("PROPER_WORD = '" & tWord & "'")
                        If rst.NoMatch Then
                            tWord = UCase(Left(tWord, 1)) + LCase(MID(tWord, 2, _
                            Len(tWord)))
                        Else
                            tWord = rst("PROPER_WORD")
                        End If
                    ElseIf aFind = 2 Then
                        tWord = UCase(Left(tWord, 2)) & UCase(MID(tWord, 3, 1)) & _
                        LCase(Trim(MID(tWord, 4, 50)))
                    ElseIf aFind > 2 Then
                        tWord = UCase(Left(tWord, 1)) & LCase(Trim(MID(tWord, 2, 50)))
                    End If
                End If
            NewString = NewString + tWord + " "
            End If
    Loop Until tFind = 0
Else
    tWord = tString
        If Not IsNumeric(tWord) Then
            aFind = InStr(1, tWord, "'")
            If aFind = 0 Then
            rst.FindFirst ("PROPER_WORD = '" & tWord & "'")
                If rst.NoMatch Then
                    tWord = UCase(Left(tWord, 1)) + LCase(MID(tWord, 2, Len(tWord)))
                Else
                    tWord = rst("PROPER_WORD")
                End If
            ElseIf aFind = 2 Then
                tWord = UCase(Left(tWord, 2)) & UCase(MID(tWord, 3, 1)) & _
                LCase(Trim(MID(tWord, 4, 50)))
            ElseIf aFind > 2 Then
                tWord = UCase(Left(tWord, 1)) & LCase(Trim(MID(tWord, 2, 50)))
            End If
        End If
    NewString = NewString + tWord + " "
End If

ProperString = Trim(NewString)

End Function

Public Sub AddToProperWords()
    Dim dbs As Database
    Dim rstInput As Recordset
    Dim tInput As String
    Dim tWord As String
    Dim strSQL As String
    
    tInput = InputBox("Type the word you want to add in proper case:", _
                    "Add Proper Word")
       
    ' Proceed only if the user actually entered something.
    If tInput = "" Then
        Exit Sub
    Else
        Set dbs = CurrentDb
        strSQL = "SELECT * FROM u_PROPER_WORDS WHERE PROPER_WORD ='-1'"
        Set rstInput = dbs.OpenRecordset(strSQL)
                                        
        rstInput.AddNew
        rstInput!PROPER_WORD = tInput
        rstInput.Update
        
        dbs.Close
        'rstInput.Close
        Set rstInput = Nothing
    End If

End Sub

3. Create a new table in your database and name it u_PROPER_WORDS.
      a. Add a text field to the table
      b. Name the field: PROPER_WORD

B) How To Set up Your Form to Use the Code:

1. Set the form's Key Preview Event to Yes
2. Copy the following code to the form's On Key Down Event

   Note: You will need to add this code to each form you want to use it in.

C) How To Use the Code on Individual Fields

1. Copy the code that follows to the AfterUpdate event of the field you want to use it on.
    Note: You will need to replace the word "LAST" with whatever the actual name of your field is.
2. Teach your users how to use the function. You might want to create a utility form for
    your users to edit the Proper Words table in case of user errors that produce unwanted
    entries to the table.

NOTICE:

Please include the Copyright and General Public License notice section in your
general module. Here is the web address for the license: GNU General Public License.

If you are not familiar with this license, you should read it before you implement
this code into your database. Since you are a programmer, you should read it anyway
so you can use it if you need to for your own code!

TOP     HOME     BACK