Create a continuous forms view form based on your clients table, and in the
form header put an unbound text box named txtNames with a label such as:
'Enter a list of client names separated by commas, e.g. Mary, Brown'
In the text box's AfterUpdate event procedure out code like this:
Dim strSQL As String
Dim strNameList As String
Dim strName As String
strSQL = "SELECT * FROM [Clients] WHERE"
strNameList = Trim(Nz(Me.ActiveControl, ""))
Do While strNameList <> ""
If InStr(strNameList, ",") > 0 Then
strName = Trim(Left(strNameList, InStr(strNameList, ",") - 1))
strSQL = strSQL & " [ClientName] LIKE ""*" & strName & "*"" AND"
strNameList = Mid(strNameList, InStr(strNameList, ",") + 1)
Else
strName = Trim(strNameList)
strSQL = strSQL & " [ClientName] LIKE ""*" & strName & "*"" AND"
strNameList = ""
End If
Loop
If Right(strSQL, 4) = " AND" Then
strSQL = Left(strSQL, Len(strSQL) - 4)
Else
strSQL = Left(strSQL, Len(strSQL) - 6)
End If
Me.RecordSource = strSQL
Me.Requery
You'll be able to enter names cumulatively in the text box, separating each
by a comma. Pressing the Enter or Tab key after entering each name will
requery the form to restrict it to those names containing all the names
entered. Note that you'll probably get discrepancies, e.g. William would
return any client name Williamson as well as those named William. Deleting
the names from the text box will show all clients.
Ken Sheridan
Stafford, England
I have a big list of clients. Usuallly don´t know all name when I search,
sometimes is the first name, middle, last... The name is in a unique field
that I can´t change. In a form, I want to write one name, for example Mary,
and have a list with all names that have Mary. Then I write Brown and have a
list with all names with Mary and Brown, and so on till the person I reallly
want.
Thanks
--
.