If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I sort a listbox by field
I have a listbox that I've populaed using the row source
SELECT DISTINCTROW [FirstName] & " " & [LastName] AS Name, Personnel.Email, Personnel.[Voice Part], Personnel.LastName, Personnel.FirstName FROM Personnel WHERE (((Personnel.Email) Is Not Null) AND ((Personnel.Status)="Active")) ORDER BY Personnel.LastName, Personnel.FirstName; I have three columns and I would like to be able to sort by the columns. In other words, sort by Name, then later sort by Email or [Voice Part]. I've seen a bunch of examples and tried them but nothing seems to work. Bruce |
#2
|
|||
|
|||
How do I sort a listbox by field
Bruce
One approach is to add command controls "above" the listbox and use the Click event on each to set the Listbox's source to a different SQL statement (i.e., ORDER BY x -- whatever the command button is the 'label/header' for). Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Bruce Rodtnick" wrote in message ... I have a listbox that I've populaed using the row source SELECT DISTINCTROW [FirstName] & " " & [LastName] AS Name, Personnel.Email, Personnel.[Voice Part], Personnel.LastName, Personnel.FirstName FROM Personnel WHERE (((Personnel.Email) Is Not Null) AND ((Personnel.Status)="Active")) ORDER BY Personnel.LastName, Personnel.FirstName; I have three columns and I would like to be able to sort by the columns. In other words, sort by Name, then later sort by Email or [Voice Part]. I've seen a bunch of examples and tried them but nothing seems to work. Bruce |
#3
|
|||
|
|||
How do I sort a listbox by field
That's what I had in mind...but I'm not addressing it right...I made some
buttons with this code: Me!lstMailTo.RowSource.OrderBy = Email Me!lstMailTo.OrderByOn = True Order By Email 'QuickList.RowSource = TheConstantValue 'Me.QuickList.Requery Didn't work... Another : Dim strSQL As String 'set row source for list box strSQL = ("[FirstName] & " " & [LastName] AS Name, Personnel.Email, Personnel.[Voice Part]) _ FROM Personnel WHERE (((Personnel.Email) Is Not Null) And ((Personnel.Status) = "Active")) Me.lstMailTo.Recordset OrderBy = "Personnel.LastName", "Personnel.FirstName"; Me!lstMailTo.RowSource = strSQL Me!lstMailTo.Requery but I'm getting all kinds of errors. B "Jeff Boyce" wrote in message ... Bruce One approach is to add command controls "above" the listbox and use the Click event on each to set the Listbox's source to a different SQL statement (i.e., ORDER BY x -- whatever the command button is the 'label/header' for). Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Bruce Rodtnick" wrote in message ... I have a listbox that I've populaed using the row source SELECT DISTINCTROW [FirstName] & " " & [LastName] AS Name, Personnel.Email, Personnel.[Voice Part], Personnel.LastName, Personnel.FirstName FROM Personnel WHERE (((Personnel.Email) Is Not Null) AND ((Personnel.Status)="Active")) ORDER BY Personnel.LastName, Personnel.FirstName; I have three columns and I would like to be able to sort by the columns. In other words, sort by Name, then later sort by Email or [Voice Part]. I've seen a bunch of examples and tried them but nothing seems to work. Bruce |
#4
|
|||
|
|||
How do I sort a listbox by field
You provided the code you use to "fill" the listbox, ORDERed BY
[PersonLastName], [PersonFirstName]. Copy that SQL statement, but change the field you use in the ORDER BY clause to reflect the column on which you wish to sort. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Bruce Rodtnick" wrote in message ... That's what I had in mind...but I'm not addressing it right...I made some buttons with this code: Me!lstMailTo.RowSource.OrderBy = Email Me!lstMailTo.OrderByOn = True Order By Email 'QuickList.RowSource = TheConstantValue 'Me.QuickList.Requery Didn't work... Another : Dim strSQL As String 'set row source for list box strSQL = ("[FirstName] & " " & [LastName] AS Name, Personnel.Email, Personnel.[Voice Part]) _ FROM Personnel WHERE (((Personnel.Email) Is Not Null) And ((Personnel.Status) = "Active")) Me.lstMailTo.Recordset OrderBy = "Personnel.LastName", "Personnel.FirstName"; Me!lstMailTo.RowSource = strSQL Me!lstMailTo.Requery but I'm getting all kinds of errors. B "Jeff Boyce" wrote in message ... Bruce One approach is to add command controls "above" the listbox and use the Click event on each to set the Listbox's source to a different SQL statement (i.e., ORDER BY x -- whatever the command button is the 'label/header' for). Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Bruce Rodtnick" wrote in message ... I have a listbox that I've populaed using the row source SELECT DISTINCTROW [FirstName] & " " & [LastName] AS Name, Personnel.Email, Personnel.[Voice Part], Personnel.LastName, Personnel.FirstName FROM Personnel WHERE (((Personnel.Email) Is Not Null) AND ((Personnel.Status)="Active")) ORDER BY Personnel.LastName, Personnel.FirstName; I have three columns and I would like to be able to sort by the columns. In other words, sort by Name, then later sort by Email or [Voice Part]. I've seen a bunch of examples and tried them but nothing seems to work. Bruce |
#5
|
|||
|
|||
How do I sort a listbox by field
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] |
#6
|
|||
|
|||
How do I sort a listbox by field
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] |
#7
|
|||
|
|||
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 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 |
#8
|
|||
|
|||
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] |
#9
|
|||
|
|||
How do I sort a listbox by field
This all make sence...but I tried it and I get a blank screen. The strSQL
shows that everything is right. "SELECT [FirstName] & " " & [LastName] AS MbrName, Personnel.Email, Personnel.[Voice Part] FROM Personnel WHERE (((Personnel.Email) Is Not Null) AND ((Personnel.Status)="active")); ORDER BY Personnel.Email" Is it the semi-colon before the ORDER BY that does it? How do I get rid of that? B "John W. Vinson" wrote in message ... 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] |
#10
|
|||
|
|||
How do I sort a listbox by field
Yes. Get rid of the semi-colon (and make sure there's a space between the
closing parenthesis and the key word Order) -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bruce Rodtnick" wrote in message ... This all make sence...but I tried it and I get a blank screen. The strSQL shows that everything is right. "SELECT [FirstName] & " " & [LastName] AS MbrName, Personnel.Email, Personnel.[Voice Part] FROM Personnel WHERE (((Personnel.Email) Is Not Null) AND ((Personnel.Status)="active")); ORDER BY Personnel.Email" Is it the semi-colon before the ORDER BY that does it? How do I get rid of that? B "John W. Vinson" wrote in message ... 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] |
|
Thread Tools | |
Display Modes | |
|
|