MS Access query custom function accepting form text input as value

Question

G'day, everyone.

I've been banging my head against this question the whole day through today, and I haven't managed to find any answers, so I'd appreciate your help.

What I have:

An Access form containing a text field

A query which is the form's data source

A custom function called RegExp defined within a module

RegExp takes two values as input: string (obtained from a table) and pattern (obtained from the form). RegExp returns a boolean value which in turn thins out query results.

The function works perfectly fine and as expected, however, this is only the case when the user fills out the text field. If the field is left blank, no results are returned (and the function's not even getting called if that's the case).

So here's what that particular statement within the query looks like:

... AND (RegExp(tblRole.Description,Trim([Forms]![frmFindRole]![txtRegExp]))<>False) AND ...

(Basically, to sum it up, user types in a value into the text field which gets leading and trailing spaces trimmed off, converted to a regular expression inside a VBA module, and then query results get filtered based on what boolean value the function returns).

There is a number of controls on this form, and they worked prior to me adding that txtRegExp text field. Now the query only returns results if txtRegExp is filled out, and I have no idea why. I've tried adding more checks, but the query's too complicated already, and I haven't succeeded.

If additional code samples are required for an answer to be made, I'll be able to provide them tomorrow.

Thank you in advance.

P.S. Would Nz help? If yes, then how would I go about using it?

Answer 1

Based on the few explanations you gave in comments

Suppose that this is code triggered on the KeyUp event :

Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)

    Me.Requery

End Sub

Store the default SQL for your form's recordsource somewhere in a local variable. In this example I considered that you stored it in SQLdefault string.

Prior to requery, check if the textbox is empty and if yes change your form's recordsource SQL accordingly:

private SQLdefault as string

Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)

    Dim SQL As String

    If Nz(txtRegExp, "") = "" Then
        SQL = SQLdefault 
        SQL = Replace(SQL, "AND (RegExp(tblRole.Description,Trim([Forms]![frmFindRole]![txtRegExp]))<>False)", "")
        Me.RecordSource = SQL ' Normally this is enought to requery, if not uncomment below
        'Me.Requery
    Else
        Me.RecordSource = SQLdefault ' Normally this is enought to requery, if not uncomment below
        ' Me.Requery
    End If

End Sub

In this example I just remove the SQL part containning :

AND (RegExp(tblRole.Description,Trim([Forms]![frmFindRole]![txtRegExp]))<>False)

Replace it by something else if that's not correct.

That's obviously not the most elegant solution but it's difficult to provide with the best solution with what you've shown.

MS Access query custom function accepting form text input as value

Sign up