Combo Box lookup help

[David S. Calef]

I use combo boxes frequently, but always to look up a record from another
table.

Now I am using a table where I use a combo box for the indexed Name. I do
an Add Record command button. Then I start typing the Name in the combo
box.. If the Name doesn't exist, I add it to the new record just fine. But
if the record does exist, it doesn't fill out the rest of the form with the
data for that name.

What I want to do is go into the name box, start typing and either have it
jump to an existing name and record or start a new record for a new name.

Can someone outline how that is done.

Previously I have used a kludgey separate combo box to see if the name
exists and jump to if. If not, I then hit the add button which exists the
combo without jumping to a record, and then the new blank record pops up.
While it works, I have to think there is a better way.

 

[HSalim]

This was a reply to a similar question - adapt to your needs:

--------------------
Follow these steps:
1. Use a combo box
2. For the row source of the combobox use a query that joins the two
tables - such as
Select Table1.Id, table2.Description, table2.ExtendedDesc table
from table1 innerjoin table2 on table1.id = table2.id
(Although, it seems to me that you could just as well use
Select table2.id, table2.description...)

3. With combo boxes, you could display one value but store another in the
table - for example, you could store the ID (the bound column) but display
the description).
Assuming you are already displaying Description but want to display the
ExtendedDesc,

4. Add a text box to your form - (you can set the text box's enabled
property to false and locked to true, ensuring that the text box behaves
like a label - read only etc)

5. Bind the textbox's control source to the combobox eg: =
cboMyDropDown.Column(2)

Hope that helps

HS