Find text string in field of sorted records on Tab Control

[Guest]

I use buttons (1 for 'A', 1 for 'B'...) at bottom of a form on a Tab Control
to move to the range of sorted records whose field begins with the letter. I
want to add a text field for added granularity that operates similarly ("ig"
goes to 'ignore' instead of "i" going to IBM, first).

I use a macro "find.a" to go to the "A's" (Find What: a, Match: Start of
Field...)
I assume I can do the same thing with the text field entry and a macro
"find.text" by using an expression (Find What: =name on form, Match: Start
of Field...)
If this will work, I expect I'm having trouble specifying the 'fully
qualified name' of the text field on the form on a page of the tab control.

 

[Guest]

Doing it in code would be easier than a macro I think (if its possible at all
in a macro), and its simple to do. Lets say that the field in the table on
which you are searching is called LastName and the text box is in the form
(whether this is the main form or a subform) bound to the table or a query
based on it, then add an unbound text box to the form and in its Change event
procedure put:

Dim rst As Object
Dim ctrl As Control
Dim strText As String
Dim strCriteria As String

Set ctrl = Me.ActiveControl
strText = ctrl.Text

If Len(strText) > 0 Then
strCriteria = "LastName Like """ & strText & "*"""

Set rst = Me.Recordset.Clone
rst.FindFirst strCriteria

If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
ctrl.SelStart = Len(strText)
ctrl.SelLength = 0
Else
MsgBox "No match.", vbExclamation, "Warning"
End If
End If

As each character is typed into the unbound text box control the form will
go to the first matching record. Remember that if a control name includes
spaces or other special characters the name must be wrapped in brackets, e.g.
[Last Name].

If by 'form on a Tab Control' you mean a subform, and the text box is on the
parent form, either on the page of the tab or outside it, then to reference
the subform you do so via the Form property of the subform control, i.e. the
control in the parent form which houses the subform (not the tab control; the
fact that the subform control is on a page of a tab control should not be
relevant as its controls are members of the form's Controls collection as
well), so the code would just need amending slightly:

Dim rst As Object
Dim frm As Form
Dim ctrl As Control
Dim strText As String
Dim strCriteria As String

Set frm = Me.sfrContacts.Form
Set ctrl = Me.ActiveControl
strText = ctrl.Text

If Len(strText) > 0 Then
strCriteria = "LastName Like """ & strText & "*"""

Set rst = frm.Recordset.Clone
rst.FindFirst strCriteria

If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
ctrl.SelStart = Len(strText)
ctrl.SelLength = 0
Else
MsgBox "No match.", vbExclamation, "Warning"
End If
End If

where sfrContacts is the name of the subform control on the page of the tab.

Ken Sheridan
Stafford, England