You can reduce the likelihood of name input errors by doing the following:
-- Force users to input data via a form - never directly into the table.
-- Collect Surname and Forename in separate fields, as
dhookom advised in his reply.
-- Remove any non-alphabetic characters from these fields (but remember to allow
' for names such as O'Neill)
-- If you need a list of names in a single field, e.g. for lookup or sorting purposes, create a
ListName field which is generated by Access, and which users cannot modify directly.
Here is some of my VBA code, which shows how I achieve these results. In my example, my form and the underlying table contain three text fields, named:
EmployeeSurname
EmployeeForename
EmployeeListName
EmployeeListName on my form has these properties set, to prevent data entry directly into the field:
Enabled=Yes
Locked=Yes
In the
BeforeUpdate event of the form, I have:
Code:
EmployeeListName = EmployeeSurname & ", " & EmployeeForename
This creates a neat list format for names, e.g.
Jones, Peter
Jones, William
Smith, John
Wilson, Ann
etc
Here is my
CleanString function.
Code:
Function CleanString(strOneLine As String) As String
Dim I As Integer
Dim strOutLine As String
Dim strOneChar As String
Dim strAllowed As String
'---------------------------------------------------
'- Set up a string of allowed characters. In this -
'- case, A to Z and a to z plus single quote ' -
'---------------------------------------------------
strAllowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'"
'---------------------------------------------------
'- If an empty string is passed to the function, -
'- just exit. -
'---------------------------------------------------
If strOneLine = "" Then
strOutLine = ""
Exit Function
End If
'---------------------------------------------------
'- Build an output string containing the valid -
'- characters from the input string -
'---------------------------------------------------
For I = 1 To Len(strOneLine)
strOneChar = Mid$(strOneLine, I, 1)
If InStr(strAllowed, strOneChar) > 0 Then
strOutLine = strOutLine & strOneChar
End If
Next I
CleanString = strOutLine
End Function
This is placed in a module, and called from the AfterUpdate event of the surname and forename fields, like this:
Code:
EmployeeSurname = CleanString(EmployeeSurname)
I hope that these ideas will be useful.
Bob Stubbs