How do I test the result of a function in an MS Access macro

I am trying to pass a post code to a function I have created. I need the function to return VALID if the post code is valid. I then want to test the return to display an error message if not valid.

My function is:

Public Function IsUKPostCode(strInput As String)
'
' Uses a regular expression to validate the format of a postcode.
'
Dim RgExp As Object
Set RgExp = CreateObject("VBScript.RegExp")
'
' Clear the function value
'
IsUKPostCode = ""
'
' Check if we have a value to test
'
If strInput = "" Then
  IsUKPostCode = "No Post Code entered"
  Exit Function
End If
'
' This is the regular expression that validates the postcode
RgExp.Pattern = "^[A-Z]{1,2}\d{1,2}[A-Z]?\s\d[A-Z]{2}$"
'
' Does the input string match the pattern?
'
If RgExp.Test(strInput) = True Then
  IsUKPostCode = "Valid"
  End If
End Function

I call the function from an MS Access macro using:

RunCode IsUKPostCode([Forms]![People maintenance]![People.Post Code]

I then want to test the return result to display a message. Can anyone help with the correct If statement please?

Description above is sufficient.

Solution

It would possibly be easier to do that completely with VBA Code.

Anyway, here is a way to do it with an Access Macro. First of all it might be a good idea a to add an Else clause to your function to have a return value for both cases

Public Function IsUKPostCode(strInput As String) As String
    ' Uses a regular expression to validate the format of a postcode.
    Dim RgExp As Object
    Set RgExp = CreateObject("VBScript.RegExp")
    
    ' Clear the function value
    IsUKPostCode = ""
    
    ' Check if we have a value to test
    If strInput = "" Then
        IsUKPostCode = "No Post Code entered"
        Exit Function
    End If
    
    ' This is the regular expression that validates the postcode
    RgExp.Pattern = "^[A-Z]{1,2}\d{1,2}[A-Z]?\s\d[A-Z]{2}$"
    
    ' Does the input string match the pattern?
    If RgExp.Test(strInput) = True Then
        IsUKPostCode = "Valid"
    Else
        IsUKPostCode = "Invalid Post Code"
    End If
End Function

Use the Function in the Macro In your MS Access macro, you want to call the function, store the result, and then display a message based on the result.

You can do this by:

Creating a Temporary Variable: Use a temporary variable to store the return value of IsUKPostCode.

Using an If Statement: Based on the value of this variable, display an appropriate message.

Here’s how you can set it up:

a. Create a Temporary Variable in the Macro Go to the Macro Design view.

Create a new SetTempVar action to store the result of the function:
Name: PostCodeResult (or any other meaningful name)
Expression: IsUKPostCode([Forms]![People maintenance]![People.Post Code])

b. Add a Conditional Statement

After setting the temporary variable, use the If action to check the value of PostCodeResult:

Condition: [TempVars]![PostCodeResult] = "Valid"
If the condition is true, use the MessageBox action to display a message like "Post Code is valid."
Add an Else clause to display a different message if the post code is not valid:
MessageBox: "Invalid Post Code entered."

c. Clean Up Temporary Variables

After the If statement, use RemoveTempVar to clean up the temporary variable PostCodeResult