When users are entering data into your Microsoft Access database, there may be times where you wish to view the information in the Microsoft Access forms, or store the information in your database tables, using a particular text case format.
Microsoft Access contains functions that will convert input in to Proper Case, Upper Case and Lower Case, and the first two are discussed here.
vbProperCase - Converts the first letter of every word in string to uppercase.
vbUpperCase - Converts the string to uppercase characters.
There are many ways available to add this functionality and one way is to create the functions in a module and call them in the After Update event of a text box on your forms. To add the code to a module you can use the following examples:
'---------------------------- 'Converts Text to Proper Case '---------------------------- Public Sub ConvertToProper() 'Test if control contains text If IsNull(Screen.ActiveControl) = False Then 'convert text to Proper Case Screen.ActiveControl = StrConv(Screen.ActiveControl, vbProperCase) End If End Sub '---------------------------- 'Converts Text to Upper Case '---------------------------- Public Sub ConvertToUpper() 'Test if control contains text If IsNull(Screen.ActiveControl) = False Then 'convert text to Proper Case Screen.ActiveControl = StrConv(Screen.ActiveControl, vbUpperCase) End If End Sub
Now to call these add the following to the After Update event procedure of any textbox that you want to ensure the format of the data:
Private Sub strStudentLastName_AfterUpdate() 'Convert the text entered to Proper Case ConvertToProper End Sub Private Sub strStudentNumber_AfterUpdate() 'Convert the text entered to Upper Case ConvertToUpper End Sub
You can now view and store the information in the desired format. Use the same approach to implement the vbLowerCase function.