Quick Search Function

by | Oct 16, 2011 | Access, Programming Notes

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

 

Filter an open form from another form

by | May 19, 2011 | Access, Programming Notes

Scenario (Applies to Access 2003, 2007 and 2010)
There are two open forms, frmEmployees and frmTransactions. frmEmployees and frmTransactions are bound to tblEmployees and tblTransactions. They have a common field which is EmployeeID.

Task
Filter a record from frmEmployee where EmployeeID correspond on the selected row in frmTransactions.

Approach
On the Click Event of TransactionDetail field in frmTransactions, put this code:

Private Sub TransactionDetail_Click()
’ Find the record that matches the control.
Dim rs As Object

Set rs = Forms!frmEmployees.Recordset.Clone
rs.FindFirst “[ID] = ” & Nz(Me.EmployeeID, 0)
If Not rs.EOF Then Forms!frmEmployees.Bookmark = rs.Bookmark

End Sub