Search code examples
regexvbams-accessregexbuddy

Regex not Triggering VBA

This is my regex:

    Dim vbRegX As Object, vbRegXMatch As Object
    Set vbRegX = CreateObject("vbscript.regexp")

    With vbRegX
        .Global = True
        .IgnoreCase = True
        .Pattern = "^[a-zA-Z0-9_-]{1,20}$"
    End With

code that uses it:

    Set vbRegXMatch = vbRegX.Execute(Me.txtProduct.Text)
    If vbRegXMatch.Count = 1 Then
        MsgBox "This string has invalid characters in it. Illegal characters are out side of the following ranges:" & vbNewLine & vbNewling & "a-z or A-Z" & vbNewLine & vbNewling & "0-9, - or _. Please try again."
        Cancel = True
        Me.txtProduct.SetFocus
        Set vbRegXMatch = Nothing
        Set vbRegX = Nothing
        Exit Sub
    End If

This code fires with invalid characters but not when length is > 20. This is the output given to me by Regex Buddy:

Dim FoundMatch As Boolean
Dim myRegExp As RegExp
Set myRegExp = New RegExp
myRegExp.Pattern = "^[a-zA-Z0-9_-]{1,20}$"
FoundMatch = myRegExp.Test(SubjectString)

Can anyone so kindly point out what Im missing?

visual of the control:

enter image description here

Solution

  • Your regex matches valid input. Thus, you need to .Test(your_string) and if the result is False, you need to fire an error.

    Replace 

    Set vbRegXMatch = vbRegX.Execute(Me.txtProduct.Text)
If vbRegXMatch.Count = 1 Then

    with

    If vbRegX.Test("1234555") = False Then

    Also, since you expect a single match, use

    .Global = False