Searching for a person

  • Thread starter Thread starter Travis
  • Start date Start date
T

Travis

I've got the basic combo box set up which enables the user to start
typing a value and the combobox will attempt to guess the remainder of
the value and give a pulldown menu.

The basic SQL created by Access (plus an Order by which I added) is as
follows:

SELECT tblGroupNames.GroupID, tblGroupNames.[Group name]
FROM tblGroupNames
ORDER BY tblGroupNames.[Group name];

And on the update event it runs this sub, also automatically created by
Access:

Private Sub Combo72_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[GroupID] = '" & Me![Combo72] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The result being that if I start typing the name of a group, e.g.

Jack

It will then give me a choice of all the groups starting with Jack,
like Jack and Jill Smith, Jackson Family etc. When I select them, the
rest of the form moves to the appropriate entry.

This isn't entirely satisfactory because at times I can't remember the
name I assigned to the group, but might remember part of it. I'd want
to search on "Jill" for example.

I know that I need to call the Like operator in the SQL, but I've not
managed to get it to do what I want it to do.

Searches of the newsgroup and web didn't really help me much. Various
WHERE expressions I have tried either resulted in no visible effect,
resulted in a pop-up query prompt which sorta did what I wanted it to
but I just want to type in the combo-box, not the popup, or broke the
combo altogether and resulted in no values being available to pick.

Is the problem in the SQL or do I also have to modify the sub? If
so... how?

Travis
 
The auto-expand of the name in the combo can only work if you are typing the
start of the name, so presumably you want to use a text box. And it can
return multiple values, so the simplest solution might be to filter the form
to matching entries.

If I have understood you correctly, you will end up with something like
this:

Private Sub txtFindName_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.txtFindName) Then
If Me.Dirty Then Me.Dirty = False 'save first
strWhere = "[Group name] Like ""*" & Me.txtFindName & "*"""
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
 
Back
Top