I have a DataSheet form where I want to search my record using an Unbound Textbox. I have write AfterUpdate code for Textbox which is as under:-
Expand|Select|Wrap|Line Numbers
- Private Sub txtSearch_AfterUpdate()
- Me.RecordSource = _
- "SELECT * " & _
- "FROM Students " & _
- "WHERE (fncSearch([ID]," & Chr(34) & Me.txtSearch & Chr(34) & ",'Students')=True);"
- End Sub
Expand|Select|Wrap|Line Numbers
- Public Function fncSearch( _
- ByVal ID As Variant, _
- ByVal textSearch As Variant, _
- ByVal TableName As String) _
- As Boolean
- Dim varText As Variant
- Dim colText As Collection
- Dim colResult As Collection
- Dim intCount As Integer
- Dim rs As DAO.Recordset
- Dim varTempFound() As Variant
- Dim fld As DAO.Field
- ' exit if there is nothing to search
- If Trim(textSearch & "") = "" Then
- fncSearch = True
- Exit Function
- End If
- ' break the txtSearch into an array
- varText = Split(textSearch, " ")
- Set colText = New Collection
- ' add array element to collection
- For intCount = 0 To UBound(varText)
- colText.Add varText(intCount)
- Next
- Set rs = CurrentDb.OpenRecordset( _
- "SELECT * FROM " & TableName & " WHERE " & _
- "[ID] = " & ID, dbOpenSnapshot)
- ReDim varTempFound(0)
- With rs
- If Not (.BOF And .EOF) Then
- .MoveFirst
- For Each fld In .Fields
- If UBound(varTempFound) = UBound(varText) + 1 Then
- fncSearch = True
- Exit For
- End If
- For intCount = 0 To UBound(varText)
- If InStr(fld.Value, varText(intCount)) <> 0 Then
- If Not InArray(varTempFound, varText(intCount)) Then
- ReDim Preserve varTempFound(UBound(varTempFound) + 1)
- varTempFound(UBound(varTempFound)) = varText(intCount)
- End If
- End If
- Next
- Next fld
- End If
- .Close
- End With
- Set rs = Nothing
- End Function
- Private Function InArray(vArr As Variant, Value As Variant) As Boolean
- Dim v As Variant
- For Each v In vArr
- If v = Value Then
- InArray = True
- Exit For
- End If
- Next
- End Function