VBA Username and Password Regex (MS Access)-CodePudding

Supposing I have the following code in a button: (with the implementation of userNameMatches and passwordMatches missing)

Private Sub Command1_Click()

If userNameMatches And passwordMatches Then
    MsgBox "Welcome!"
    DoCmd.Close
    DoCmd.OpenReport "HomePage", acViewReport
Else
    MsgBox "Please enter valid credentials."
End If

End Sub

The username text input field is named username. The password text input field is named password.

The regex pattern I want for the username is: "^[A-Za-z][A-Za-z0-9_]{3,16}$"

The regex pattern I want for the password is: "^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$"

My Question: How do I implement userNameMatches and passwordMatches to return True if their respective text fields match the patterns, and False if they do not?

Thanks a lot in advance!

CodePudding user response：

To implement userNameMatches and passwordMatches, you can use the Like operator in VBA to check if the strings in the username and password text fields match the regex patterns.

The Like operator allows you to compare a string to a pattern and returns True if the string matches the pattern, and False otherwise. The syntax for using the Like operator is as follows:

string Like pattern

Here, string is the string you want to compare to the pattern, and pattern is the regex pattern you want to use. The Like operator supports the same regex syntax as the Match function in VBA.

To implement userNameMatches and passwordMatches, you can use the Like operator in the following way:

Private Function userNameMatches() As Boolean
    userNameMatches = username.Value Like "^[A-Za-z][A-Za-z0-9_]{3,16}$"
End Function

Private Function passwordMatches() As Boolean
    passwordMatches = password.Value Like "^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$"
End Function

In the above code, userNameMatches returns True if the value in the username text field matches the regex pattern "^[A-Za-z][A-Za-z0-9_]{3,16}$", and False otherwise. Similarly, passwordMatches returns True if the value in the password text field matches the regex pattern "^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$", and False otherwise.

You can then use these functions in your Command1_Click event handler as follows:

Private Sub Command1_Click()
    If userNameMatches() And passwordMatches() Then
        MsgBox "Welcome!"
        DoCmd.Close
        DoCmd.OpenReport "HomePage", acViewReport
    Else
        MsgBox "Please enter valid credentials."
    End If
End Sub

In this code, if both userNameMatches() and passwordMatches() return True, the user is welcomed and the HomePage report is opened. Otherwise, a message is displayed prompting the user to enter valid credentials.