Should I use a combo box?

[mles]

I am building a form that combines data from several tables. One table, Jobs, contains information about projects (like job #, budget, and so forth). That table is linked to another table, Contacts, that contains information about individual clients, via the ContactID #. I want to be able to type in the Contact's name on the form, and have the ContactID # stored in tblJobs.

The problem I am having is that the Contacts table has first and last names as separate fields. So when I create a combo box on the form with two columns for first and last name, I can't type in the last name to locate the correct Contact record. There are too many contacts to scroll through to find the right one. I can't use only the first name or the last name, because there are duplicates of each. What should I do? Would I be better off changing the structure of Contacts to have one field for both first and last name?

 

[BarkerD]

One solution for you would be to concatenate the first and last names in one field.

Fullname:[Lname] & " " & [Fname]
You would then be able to type the full name of the contact in your selection list. You could include either a comma, or a space to separate the names.

Regards
Duane Barker

 

[mles]

Forgive me if this is a dumb question... I'm pretty new at using Access.

Am I correct in thinking I should enter your above suggestion in the SQL view of the RowSource property of the combo box? The thing that currently reads like this:

SELECT tbl_Contacts.ContactID, tbl_Contacts.FirstName, tbl_Contacts.LastName
FROM tbl_Contacts
ORDER BY tbl_Contacts.FirstName;

Thanks for your help.

 

[BarkerD]

You can use the SQL view of the row source property, or , if you are fairly new at SQL, you can use the expression builder. By clicking on the ... beside the row source property, you will bring up the Query builder. Just remove the two name fields, and replace them with the new concatenated field.

The SQL entry would be:
SELECT tbl_Contacts.ContactID, tbl_Contacts.[FirstName] & ", " & tbl_Contacts.[LastName] AS FullName
FROM tbl_Contacts
ORDER BY tbl_Contacts.FirstName;

One thing you will have to do afterwards, is to change the column count property from 3 to 2. You may also want to resize the column widths.

Regards.
Duane Barker

[This message has been edited by BarkerD (edited 03-15-2000).]

 

[mles]

Excellent! It works like a charm! Thanks for your help. Now I definitely need to hit the books and learn SQL.

-Emily