Order By Combobox

[jsic1210]

Okay, I know I've done this before, but I can't remember how. I have a combobox with a row source of existing values of that field. One of those options is "None," which I want to show up as the last option. So the existing values are:

"Adam"
"None"
"Zak"

But I want:
"Adam"
"Zak"
"None"

My SQL statement gives me an error that says "ORDER BY clause...conflicts with DISTINCT." Here is my SQL statement:

SELECT DISTINCT tblOpenItems.Legal
FROM tblOpenItems
WHERE (((tblOpenItems.Legal) Is Not Null))
ORDER BY iif(tblOpenItems.Legal="None","ZZZ",tblOpenItems.Legal)

How do I take care of this?

 

[pr2-eugin]

Why not use VBA to manipulate the recordset and finally add the value "None" ?

 

[jsic1210]

I'm not good with recordsets, how would I do this?

 

[pr2-eugin]

Something like..

Private Sub Form_Current()
    Dim tmpRS As DAO.Recordset, iCtr As Long
    Me.yourComboName.RowSourceType = "Value List"
    Me.yourComboName.RowSource = ""
    Set tmpRS = CurrentDB.OpenRecordset "SELECT tblOpenItems.Legal FROM tblOpenItems WHERE (tblOpenItems.Legal Is Not Null) GROUP BY tblOpenItems.Legal;"
    Do While Not tmpRS.EOF
        Me.yourComboName.AddItem tmpRS.Fields(0)
    Loop
    Me.yourComboName.AddItem "None"
    Set tmpRS = Nothing
End Sub