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:
... AND (RegExp(tblRole.Description,Trim([Forms]![frmFindRole]![txtRegExp]))<>False) AND ...
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.