Combo box problem - more than one with same last name

[BarbaraM]

I have a combo box to select students on a form.

It works great, as long as no one else has the same last name. For example,
I have many Johnsons. My Combo box looks up students by name, but the First
Johnson is always the one chosen.
It works if I have Student ID be the first column and select students based
on their ID number, but then I can't search in the combo box by last name. I
want users to be able to click the dropdown arrow, start typing the last name
to get to the approx. part of the list (3000 names) then select the correct
student in the combo box. What am I missing??

 

[Cheese_whiz]

Hiya Barbara,

Here's the way I do it. Others may have a better way. I use the following
in my row source for a combobox.

SELECT [ID], Trim([LastName] & ", " & [FirstName] & " " & [MiddleInitial])
AS FullName FROM Contacts ORDER BY [LastName], [FirstName], [MiddleInitial],
[ID];

That gives me names listed by lastname first (comma) firstname (space)
MiddleInitial. It also seems to be convenient in terms of parsing notInList
entries due to the comma giving me something to count on in terms of
someone's entry. I deny people if their entry doesn't have a comma, plus
when they start typing the dropdown list opens and they can clearly see the
format (last, first M).

You can add a "where" clause before the "order by" if you want to limit the
entries based on some other field.

Hope that's useful.
CW

 

[BarbaraM]

Thanks so much for your response.
I have the combo box showing the choices, however, now it does not select
that student in the form. Also, I can't select it by Alpha (it is sorted that
way, but you can't type the first few letters to get to that part of the
list).

Any more ideas?

Hiya Barbara,

Here's the way I do it. Others may have a better way. I use the following
in my row source for a combobox.

SELECT [ID], Trim([LastName] & ", " & [FirstName] & " " & [MiddleInitial])
AS FullName FROM Contacts ORDER BY [LastName], [FirstName], [MiddleInitial],
[ID];

That gives me names listed by lastname first (comma) firstname (space)
MiddleInitial. It also seems to be convenient in terms of parsing notInList
entries due to the comma giving me something to count on in terms of
someone's entry. I deny people if their entry doesn't have a comma, plus
when they start typing the dropdown list opens and they can clearly see the
format (last, first M).

You can add a "where" clause before the "order by" if you want to limit the
entries based on some other field.

Hope that's useful.
CW

I have a combo box to select students on a form.

It works great, as long as no one else has the same last name. For example,
I have many Johnsons. My Combo box looks up students by name, but the First
Johnson is always the one chosen.
It works if I have Student ID be the first column and select students based
on their ID number, but then I can't search in the combo box by last name. I
want users to be able to click the dropdown arrow, start typing the last name
to get to the approx. part of the list (3000 names) then select the correct
student in the combo box. What am I missing??

 

[John Spencer]

The first column should be the primary key on your table. You can hide
that column by setting column widths to 0 for the first column. Keep
the control bound to the primary key column.

The second column should concatenate the last name, first name middle
initial (and if there are still duplicates, add something else at the
end to make it unique.

Now when you type you should be able to enter something like

Spencer, J

And have the combobox jump to the first row that has Spencer, J in it.

Your SQL for the combobox row source might look something like

SELECT StudentID, LastName & ", " & FirstName & " : " & StudentID
FROM YourTable
ORDER BY LastName, FirstName, StudentID

The display will just show the second column
Spencer, John : 99823


---
John Spencer
Access MVP 2001-2005, 2007

Thanks so much for your response.
I have the combo box showing the choices, however, now it does not select
that student in the form. Also, I can't select it by Alpha (it is sorted that
way, but you can't type the first few letters to get to that part of the
list).

Any more ideas?

Hiya Barbara,

Here's the way I do it. Others may have a better way. I use the following
in my row source for a combobox.

SELECT [ID], Trim([LastName] & ", " & [FirstName] & " " & [MiddleInitial])
AS FullName FROM Contacts ORDER BY [LastName], [FirstName], [MiddleInitial],
[ID];

That gives me names listed by lastname first (comma) firstname (space)
MiddleInitial. It also seems to be convenient in terms of parsing notInList
entries due to the comma giving me something to count on in terms of
someone's entry. I deny people if their entry doesn't have a comma, plus
when they start typing the dropdown list opens and they can clearly see the
format (last, first M).

You can add a "where" clause before the "order by" if you want to limit the
entries based on some other field.

Hope that's useful.
CW

I have a combo box to select students on a form.

It works great, as long as no one else has the same last name. For example,
I have many Johnsons. My Combo box looks up students by name, but the First
Johnson is always the one chosen.
It works if I have Student ID be the first column and select students based
on their ID number, but then I can't search in the combo box by last name. I
want users to be able to click the dropdown arrow, start typing the last name
to get to the approx. part of the list (3000 names) then select the correct
student in the combo box. What am I missing??