On Tue, 11 May 2010 14:47:05 -0500, Bruce Rodtnick wrote:

And that's what I'm TRYING to do...This is what I have now:

Dim strSQL As String

strSQL = Me.lstMailTo.RowSource

strSQL = strSQL & " " & ORDER By Personnel.Email

Me!lstMailTo.RowSource = strSQL
Me!lstMailTo.Requery

***********

strSQL is picking up the proper RowSource but when I try to add the ORDER BY
to the strSQL I'm getting a two ORDER BYs in my strSQL...the old one and the
new one...and so I get NOTHING in the ListBox lstMailTo

B

"John W. Vinson" wrote in message
news
On Tue, 11 May 2010 13:25:26 -0500, "Bruce Rodtnick"

wrote:

Me!lstMailTo.RowSource.OrderBy = Email
Me!lstMailTo.OrderByOn = True
Order By Email

A listbox (unlike a Form) does not HAVE an OrderBy property.

Do as Jeff suggested: use your code to change the listbox's RowSource
property
to a SQL string which sorts as you want it.
--

John W. Vinson [MVP]

Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

For a more complete list of reserved words, see:
http://www.allenbrowne.com/Ap****ueBadWord.html

I've taken the liberty to rename your "Name" field to "EmpName".

You also have some quotes and parenthesis in the wrong place.
Try it this way:
The SQL for the list box stays the same for all columns *Except* for
the OrderBy part?
Code each button you use to change the OrderBy like this:

Dim strSQL As String
strSQL = "Select [FirstName] & ' ' & [LastName] AS EmpName,
Personnel.Email, Personnel.[Voice Part] FROM Personnel
WHERE Personnel.Email Is Not Null And Personnel.Status = 'Active' "

Then add to each code the appropriate OrderBy clause.
If this is to order by the Email field add:
strSQL = strSQL & " OrderBy Personnel.Email;"

If this is to order by the [Voice Part] field, then use:
strSQL = strSQL & " OrderBy Personnel.[Voice Part];"

If this is to be used to order by the EmpName column:
strSQL = strSQL & " OrderBy Personnel.[LastName], Personnel.
[FirstName];"

No need to requery.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail