Hi,
I use the following
Private Sub PostCode_BeforeUpdate(Cancel As Integer)
If Not rgxValidate(Forms.FrmAddNewAccount.PostCode, "(?
?:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})") Then
MsgBox "The Postcode you Entered " & Me.PostCode & " is incomplete or incorrect, Please Enter a Valid UK Postcode", vbOKOnly, "Just to let you know"
Cancel = True
End If
End Sub
this is the function
Function rgxValidate( _
Target As Variant, _
Pattern As String, _
Optional CaseSensitive As Boolean = False, _
Optional MatchWholeString As Boolean = True, _
Optional FailOnError As Boolean = True) _
As Boolean
'Returns True if Target matches the regular
'expression Pattern.
'By John Nurick, October 2002 - January 2003
'©2003 John Nurick
'NOTES:
'Target will normally be a String. If Target is Null,
'rgxValidate returns False. Otherwise if Target cannot be
'converted to a string with CStr(), rgxValidate fails
'with Error 13, Type Mismatch.
'Pattern should be a VBScript regular expression. See VBScript
'help file and other documentation for information.
'CaseSensitive does the expected.
'MatchWholeString: if False, rgxValidate returns True if any
'substring of Target matches Pattern. If True or omitted,
'the function only returns True if the whole of Target
'matches Pattern.
' E.g. Target "12345" only matches Pattern "234" if
' MatchWholeString is False.
'FailOnError: if this is True or omitted, rgxValidate passes
'any run time error to the calling procedure. If it is False,
'the function returns True on a successful match and False if
'the match fails for any reason including a run time error.
'rgxValidate is suitable for use in data entry forms and the
'like. It can also be used in queries and in looping through
'recordsets, but because it creates a RegExp object and compiles
'the regular expression (Pattern) every time it is called,
'it is rather inefficient for repetitive operations.
'Constants for messages:
Const rgxPROC_NAME = "rgxValidate"
Const rgxERRMSG_CREATE = "Could not create VBScript.RegExp object: "
Const rgxERRMSG_UNEXPECTED = "Unexpected error: "
'VBScript.Regexp error messages:
Const rgxERRMSG_5017 = "Syntax error in regular expression"
Const rgxERRMSG_5019 = "Expected ']' in regular expression"
Const rgxERRMSG_5020 = "Expected ')' in regular expression"
Dim oRE As Object
On Error GoTo ErrHandler
rgxValidate = False 'Set default return value
If IsNull(Target) Then Exit Function
Set oRE = CreateObject("VBScript.RegExp")
'If we're here, the object has been created
oRE.Global = False
oRE.IgnoreCase = Not CaseSensitive
oRE.Multiline = False
If MatchWholeString Then
'Add anchors at ends of Pattern
'(doesn't matter if Pattern already has them)
oRE.Pattern = "^" & Pattern & "$"
Else
oRE.Pattern = Pattern
End If
'Do it!
rgxValidate = oRE.Test(CStr(Target))
'If we're here, the match executed OK. Normal termination
Set oRE = Nothing
Exit Function
ErrHandler:
If FailOnError Then
With err
Select Case .Number
Case 5017: .Description = rgxERRMSG_5017
Case 5019: .Description = rgxERRMSG_5019
Case 5020: .Description = rgxERRMSG_5020
Case Else
If oRE Is Nothing Then
.Description = rgxERRMSG_CREATE & err.Description
Else
.Description = rgxERRMSG_UNEXPECTED & err.Description
End If
End Select
Set oRE = Nothing
err.Raise err.Number, , rgxPROC_NAME & "(): " & .Description
End With
Else 'Fail silently
err.Clear
Set oRE = Nothing
End If
End Function
So when the user enters a postcode it will validate the Postcode.
What I would now like to do is, after I import a table, I would like to loop through all the postcodes and either produce a list that need correcting or ideally correct the postcodes automatically. Is there away of adapting this code or do I need to start from scratch.
Any guidance and help would be appreciated
thanks
cneill
I use the following
Private Sub PostCode_BeforeUpdate(Cancel As Integer)
If Not rgxValidate(Forms.FrmAddNewAccount.PostCode, "(?
?:A[BL]|B[ABDHLNRST]?|" _& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})") Then
MsgBox "The Postcode you Entered " & Me.PostCode & " is incomplete or incorrect, Please Enter a Valid UK Postcode", vbOKOnly, "Just to let you know"
Cancel = True
End If
End Sub
this is the function
Function rgxValidate( _
Target As Variant, _
Pattern As String, _
Optional CaseSensitive As Boolean = False, _
Optional MatchWholeString As Boolean = True, _
Optional FailOnError As Boolean = True) _
As Boolean
'Returns True if Target matches the regular
'expression Pattern.
'By John Nurick, October 2002 - January 2003
'©2003 John Nurick
'NOTES:
'Target will normally be a String. If Target is Null,
'rgxValidate returns False. Otherwise if Target cannot be
'converted to a string with CStr(), rgxValidate fails
'with Error 13, Type Mismatch.
'Pattern should be a VBScript regular expression. See VBScript
'help file and other documentation for information.
'CaseSensitive does the expected.
'MatchWholeString: if False, rgxValidate returns True if any
'substring of Target matches Pattern. If True or omitted,
'the function only returns True if the whole of Target
'matches Pattern.
' E.g. Target "12345" only matches Pattern "234" if
' MatchWholeString is False.
'FailOnError: if this is True or omitted, rgxValidate passes
'any run time error to the calling procedure. If it is False,
'the function returns True on a successful match and False if
'the match fails for any reason including a run time error.
'rgxValidate is suitable for use in data entry forms and the
'like. It can also be used in queries and in looping through
'recordsets, but because it creates a RegExp object and compiles
'the regular expression (Pattern) every time it is called,
'it is rather inefficient for repetitive operations.
'Constants for messages:
Const rgxPROC_NAME = "rgxValidate"
Const rgxERRMSG_CREATE = "Could not create VBScript.RegExp object: "
Const rgxERRMSG_UNEXPECTED = "Unexpected error: "
'VBScript.Regexp error messages:
Const rgxERRMSG_5017 = "Syntax error in regular expression"
Const rgxERRMSG_5019 = "Expected ']' in regular expression"
Const rgxERRMSG_5020 = "Expected ')' in regular expression"
Dim oRE As Object
On Error GoTo ErrHandler
rgxValidate = False 'Set default return value
If IsNull(Target) Then Exit Function
Set oRE = CreateObject("VBScript.RegExp")
'If we're here, the object has been created
oRE.Global = False
oRE.IgnoreCase = Not CaseSensitive
oRE.Multiline = False
If MatchWholeString Then
'Add anchors at ends of Pattern
'(doesn't matter if Pattern already has them)
oRE.Pattern = "^" & Pattern & "$"
Else
oRE.Pattern = Pattern
End If
'Do it!
rgxValidate = oRE.Test(CStr(Target))
'If we're here, the match executed OK. Normal termination
Set oRE = Nothing
Exit Function
ErrHandler:
If FailOnError Then
With err
Select Case .Number
Case 5017: .Description = rgxERRMSG_5017
Case 5019: .Description = rgxERRMSG_5019
Case 5020: .Description = rgxERRMSG_5020
Case Else
If oRE Is Nothing Then
.Description = rgxERRMSG_CREATE & err.Description
Else
.Description = rgxERRMSG_UNEXPECTED & err.Description
End If
End Select
Set oRE = Nothing
err.Raise err.Number, , rgxPROC_NAME & "(): " & .Description
End With
Else 'Fail silently
err.Clear
Set oRE = Nothing
End If
End Function
So when the user enters a postcode it will validate the Postcode.
What I would now like to do is, after I import a table, I would like to loop through all the postcodes and either produce a list that need correcting or ideally correct the postcodes automatically. Is there away of adapting this code or do I need to start from scratch.
Any guidance and help would be appreciated
thanks
cneill
