Combobox won't sort alpha

[rpadams]

I have a combobox that opens when a form opens. It has a studentID (hidden) and an expression for LastName&Firstname as shown in this SQL.

I have it sorted alpha on the names. When the combo opens it is alpha but shows the name of the Lowest StudentID. I would like it to show the lowest ALPHA name. The underlying table (tblStudentBasicInfo) is also sorted alpha but this doesn't seem to make any difference.

SELECT tblStudentBasicInfo.StudentID, [LastName] & ", " & [FirstName] AS Expr1
FROM tblStudentBasicInfo
ORDER BY [LastName] & ", " & [FirstName];
& ", " & [FirstName] AS Expr1

 

[Nouba]

try this

SELECT
  StudentID
, [LastName] & ", " & [FirstName] AS Expr1
FROM tblStudentBasicInfo
ORDER BY
  [LastName]
, [FirstName]

 

[rpadams]

Not yet

Thanks for the reply. I understand where you're coming from and it sure looked like you had the solution. I tried it and I still get the same result. The StudentID is an autonumber. If I delete the lowest one on a trial basis, it goes to the next lowest etc.

I can set the combobox = a blank on open, but this still requires typing at least on letter to get at the top of the list.

 

[pono1]

RP,

Wild guess: Check your combo's column count property.

Regards,
Tim

 

[rpadams]

checked column count

The column count = 2. Width = 0" (hidden studentID);1"

Bound column 1

RowSource as shown in Nouba's suggestion above.

 

[pono1]

Sorry -- but I'm unable to re-create your situation using any of the SQL strings. This means the problem lies elsewhere, or I simply don't understand the problem.

Regards,
Tim

 

[rpadams]

Thanks -- looking further

I even pasted your attachment into my application and it didn't cure the problem. On its own, it works fine. I agree that maybe the problem lies elsewhere, perhaps on the Open or Load form event or the way I'm saving prior work even before shutdown.

I've requeried just about everything already and am now down to "I think I'll try this and see what happens." As you know, sometimes when you start making random changes without knowing the cause, the application gets much worse with other unexpected things happening. Looks like a long day ahead.

Thanks for all your efforts.

 

[Pat Hartman]

Are you using a stored querydef or an embedded SQL statement? Sometimes when I bind combo's to querydef's Access takes it upon itself to embed an SQL statement. I then change the querydef thinking that I am changing the rowsource for the combo and nothing happens. I eventually look at the rowsource and find out what happened.

 

[rpadams]

Thanks to everyone - here's the solution

Thanks to all for the suggestions. I'm posting my final solution so that it might help others as well.

What was apparently happening is that although the name selection combobox was being sorted alpha and was bound to the studentID field, the form had a recordsource of the underlying table which was not explicitly sorted even though I was displaying (and saving) it as if sorted by alpha on last name. What you SEE is not necessarily what you get!

The solution was to make a SQL query for the FORM's DATA (tblStudentBasicInfo) with all of the table fields (*) plus include the LastName and FirstName fields sorted ascending but not displayed.

SELECT tblStudentBasicInfo.*
FROM tblStudentBasicInfo
ORDER BY tblStudentBasicInfo.LastName, tblStudentBasicInfo.FirstName;

Apparently the form's data takes precedence over the combobox's data on the form. I guess what I take from this is that even when you have only a single table for your data, it is better to make it a query (or SQL) to make sure you know exactly what your data looks like.

 

[Pat Hartman]

A fine lesson to learn.