Quick Search Function

Scenario (Applies to MS Access)

I have a main form (frmSearch) with a subform (sfrmNamesList) by which its data source is a query (qrySearchNames). In a query, I added a Search field concatenating the fields that are included in the search (Search:[LastName] & ” ” & [FirstName] & ” ” & [Employee ID]). I set a criteria for the Search field as : Like “*” & [Forms!frmSearch!txtSearch] & “*”.

Task

Refresh the form that when the user enter a letter in the Search text box (txtSearch), the names of employees that match would display in the subform.

Approach

Create a module in VBA and place this code:

Public Function pfPositionCursor(ctl As Control, lngWhere As Long)
Select Case ctl.ControlType
Case AcControlType.acTextBox, AcControlType.acComboBox

ctl.SelStart = lngWhere
ctl.SelLength = 0

Case Else
’Do Nothing
End Select
End Function

Then, on the On Change Event of txtSearch, put this code:

Private Sub txtSearch_Change()
Me.Refresh
pfPositionCursor Me.txtSearch, Len(Me.txtSearch & “”)
End Sub