Find Combo Box Almost Works All the Time

[Guest]

We have a students table [tblStudents] with approximately 4,500 records. On
frmStudents, we have a "Find" Combo Box that works 99% of the time. the Combo
Box uses the value of StudentID to find the student.

When two people have the same last name and a different first name, it will
only find the first person with that last name. For example, if there are 2
people with the following names:

Goldstein, Linda
Goldstein, Martin

Whenever we click on Goldstein, Martin, it displays Linda Goldstein's record
on the form. We can manually move to Martin Goldstein's record, but that is
somewhat annoying. We would rather use the Find Combo box to go directly to
his record.

Robert

 

[Jeff Boyce]

Robert

What is the SQL statement of the query your combo box uses to "fill" itself?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hello Jeff:

SELECT qrytblStudents.Student_LastName, qrytblStudents.Student_FirstName,
qrytblStudents.StudentID, qrytblStudents.Student_Email
FROM qrytblStudents
ORDER BY qrytblStudents.Student_LastName, qrytblStudents.Student_FirstName;

 

[Guest]

Hello Jeff:

I just realized the same problem happens on 2 different forms and each form
uses a different query. Here's the other query.

SELECT qrytblStudents.Student_LastName, qrytblStudents.Student_FirstName,
qrytblStudents.StudentID
FROM qrytblStudents;

Robert

 

[Jeff Boyce]

Robert

OK, next step...

In the properties of the combo box, which of the columns are you using as
the "bound" column?

Based on your description, I'm guessing you are using the first column.
Take a look at your query. The first column is the last name. So if you
are finding the record that corresponds to the row selected in the combo
box, you are finding the first record that matches the last name!

Instead, consider modifying your SQL statement/query to use the ID field as
the first and the bound column, then set its width to 0 to hide it.

And while you are at it, instead of returning two fields for the name (last
& first), consider creating a new field in your query, something like:
StudentName: [Student_LastName] & ", " & [Student_FirstName]

This is only one field wide and allows an easier way to get the correct row.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hello Jeff:

I just checked and both queries are using 1 as the bound column so it looks
as if your suggestion will do the trick. As soon as I have some free time
here at work, I'll modify the queries, but I'm confident you nailed it.

Thanks so much,
Robert