Access Combo Box search allowing seacr on multiple fields

[L Balogh]

Question:
In a table the first field is the Customer number and the
second field is the Customer name. I want to create a
single combo box so it will accept both the customer
number or the name and search accordingly.
Say I first type in a number, the box will recognise to
use the Number field for search, if I type an alpha
character it will recognise it and search on the name
field. I'm experienced with VB coding. The AVB code looks
the same for both type of combo box, the only difference
is in the 'Colunm width' if you search by nuber usually
the first column and the column length is set to zero.

[Allen Browne]

Well, I supppose you could set the RowSource of the combo to a UNION query
that combines the customer number and name into the visible column, but that
doesn't sound like a great interface to me.

Would it not be better to use two unbound controls: a combo with the names,
and a text box for the customer number? The AfterUpdate event procedure of
each would FindFirst in the form's RecordsetClone, and I think it's clearer
for the user to understand.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - [url]http://allenbrowne.com/tips.html[/url]
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

"L Balogh" <lst@bigpond.net.au> wrote in message
news:061101c33ee2$15945700$a301280a@phx.gbl...

> Question:
> In a table the first field is the Customer number and the
> second field is the Customer name. I want to create a
> single combo box so it will accept both the customer
> number or the name and search accordingly.
> Say I first type in a number, the box will recognise to
> use the Number field for search, if I type an alpha
> character it will recognise it and search on the name
> field. I'm experienced with VB coding. The AVB code looks
> the same for both type of combo box, the only difference
> is in the 'Colunm width' if you search by nuber usually
> the first column and the column length is set to zero.

[Albert D. Kallal]

I would not use a combo if your users are going to be able to search for
more then one type of value.

By the way, I OFTEN also make my search boxes intelligent, and search on
more then one value. (say, LastName, Companies, and invoice number).

If the users types in a invoice number, then I search by number.

Don't torture your users with a combo box for search. I Mean, after 100
entries, a combo box is way too much.

Just build a normal text box, and in the after update event of the field,
stuff the sql results into a sub form, or a list box.

The code to do this can look like:

If IsNumeric(txtSLastName) = True Then
' number for last name...lets assume this is a invoice number
'
myconds = "InvoiceNumber = " & txtSLastName
Else
myconds = "(LastName like " & quS(txtSLastName) & ") "
end if

MySql = CurrentDb.QueryDefs(strSearchQuery).SQL

' get rid of the ";"

MySql = Left(MySql, InStr(MySql, ";") - 1)

MySql = MySql & " where " & myconds
MySql = MySql & myorder

Me.tblMainClient_subform.Form.RecordSource = MySql

You can see a screen shot of the above in action at:

[url]http://www.attcanada.net/~kallal.msn/Search/index.html[/url]

In fact, here is another one with drill down:
[url]http://www.attcanada.net/~kallal.msn/test/gs1.gif[/url]

It works the same as the first example. However, it drills down. On the left
side is a listbox, and on the right side is a sub-form. I used a sub-form on
the right side since I wanted to display the check box.

If you hit the up arrow a few times, then the details on the right side
change:
[url]http://www.attcanada.net/~kallal.msn/test/gs2.gif[/url]


Also, if you look closely in the first search example, I actually use a
combo box so the user can select stuff like email name, or phone number for
the search.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
[email]kallal@msn.com[/email]
[url]http://www.attcanada.net/~kallal.msn[/url]