Access 2007 - combo box pull-down menus

[ml20090412]

I have a field called "ClientName" in one of the tables in my database. The data in this field is a combo box containing two fields from another table: one field is called "FirstName", the other "LastName". The fields are ordered "FirstName" then "LastName" in the combo box.

When I'm in datasheet view, if I go to the field "ClientName" and type in "John", the pull-down menu will move automatically to a record with the first name "John", as long as there is someone in the source field with this first name. However, if I try to type in a last name such as "Smith" -- even if there is someone in the source field with this surname -- Access gives me the error message,

"The data you entered isn't an item in the list"

The same occurs if I type in the full name "John Smith".

Is there a way to search quickly through any of the fields contained in a combo box pull-down menu? (Or by any combination of these fields?)

If it helps, here is the rowsource for the combobox:

SELECT [Basic client data].[ClientID], [Basic client data].[FirstName], [Basic client data].[LastName] FROM [Basic client data] ORDER BY [FirstName], [LastName], [ClientID];

Thanks!

 

[Trevor G]

Have you tried using wild characters or Like when looking for surnames?

*Smith

Like Smi*

Help will give you some basic suggestions on wild characters

 

[ml20090412]

Have you tried using wild characters or Like when looking for surnames?

*Smith

Like Smi*

Help will give you some basic suggestions on wild characters

I tried * and the Like command, but got the same error message in both cases: "The text you entered isn't an item in the list". The pull-down menu only seems to recognize the first field of the combo box (= FirstName).