regexms-accessvbaconditional-formattingcontinuous-forms

conditional fomatting on continuous forms using VBA(Access 2003)

I have a continuous form in access 2003 on which i need to perform validation using regular expressions in textboxes. I want to call the validation using a button click. I want to use constracts similar to conditional formatting in vba for regular expresions on th textboxes used on th form.

Please suugest pointers for direction on how to do it. If you have a code snippet doin it, request to share.

Solution

  • Conditional Formatting doesn't support Regex directly. You would need to create a user-defined function (UDF) in VBA. For example:

    Function FormatSalary(varField As Variant) As Boolean
    FormatSalary = (varField) > 20000
End Function

    This Function would use Regex before returning True or False. In the Conditional Formatting rule for the field, you would use Expression is and enter:

    FormatSalary([Salary])

    notice that there is no equals-sign preceding this.

    You can, I believe, do this programmatically when clicking a button, working with the FormatConditions collection in VBA. However, I suspect that you might need to switch the form back and forth to Design View. (I haven't tried this recently.)