Run-time error 432 (1 Viewer)

[meanster99]

I have used the following code to validate UK postcodes in an Access 2003 database. Everything works fine with no apparent problems.

However, I have given the database to a friend who has Access 2007 - when he tries to enter a postcode (in the postcode field on a form, that has the function set as the before update event) he gets a run-time error:

Run-time Error 432: File name or class name not found during OLE Automation operation (based on could not create VBscript.RegExp Object).

I have checked that we both have the same references selected (the only difference is he has Microsoft Access 12.0 Object library and I have 11.0).

Can anyone shed any light on this as I am fairly new to vba and since I have only copied John Nurick's function I don't really understand it!

Option Compare Database

 'Some useful regular expressions:
                'Notes:
                'Each of these regular expressions is wrapped in a (?: )
                'grouping pattern. This means that they can be OR'd by
                'concatenating them with the pipe character "|". Thus
                ' rgxZIP_US & "|" & rgxZIP_CA
                'will match either US or Canadian postal codes.
                '
                'Official formatting of postcodes and the like may change
                'over time. Some of these expressions may need adjustment
                ' to bring them up to date.
                
                'UK Postcode
                Public Const rgxZIP_UK = "(?:(?: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})"
                
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

 

[meanster99]

Hi guys,

It seems no one can help me with this issue! Therefore, does someone know of another postcode format validation that I could use that has been tested in Access 2007 running on Windows XP?

Many thanks in advance....

 

[vbaInet]

It's a complicated regular expression and doesn't take a few minutes to decipher. However, the error looks like a reference problem to the RegExp library. I would have a closer look later on in the day (if you don't get any other replies).

In the meantime, here's a simple UK postcode validator from one of this forum's mods.
http://www.access-programmers.co.uk/forums/showthread.php?t=187898

 

[meanster99]

Thanks vbaInet - in my ignorance I hoped it would be an easy fix! Thank you for taking a look at it and for the validation link.

I look forward to your response later...

 

[vbaInet]

Not really an easy fix unfortunately. Regular expressions is a VBScript concept that can be used in vba or vb.

Here's what you should try. Open the References dialog box (i.e. from the VBA editor go to Tools -> References), scroll down the list until you find this option:

"Microsoft VBScript Regular Expressions 5.0"

Tick that box and OK it. Run the db and see if that works.

 

[Julian]

For VBA code to validate postcodes without Regex go to:

http://www.cleardatasystems.co.uk/postcodeformatvalidator.html

This is a more accurate validation than most of the regex codes I have seen and easier to understand!

Regards,

Julian.