How do I sort a listbox by field
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
The problem is that strSQL is *the whole thing* - it isn't a complex object
with an Order By property that you can replace; it's just a text string. VBA
won't have a clue what you mean by the ORDER By Personnel.Email text there;
that's SQL text, not valid VBA code.
I'd suggest using a saved query in your table, without *any* ORDER BY clause,
and use code like
strSQL = Currentdb.QueryDefs("lstMailToTemplateQuery").SQL
strSQL = strSQL & " ORDER BY Personnel.Email"
Me!lstMailTo.RowSource = strSQL
You won't need to requery it - setting its rowsource does the job.
--
John W. Vinson [MVP]
|