list box sort

I have a list box that uses a query for the rowsource. How would I change the 
sort order (on click of command button):
I thought of

  Dim strSQL as string

    strSQL = CurrentDb.QueryDefs("pqSearchList").SQL & _
        " ORDER BY NameLFMN"

And then assign strSQL as the new query def, but that get's into removing 
the ";" etc. Is there a simpler way?

Thanks for your time!!
Sam
0
Utf
3/21/2008 9:46:01 PM
access.formscoding 7493 articles. 0 followers. Follow

5 Replies
766 Views

Similar Articles

[PageSpeed] 55

Copy the SQL statement from pqSearchList into your code, and chop of the 
ORDER BY clause. Define a string to set up with the ORDER BY clause. Assign 
the result to the list box's RowSource:

    Dim strOrderBy As String
    Const strcStub = "SELECT Table1.ID, Table1.SomeField FROM Table1 "
    strOrderBy = "ORDER BY NameLFMN;"
    Me.List0.RowSource = strcStub & strOrderBy

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"smk23" <smk23@discussions.microsoft.com> wrote in message
news:6B609074-C828-4111-AACA-1EAB2E5CC7BC@microsoft.com...
>I have a list box that uses a query for the rowsource. How would I change 
>the
> sort order (on click of command button):
> I thought of
>
>  Dim strSQL as string
>
>    strSQL = CurrentDb.QueryDefs("pqSearchList").SQL & _
>        " ORDER BY NameLFMN"
>
> And then assign strSQL as the new query def, but that get's into removing
> the ";" etc. Is there a simpler way?
>
> Thanks for your time!!
> Sam 

0
Allen
3/21/2008 10:00:16 PM
Allen,
pqSearchList gets redefined according to what I'm searching for, so it 
wouldn't be practical to make a constant for every possibility. I guess I 
will have to trim the ";" at the end of the statement, but I don't like that 
because it's asking for trouble, i.e. an extra space after the ";" or similar.

"Allen Browne" wrote:

> Copy the SQL statement from pqSearchList into your code, and chop of the 
> ORDER BY clause. Define a string to set up with the ORDER BY clause. Assign 
> the result to the list box's RowSource:
> 
>     Dim strOrderBy As String
>     Const strcStub = "SELECT Table1.ID, Table1.SomeField FROM Table1 "
>     strOrderBy = "ORDER BY NameLFMN;"
>     Me.List0.RowSource = strcStub & strOrderBy
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "smk23" <smk23@discussions.microsoft.com> wrote in message
> news:6B609074-C828-4111-AACA-1EAB2E5CC7BC@microsoft.com...
> >I have a list box that uses a query for the rowsource. How would I change 
> >the
> > sort order (on click of command button):
> > I thought of
> >
> >  Dim strSQL as string
> >
> >    strSQL = CurrentDb.QueryDefs("pqSearchList").SQL & _
> >        " ORDER BY NameLFMN"
> >
> > And then assign strSQL as the new query def, but that get's into removing
> > the ";" etc. Is there a simpler way?
> >
> > Thanks for your time!!
> > Sam 
> 
> 
0
Utf
3/21/2008 10:29:01 PM
You can use InstrRev() to locate the ";" at the end, and also the "ORDER BY"
(Typically is is followed by a CR LF.)

Parsing a SQL statement is not a simple task. There may be uilities to do 
it, but there are actually lots of possiblities to handle, such as 
subqueries, UNION, action queries, crosstab, parameters, literals that 
contain reserved words, and so on.

If you are already modifying the query, then building the entire SQL 
statement as a string in code might be even easier.

Another possibility might be to leave the existing query intact:
    Me.List0.RowSource = "SELECT * FROM pgSearchList ORDER BY NameLFMN;"

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"smk23" <smk23@discussions.microsoft.com> wrote in message
news:AD3C2767-3A7D-48DB-8945-41CBEDAE2F84@microsoft.com...
> Allen,
> pqSearchList gets redefined according to what I'm searching for, so it
> wouldn't be practical to make a constant for every possibility. I guess I
> will have to trim the ";" at the end of the statement, but I don't like 
> that
> because it's asking for trouble, i.e. an extra space after the ";" or 
> similar.
>
> "Allen Browne" wrote:
>
>> Copy the SQL statement from pqSearchList into your code, and chop of the
>> ORDER BY clause. Define a string to set up with the ORDER BY clause. 
>> Assign
>> the result to the list box's RowSource:
>>
>>     Dim strOrderBy As String
>>     Const strcStub = "SELECT Table1.ID, Table1.SomeField FROM Table1 "
>>     strOrderBy = "ORDER BY NameLFMN;"
>>     Me.List0.RowSource = strcStub & strOrderBy
>>
>> "smk23" <smk23@discussions.microsoft.com> wrote in message
>> news:6B609074-C828-4111-AACA-1EAB2E5CC7BC@microsoft.com...
>> >I have a list box that uses a query for the rowsource. How would I 
>> >change
>> >the
>> > sort order (on click of command button):
>> > I thought of
>> >
>> >  Dim strSQL as string
>> >
>> >    strSQL = CurrentDb.QueryDefs("pqSearchList").SQL & _
>> >        " ORDER BY NameLFMN"
>> >
>> > And then assign strSQL as the new query def, but that get's into 
>> > removing
>> > the ";" etc. Is there a simpler way?
>> >
>> > Thanks for your time!!
>> > Sam 

0
Allen
3/21/2008 10:38:01 PM
If it was just the semi-colon, you could easily use the Replace function:

    strSQL = Replace(CurrentDb.QueryDefs("pqSearchList").SQL, ";", "") & _
        " ORDER BY NameLFMN"

but what are you going to do the next time, when the ORDER BY NameLFMN is in 
the SQL as well? If you're saying that you're saving the SQL in another 
query, and that pqSearchList is never changed, how's that any better than 
having the base SQL assigned to a constant, like Allen's suggesting?

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"smk23" <smk23@discussions.microsoft.com> wrote in message 
news:AD3C2767-3A7D-48DB-8945-41CBEDAE2F84@microsoft.com...
> Allen,
> pqSearchList gets redefined according to what I'm searching for, so it
> wouldn't be practical to make a constant for every possibility. I guess I
> will have to trim the ";" at the end of the statement, but I don't like 
> that
> because it's asking for trouble, i.e. an extra space after the ";" or 
> similar.
>
> "Allen Browne" wrote:
>
>> Copy the SQL statement from pqSearchList into your code, and chop of the
>> ORDER BY clause. Define a string to set up with the ORDER BY clause. 
>> Assign
>> the result to the list box's RowSource:
>>
>>     Dim strOrderBy As String
>>     Const strcStub = "SELECT Table1.ID, Table1.SomeField FROM Table1 "
>>     strOrderBy = "ORDER BY NameLFMN;"
>>     Me.List0.RowSource = strcStub & strOrderBy
>>
>> -- 
>> Allen Browne - Microsoft MVP.  Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "smk23" <smk23@discussions.microsoft.com> wrote in message
>> news:6B609074-C828-4111-AACA-1EAB2E5CC7BC@microsoft.com...
>> >I have a list box that uses a query for the rowsource. How would I 
>> >change
>> >the
>> > sort order (on click of command button):
>> > I thought of
>> >
>> >  Dim strSQL as string
>> >
>> >    strSQL = CurrentDb.QueryDefs("pqSearchList").SQL & _
>> >        " ORDER BY NameLFMN"
>> >
>> > And then assign strSQL as the new query def, but that get's into 
>> > removing
>> > the ";" etc. Is there a simpler way?
>> >
>> > Thanks for your time!!
>> > Sam
>>
>> 

0
Douglas
3/21/2008 10:41:28 PM
You're right, Doug. 
What I'm doing is: pqSearchList is a pass-through to a stored procedure 
which is just a SELECT statement. I can use pqSearchList as the source for 
another query, qrySearchListNameOrder, and just change the rowsource of the 
list box to that when I want that sort.

Thanks for the help to both!!
Sam

"Douglas J. Steele" wrote:

> If it was just the semi-colon, you could easily use the Replace function:
> 
>     strSQL = Replace(CurrentDb.QueryDefs("pqSearchList").SQL, ";", "") & _
>         " ORDER BY NameLFMN"
> 
> but what are you going to do the next time, when the ORDER BY NameLFMN is in 
> the SQL as well? If you're saying that you're saving the SQL in another 
> query, and that pqSearchList is never changed, how's that any better than 
> having the base SQL assigned to a constant, like Allen's suggesting?
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> "smk23" <smk23@discussions.microsoft.com> wrote in message 
> news:AD3C2767-3A7D-48DB-8945-41CBEDAE2F84@microsoft.com...
> > Allen,
> > pqSearchList gets redefined according to what I'm searching for, so it
> > wouldn't be practical to make a constant for every possibility. I guess I
> > will have to trim the ";" at the end of the statement, but I don't like 
> > that
> > because it's asking for trouble, i.e. an extra space after the ";" or 
> > similar.
> >
> > "Allen Browne" wrote:
> >
> >> Copy the SQL statement from pqSearchList into your code, and chop of the
> >> ORDER BY clause. Define a string to set up with the ORDER BY clause. 
> >> Assign
> >> the result to the list box's RowSource:
> >>
> >>     Dim strOrderBy As String
> >>     Const strcStub = "SELECT Table1.ID, Table1.SomeField FROM Table1 "
> >>     strOrderBy = "ORDER BY NameLFMN;"
> >>     Me.List0.RowSource = strcStub & strOrderBy
> >>
> >> -- 
> >> Allen Browne - Microsoft MVP.  Perth, Western Australia
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "smk23" <smk23@discussions.microsoft.com> wrote in message
> >> news:6B609074-C828-4111-AACA-1EAB2E5CC7BC@microsoft.com...
> >> >I have a list box that uses a query for the rowsource. How would I 
> >> >change
> >> >the
> >> > sort order (on click of command button):
> >> > I thought of
> >> >
> >> >  Dim strSQL as string
> >> >
> >> >    strSQL = CurrentDb.QueryDefs("pqSearchList").SQL & _
> >> >        " ORDER BY NameLFMN"
> >> >
> >> > And then assign strSQL as the new query def, but that get's into 
> >> > removing
> >> > the ";" etc. Is there a simpler way?
> >> >
> >> > Thanks for your time!!
> >> > Sam
> >>
> >> 
> 
> 
0
Utf
3/21/2008 11:13:01 PM
Reply:

Similar Artilces:

Distribution List Auto Respond
Hello all, Do any of you know of a way to have Exchange auto respond to messages sent to a distribution list? I do not want to turn on the Send Out-of-Office Messaegs to originator option on because all of the members will have their own OOF messages set up during our Holiday Closure (1 week). The desire is that when people send a message to our Help Desk DL during our closure they would receive a message in return stating that we are closed for the holidays. Thanks for any help with this. Mike Seattle Pacific University You may have to remove everyone from the DL except a temp user wi...

How do I unpin an item from the list?
In addition to Word 2007 refusing to save documents unless I change the name I now have an old version pinned to the list and it refuses to unpin. Why is this happening, and how can I correct it? I am using Win7 Professional. "Viv" <Viv@discussions.microsoft.com> wrote in message news:F370AAFF-607C-4816-AA0E-A53A944CB026@microsoft.com... > In addition to Word 2007 refusing to save documents unless I change the > name > I now have an old version pinned to the list and it refuses to unpin. Why > is > this happening, and how can I correct it? &g...

Smart List Builder
We just bought Smartlist Builder. Are there any good resources out there for using it? Thanks! There are a few sample SmartLists from Great Plains. They are located in the document library on customer source. -- www.fmtconsultants.com "Wes" wrote: > We just bought Smartlist Builder. Are there any good resources out there for > using it? > > Thanks! > > > ...

Restricting input box entries to integers
Dear Experts: below macro applies a user-defined paragraph style to rows using an input box. The macro is running fine. But the input box also allows for entries such as 7,2 (comma because I live in Germany). How do I have to re-write the code to only allow integers as input box entries? Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub Tbl_BodyStyle() Dim oRng As Word.Range Dim oTbl As Word.Table Dim AskRowNumber As String Dim blnAsk As Boolean If Not Selection.Information(wdWithInTable) Then MsgBox "Please place the cursor...

Can Drop Down Boxes jump to the answer as you type?
If I create a form with VBA, to create a drop down box to have a list to pick an answer for to fill in a cell in a worksheet, it will jump to the answer in the list as you type. Can a Data|Validation|List do the same thing? If not, is there a way to get that functionality in Excel? Data Validation doesn't support autocomplete. If you can use programming, there are instructions here for adding a combobox from which you can select one of the values from the data validation list. In the combobox, you can enable autocomplete: http://www.contextures.com/xlDataVal11.html rrucksda...

Self-Made Combo Boxes not working correctly...
Okay all.. Here's my deal... I have tried and tried and tried to get these to work... I'm going to post what I have done, and what I am trying to do, and see if someone can help me figure crap out... tblBuilding - PK is BuildingIDNum - Autonumber tblBuildingZones - FK is BuildingIDNum via SELECT DISTINCTROW Building.BuildingIDNum, Building.Name FROM Building ORDER BY Building.BuildingIDNum; cboBuilding - This has Building 1, Building 2, etc. The field displaying this info is "Name" in tblBuilding. cboBuildingZones - This has different "zones" for Building 1,...

Need help with Combo Box?
I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. Sure...

Do I need DSClient to run Exchange 5.5 on an NT4.0 box in Windows 2003 Native Mode ADS?
Good Evening, I am in the process of migrating my WinNT4.0 domain and Exchange 5.5 Org to Windows 2003 ADS/Exchange 2003. I know best practice is to change the domain to Native mode, but how does this affect my NT4.0 server running 5.5? Do I need to simply load the DSClient onto the server? I should also note that we did an inplace upgrade from NT 4.0. This is a single domain environment. As long as you no longer have NT 4.0 BDCs you should be able to move to native mode. Your NT 4.0 server running E55 will not be affected (unless of course it is also a BDC). It is a good idea to...

Filtered list
Hi, I hope that some one can help! I want to take a certain range of cells in the file "HCP_2005_upgrade" and filter so as to select all the cells that are not empty. Then select all the rows for these cells and copy them to a new workbook "W_V" in "sheet2". I have the following code. Three problems: 1- When copying to the new workbook I did not have the same column width. What should I do in order to have the same column width? 2- What code do I need to add, and where, so as to let the macro automatically find the last r...

outlook contact list problem
Hello, I've been using Outlook for quite some time and it worked fine. My set-up: At work an english Outlook 2002 At home a german Outlook 2000 To sync I use an IPAQ PPC with Active Sync 3.7 I've now installed bluetooth support and sync even with my SE mobile phone with XTND Before the contact list was on both computers shown as "first last" i.e. Tom Smith, but now suddenly it shows as "Smith, Tom" I checked the settings on both outlooks and it says: "first last" both in - Tools>Options>Contact options> - Tools > E-mail accounts &g...

master list for auto complete data?
Is it possible to have a master list, in my case it's of names, in a workbook that excel will use for auto complete data for that workbook? Thanks Hi Tangy Go to "Tools>Auto Correct Options", select the "Auto Correct" tab and enter the abbreviated items and full names for those items you want Excel to replace. Ensure the relevant boxes are ticked. -- XL2002 Regards William willwest22@yahoo.com "Tangy" <t a n g y@rogers.com> wrote in message news:_OqdnbKPa7IShELcRVn-jQ@rogers.com... | Is it possible to have a master list, in my case it'...

List<string> as class property ?
Hello, is it possible to have a List<whatever> as a property of a class or must you use indexers? If so, please show me an example of the syntax. Thanks. G GiJeet wrote: > Hello, is it possible to have a List<whatever> as a property of a > class or must you use indexers? If so, please show me an example of > the syntax. Thanks. It is possible to have a property, or any number of properties, of type List<whatever> in a class. public class Foo { public List<string> List1 { ... } public List<FileInfo> List2 { ... } } .......

Creating a list of info in A1 cell in multiple worksheets
How can I create a list of the info/contents in the A1 cell in multiple worksheets? I am trying to create a summary worksheet of the data from 90 worksheets and would like to be able to list the headers in the A1 cell vertically. 'Right click on the summary sheet tab, and paste in this macro. 'Edit where appropriate: Sub CreateSummary() 'Starting row for summary: i = 1 For Each Sheet In ThisWorkbook.Sheets If Sheet.Name <> Me.Name Then 'Control What column to place data in Me.Cells(i, "A").Value = Sheet.Range("A1").Value i = i + 1...

Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between Sort order doesn't matter actually. Data is coming by the use of simple cell reference of "another sheet -- Prais ----------------------------------------------------------------------- Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27144 Hi you may use the following addin to filter out blank cells: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany Praise wrote: &g...

Using skins in a multiline edit box
Hi, I have a dialog based application, in which I have a multi-line edit control. In the edit control, I would like to have a background image, say some .bmp file. Some status messages are to be displayed in the edit control, based on certain user actions in the dialog. How do I go about this? Thanks, Sucharit you can do it, i think, if you were to create a class which inherited from the edit control, then in the override the OnDraw()/OnPaint() event, where you first get the rect, and draw your bitmap resource, then call the parent event method to do the rest... I don't know if t...

Sorting messages by sender
I've recently changed from using Outlook 2003 on an old Celeron PC running Win 2k, to a new P4 machine with XP. On the old set-up, I could click on the 'From' column header to sort messages in a mail folder by sender, then start typing the name of the sender to quickly take me to the messages from that person. This was very useful and I used this feature regularly, although it only seemed to work for the first three characters of the name, the fourth character entered would then take the cursor to the first instance of a name beginning with this letter. This was fine as the firs...

Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width box, yet using the mouse, I am able to adjust the width. When opening the text box, I can delete the number that appears, but am unable to even reenter that one after it is deleted. I must tell you that I'm a new user who's working through numerous tutorials. Thanks, Hi do you get an error message or what happens exactly -- Regards Frank Kabel Frankfurt, Germany rly2rys wrote: > Does anyone know why I am unable to enter a number into Excel's > column width box, yet using the mouse, I am ...

Why do sheet tabs keep disappearing? Box in Tools is checked.
When I open a new or existing Excel file, the sheet tabs at the bottom may or may not show up. I have repeatedly gone to Tools, Options, View and made sure the sheet tabs box is checked, but still no tabs. How do I get them back? I can't get from one page of a workbook to another. This is in Offfice 2003. Hi Arlie, Try Tools/Options/General In the Sheets in new workbook box, check and see how many sheets are set to appear when opening a new workbook. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://w...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

2 existing spreadsheets show 1st line rather than header row to sort ...
(XL2003) This is odd, I haven't seen this ever before. I was working on spreadsheets yesterday for a mail merge. Two had headers rows. I cleaned up both documents, put print area and then tried to sort. In both cases, what came up in the sort was the first row below the header where one always sees the actual titles of the header row when one selects the Header Row radio button under "My List Has" in the "Sort" box. I fiddled and fiddled a bit and managed to get one of the workbooks to display the sort properly by the header row titles - without figuring out what ...

Check Boxes #6
Is there an "easy" way to add check boxes to a worksheet? As it stands, I have to manually attach it to the underlying cell, and adjust the size. I would like to add several hundred checkboxes. Am I doing somethig wrong, or is it just that akward and labor intensive in Excel? Hi Adam, Try: '=============>> Public Sub Tester01() Dim SH As Worksheet Dim rng As Range Dim rCell As Range Set SH = ActiveSheet '<<==== CHANGE Set rng = SH.Range("A1:A100") '<<==== CHANGE Application.ScreenUp...

Synchronize combo boxes
I am trying to sync to combo boxes on a form. It is for plant names which consist of Genus + Species + Subspecies, basically. I have all my data in a single table and I want only applicable species to show up for a particular Genus, for example: Abies concolor Abies glauca Abies alba Quercus alba Quercus macrocarpa Any genus can have many species, some of those species names can exist within multiple Genus names, I have made to seperate tables with just Genus and just Species names, I want them to link in the combo boxes, any ideas? Previously responded to... Regards ...

Random List from Query
For accrediation purposes we have to audit a random 10% of our cases each quarter I was wondering if there is a way to run a query or report that would let me show a random 10% of cases for a specific provider. ...

List of items with commissions
Is there a report that will list items that have a commission set on them? I want to be able to email our sales reps a list, but haven't found a good way to do it. The report I'd like, but don't know how to create would have department, category, item, price, and commission amount. Thanks for any help, -CP Hi there, Retail Analytics from Professional Advantage will enable you to create that report. Basically anything that is captured in RMS can be reported on in Retail Analytics. If you would like to take a look at the product, a quick movie is available at http://www.p...

Conditional Formatting for Image box on Continuous Forms
Hello again, This problem is driving me crazy. I am a car enthusiast and love taking photos of classic cars. I have a table which describes the make and model in a series of fields. There are several fields that contain a reference to photos of those cars. Ie: the fields are called "Front" "Side" "Rear" "Angle". My form has four image boxes to display the images stored in these fields. But when I open the form all the records show the images of the first car only. I asked about this a few days ago and was advised to check out http://www.lebans.co...