lookup records using a combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to look up records in a table by using a combo box. It works
great and pulls of the record as long as the look-up is unique. Unfortunately
I have 10 people with the name anderson and the look-up always defaults to
the first anderson. Is there a way to base the lookup on 2 columns, i.e,
firstname and lastname by using a combo box or select from any anderson on
the list?
 
Your table should have a primary key (typically an AutoNumber.)
If it does, you can use the primary key value to go to the right person.

In form design view, right-click the combo and choose Properties.
On the Data tab of the Properties box, click in the Row Source.
Click the Build button (...) beside this.
Access opens the query into the Customer table.
Drag 3 columns into the grid:
ID, lastname, firstname
Close the query designer, answering Yes.

Now set these other properties for the combo:
Bound Column 1
Column Count 3
Column Widths 0";1;1"
List Width 2.2"
Control Source (leave this blank.)

The unbound combo is now bound to the customer ID. It will show the other 2
columns when dropped down, and the code will take you to the correct
(hidden) ID when you select someone.

If the code you currently have does not navigate to the desired record,
there's some sample code here:
http://allenbrowne.com/ser-03.html
 
I would combine Last Name and First Name in a query to give Full Name (e.g.
Anderson, Greg, etc.) and base the combobox on that. You then can type
Anderson, G and probably get Greg Anderson unless you have a Gary Anderson.
Even if you type Ande in the combobox, the drop down arrow will bring up all
the Andersons in alphabetic order if you have sorted the query by Full Name.
I always use queries for comboboxes because you can sort them any way you
want. Not true of a table.
 
Back
Top