The following code is for when a user wants to use a Form and a Function (i.e. Module) for calling a Split Array.
The following is the Form code. The bold text is the portion that calls the Function (i.e. Module) for Split Array.
Code:
Private Sub btnFindRecord_Click()
On Error GoTo btnFindRecord_Click_Err
btnFindRecord_Click_SplitSearch:
If Me.[txtRecordSearchBox] <> "" Then
Call SplitSearch
Else
GoTo btnFindRecord_Click_Exit
End If
btnFindRecord_Click_SearchRecord:
Dim strSQL As String
strSQL = "SELECT ID, DataEntry AS [Data], CreatedBy AS [Creator], DateCreated AS [Date], ModifiedBy As [Adjuster], LastModified AS [Last Modified] " _
& "FROM tblDataEntry " _
& "WHERE ID LIKE '*" & Me.txtRecordSearchBox & "*' " _
& "OR DataEntry LIKE '*" & Me.[txtRecordSearchBox] & "*' " _
& "OR CreatedBy LIKE '*" & Me.txtRecordSearchBox & "*' " _
& "OR DateCreated LIKE '*" & Me.txtRecordSearchBox & "*' " _
& "OR TimeCreated LIKE '*" & Me.txtRecordSearchBox & "*' " _
& "OR ModifiedBy LIKE '*" & Me.txtRecordSearchBox & "*' " _
& "OR LastModified LIKE '*" & Me.txtRecordSearchBox & "*' " _
& "OR Notes LIKE '*" & Me.txtRecordSearchBox & "*' "
Me.
[listSearch].RowSource = strSQL
Me.
[listSearch].Requery
btnFindRecord_Click_Exit:
Exit Sub
btnFindRecord_Click_Err:
MsgBox Err.Description, vbOKOnly, ""
Resume btnFindRecord_Click_Exit
End Sub
The following is the Function (i.e. Module) for executing the Split Array. Note: Less than two and more than two = '" & SearchArray(0) & "' Or '" & SearchArray(1) & "' will cause an error and will not search for entries properly.
Code:
Option Compare Database
Option Explicit
Public Function SplitSearch()
On Error Resume Next
Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
Dim SearchBox As String
Dim SearchArray() As String
Dim i As Integer
SearchBox = frmCurrentForm.[txtRecordSearchBox]
SearchArray() = Split(SearchBox, " ", -1)
For i = LBound(SearchArray, 1) To UBound(SearchArray, 1)
Next i
If (IsNull(DLookup("DataEntry", "tblDataEntry", "DataEntry = '" & SearchArray(0) & "' Or '" & SearchArray(1) & "'"))) Then
Exit Function
Else
modDataEntry.Tracker "Invalid Data Entry Detected"
Beep
MsgBox ("Invalid Data Entry Detected. Entry was blocked!"), vbOKOnly, "Warning"
frmCurrentForm.btnFindRecord_Click_Exit
End If
End Function