Sort with Listbox Selection On Top

Hi,

I have a listbox. When I select an item in the listbox, I would like
subform1 to sort column1 with the selection from listbox on top.

For example, when I select Pennsylvania from the listbox- it will
update subform1 to, while still retaining all of the records, but all
records with "Pennsylvania" listed in column1 on top.

Is this possible? Thanks
0
shmoussa
3/29/2010 5:59:41 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
1315 Views

Similar Articles

[PageSpeed] 23

shmoussa wrote:
>I have a listbox. When I select an item in the listbox, I would like
>subform1 to sort column1 with the selection from listbox on top.
>
>For example, when I select Pennsylvania from the listbox- it will
>update subform1 to, while still retaining all of the records, but all
>records with "Pennsylvania" listed in column1 on top.
>

That's not a sort problem.  Presumably the records are
already sorted by state so all you need to do is navigate to
the first record with the selected state:

With Me.subform1.Form.Recordset
	.FindFirst "statefield = '" & Me.thelistbox & "' "
	If .Nomatch Then Beep
End With

-- 
Marsh
MVP [MS Access]
0
Marshall
3/29/2010 7:32:30 PM
On Mar 29, 3:32=A0pm, Marshall Barton <marshbar...@wowway.com> wrote:
> shmoussa wrote:
> >I have a listbox. When I select an item in the listbox, I would like
> >subform1 to sort column1 with the selection from listbox on top.
>
> >For example, when I select Pennsylvania from the listbox- it will
> >update subform1 to, while still retaining all of the records, but all
> >records with "Pennsylvania" listed in column1 on top.
>
> That's not a sort problem. =A0Presumably the records are
> already sorted by state so all you need to do is navigate to
> the first record with the selected state:
>
> With Me.subform1.Form.Recordset
> =A0 =A0 =A0 =A0 .FindFirst "statefield =3D '" & Me.thelistbox & "' "
> =A0 =A0 =A0 =A0 If .Nomatch Then Beep
> End With
>
> --
> Marsh
> MVP [MS Access]


Thanks for the reply. Can you tell me where to put this? Does it go in
the SQL for the query that generates the subform? Or in VBA for which
event?

Thanks again
0
shmoussa
3/29/2010 7:43:25 PM
shmoussa wrote:

>On Mar 29, 3:32�pm, Marshall Barton <marshbar...@wowway.com> wrote:
>> shmoussa wrote:
>> >I have a listbox. When I select an item in the listbox, I would like
>> >subform1 to sort column1 with the selection from listbox on top.
>>
>> >For example, when I select Pennsylvania from the listbox- it will
>> >update subform1 to, while still retaining all of the records, but all
>> >records with "Pennsylvania" listed in column1 on top.
>>
>> That's not a sort problem. �Presumably the records are
>> already sorted by state so all you need to do is navigate to
>> the first record with the selected state:
>>
>> With Me.subform1.Form.Recordset
>> � � � � .FindFirst "statefield = '" & Me.thelistbox & "' "
>> � � � � If .Nomatch Then Beep
>> End With
>>
>
>Thanks for the reply. Can you tell me where to put this? Does it go in
>the SQL for the query that generates the subform? Or in VBA for which
>event?


VBA code never, ever goes in an SQL statement.  VBA code can
ONLY go in a module,  In your case I suggest that you put it
in a button's click event (the button is for users to say
the list box selection is made and they are ready to do the
search).  If you want to do the search immediately when a
row is selected in the list box, then you can put the code
in the list box's AfterUpdate event procedure.

-- 
Marsh
MVP [MS Access]
0
Marshall
3/29/2010 8:41:53 PM
high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD 
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price?  China 
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can 
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are 
the best brand replica goods whih are look the same as the original goods. 
excellent quality and steady supply for them. we have been marketed in Europe 
and American for 3 year. all the goods we offer are AAA quality.  our soccer 
jersey are Thailand style. If any goods you buy from my company have problem, 
we will refund or resend them again. Most of ourProducts have no minimum 
order requirements,soyou can shop retail goods at wholesale prices. if you 
can buy more than 300usd. We offer free shipping. The more you buy the more 
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping.  7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com
0
Utf
3/31/2010 8:15:02 PM
Reply:

Similar Artilces:

View pareto chart by sorting the max number on the bottom
Is it possible to format the pareto chart by sorting the maximun number show in the bottom of each bar and ignore sort by Legend item? The order is determined by the Row Source of the chart. -- Duane Hookom Microsoft Access MVP "sq75222" wrote: > Is it possible to format the pareto chart by sorting the maximun number show > in the bottom of each bar and ignore sort by Legend item? > > ...

Sort Order on Order Fulfillment Window
In the sales order fulfillment window you can sort the lines items by Order Entered or Site/Bin/Item. Does the Site/Bin/Item only sort by Bin if Multi-bin is used? I am not using multi-bin but have created bin for each item in each site on the Quantities/Sites window. thanks. ...

Sales order processing item inquiry windows
Hi, Since I have updated to version 10 (from version 9.0) the document are not showing in any specific order. In version 9 they were sorted by document date. For example if you choose "By item number" then include only invoice and then choose history. You will see that the document are not sorted at all. Any ideas or suggestions? Thanks Andre, Everything scrolling window has a sort order - just maybe not what you expect. But you can always see it by turning on your dexsql.log and seeing the query generated. In this case with your same options marked, i get: SELECT TOP...

Can I set up a calendar lookup for date selection
Is it possible to lookup a calendar for date selection on a cell. I cannot find any reference to it in the help. try this url http://www.fontstuff.com/vba/vbatut07.htm ================= "Tangoxray" <Tangoxray@discussions.microsoft.com> wrote in message news:72D4DB63-1D7F-477A-93C7-2F767A207B26@microsoft.com... > Is it possible to lookup a calendar for date selection on a cell. I cannot > find any reference to it in the help. ...

>> Calendar Control drag to select range
Hi, using MS Access 2003, is it possible to allow a user to drag to select a range of dates and, if 'yes', how do you store the selected date range? Many thanks, Jonathan Not that I know of. Storing a range of dates is quite simple though. You store the start date and the end date and use a calculation to show all the dates between. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jonathan" <Jonathan@discussions.microsoft.com> wrote in message news:4F22299F-3765-40D2-AA03-67FB42FFC07A@microsoft.com... > ...

Sort House Numbers
How can I get excel to sort house numbers as I would like, i.e 1,2,2a,3,4,5,5a,5b, et. etc. So far I've ailed miserably. Any help out there? TIA Mike G Hi Mike, You will have to separate the digits into a separate cell. Extraction of a Group of Digits and Dashes, from postings by Harlan Grove Extract the First Set of Digits (#DigitsFirstID) http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm#DigitsFirstID -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvp...

Insert Blank Line and Sort
------------liX6Lxsnq7Ovs6LAASpU5y Content-Type: text/plain; charset=iso-8859-15; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Within a worksheet I have an example of data below consisting of four columns. I need to put a blank line in between the Racecourse (where they change) and after do a sort on Rating (high to low). Is it possible? I could do it with a macro, but am unable to fathom out how to deal with a differing number of horses at each racecourse. Time Racecourse Horse Rating 04:30:00 SANDOWN Dare To Dance 0.261 04:30:00 SANDOWN Garud 0.153 04:30:00 SANDOWN Wes...

Sorting Issue (Mixde Fields)
I have an issue where I need to sort a worksheet based on a field (Bin Location)that is sorting as seen below... 1A1 1A20 1A21 1A22 1A3 1A4 How can I get Excel to sort it the following way... 1A1 1A3 1A4 1A20 1A21 1A22 Please help.... I need to get this figured out today. I appreciate any responses. Thanks, Jonathan G. One way: add a column showing the length of the column you want to sort, then sort using the new column then the "real" column. "Jonathan G." wrote: > I have an issue where I need to sort a worksheet based on a field (Bin > Location)that is s...

Sorting Stacked Columns
I am trying to sort the columns so that the largest value is on th bottom each year. This would change the order of the series over eac year presumably. I have not figured out a way to do this, and fear i may be VBA related. Any ideas? Thanks. I attached the file for viewing Attachment filename: testbook_stackedcols.xls Download attachment: http://www.excelforum.com/attachment.php?postid=61644 -- Message posted from http://www.ExcelForum.com ...

move selection after enter into cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Upon entering data into a cell, I do not want to move to another cell when I hit Return. In the older version of Excel I used to be able to go into Tools/Options/Edit and uncheck Move Selection after Enter. How can I accomplish this in the 2008 version? thanks Aha - you've revealed yourself as a Windows user :-) Mac OS has never had Options, it uses Preferences instead. Go to Excel> Preferences> Edit where you can specify Up, Down, Left, Right from the list or clear the check to not have the se...

Option to select printer B4 printing Payables Checks
Client recently upgraded from 7.5 to 9.0 and they lost the option to select a printer after clicking print. From print payables checks In 7.5 when they would click on print, a window would pop up and give them the option to select a printer to send their checks to. Now it automatically goes to their defaulted printer. How do they set up the option in 9.0? Go to the Named Printers settings, and look at the checkbox to suppress the printer dialog box. This should solve your problem Best regards Mike "TROOTBMI" <TROOTBMI@discussions.microsoft.com> wrote in message news:...

Large List Sorting
Hello, All! Have a problem. I'm developing an application which reading a large database file (DBF) and displaying it in the list control. Because of the size of the file (>15 MB, later it will be increased) I'm using a virtual list (owner data). But now I need to sort data in the list. AFAIK to perfirm this I should read the whole file. In this case my program starts several minutes!!! It doesn't have to be like this [(C) Pink Floyd]... Is there any methods to load a large amount of data as quickly as possible? Or I just should humble, 'cause sorting of large amount of d...

formatting selected characters or numbers in each cell within a range of cells
I am sending this to several Excel newsgroups, because I am not sure which one if the most appropriate. I apologize in advance for the multiple postings. I have MS Excel 2000 (version 9.0.3821 SR-1). How can I format only a selected character or number within each cell in a specific range of cells? For instance if I have a cell containing �1234234�, how can I format it so that only the 2�s are bold or are colored? Obviously, I have a range of cells, and I want to format all the 2�s occurring in any of the cells as either bold or colored. Conditional formatting does not seem to all...

How to sort in Excel with headers link to all body parts
I have a table that have subheaders and each subheader with rows of items below them. I wanted to sort all subitems from all subheaders according to value (in one column); and have each subitem link to the subheader as label for that particular item. I guess what I need to do is link a cluster of items to it subheader; so when I sort each item will include the subheader as label to show what subheader it came from. ...

Select Records that fall in an external set of ranges (subquery)
HiI have not done subqueries before so I am a bit confused. I have twotables1 - Phone Numbers - (converted to be numeric) (with other fields forlater) and2 - Exchange codes - multiple entries for code with 3 fields -Exchange name (duplication) - Start Range and End Range.I want to return a list of phone numbers that fall into the range fora certain exchange.For example - MRSPOO has 4 entries in the Exchange Table with 4distinct ranges. 200-299, 307-788, 997-1102 and 2036-6698. I would like to be able to return all the phone numbers for a singleexchange code that I would select (using a query...

Cannot access Outlook on my desk top from my laptop
I have a Roadrunner address with Outlook as my primary, on my desktop. But I cannot get to my Outlook using a laptop. Note: I can get to Hotmail, my secondary, using the laptop or another computer. Obivously, the primary is important. Can you detail the steps you're taking? "DCW" <DCW@discussions.microsoft.com> wrote in message news:EF21BBB7-2104-4C27-813F-8073234259F7@microsoft.com... > I have a Roadrunner address with Outlook as my primary, on my desktop. But I > cannot get to my Outlook using a laptop. Note: I can get to Hotmail, my > secondar...

Report names in listbox
I recently upgraded to Access 2007 and am having some trouble with the changes. I have a form with a listbox in which I would like to display the names of all the reports in the database. I've tried using the following as the rowsource for the listbox, but in form view nothing is displayed. Does anyone know how to do this? TIA, Ken Warthen kenwarthen@gmail.com "SELECT MSysObjects.Name FROM MSysObjects WHERE (((Left([Name],1))<"~") AND ((MSysObjects.Type)=-32764)) ORDER BY MSysObjects.Name;" I think you made a slight typo. Try (((Left([Name],1))<>&quo...

Sorting Notes
I use the Outlook Notes program quite a bit. But, there are two problems I have with it: 1. It sorts most recent to oldest -- is there any way to change the sort order to alphabetical? 2. I'd like the default "note" size to be a little larger? Is this possible? Thanks I can answer part of this one. To make the note a little larger - Tools, Options, Note Options, Size. Judy Freed <EHPorter> wrote in message news:414a4d0d$0$55835$a1866201@newsreader.visi.com... > I use the Outlook Notes program quite a bit. But, there are two problems I > have wit...

Highlight a selected record
I am trying to highlight a selected record of a continuous form. Conditional Formatting is not working because it highlights individual fields. If Conditional Formatting would highlight the entire row, it would be perfect. I created a txtBackground text box to size of all my fields, sent it the back—behind the fields. Use the txtBackground as the control, but when it has focus it covers the data. Below is the code I used: Sub HighlightControl(ctl As Control) On Error Resume Next ctl.BackColor = 65535 End Sub Sub UnhighlightControl(ctl As...

Suppliers with Top Items List
Can anyone let me know how to show suppliers on Top Item List? I badly need this. I tried my way but not successful. -- Many Thanks Install the RMS Toolkit trial download you requested and check off Custom Reports/Templates during installation. Now run Manager, Reports, Custom, Top Items Report. This report includes Suppliers, Dept, Category, and Sub-Descriptions as columns and filter options. There are over 100 free reports and templates included with our RMS Toolkit trial. Visit: www.digitalretailer.com/trial Greg Digital Retail Solutions www.digitalretailer.com/RMSaddins (v1.x and ...

Selecting data for charts by criteria
Hi there, I am in control of a large database of information and I want to be able to automatically make my charts update to include new data that is added. I am using a regular excel spreadsheet. I would also like to be able to select which data is used for a chart depending on a criteria such as, 0 <= value <= 10. Any help is appreciated. Thanks Tim Archer ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Tim - If it's an external database, I guess yo...

"One or more of the selected senders could not be added to your blocked senders list"
Hey all. I get this when ever I try to killfile anyone at all, regardless of who they are. I'm on OE6 in XP Home and this has only just started happening. My KF only has a few spammers in it so I don't think it's full. Does anyone know what's going on? Thanks Ayatollah of rock 'n' roller wrote: > Hey all. I get this when ever I try to killfile anyone at all, regardless of > who they are. I'm on OE6 in XP Home and this has only just started > happening. My KF only has a few spammers in it so I don't think it's full. > Does anyone...

Sort #2
How do you sort 5 or more columns? They must be contiguous columns. Select them all together *before* you choose <Data> <Sort>. Never, never, never allow XL to choose your sort range for you. Never, never, never use the sort icons on the tool bar. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jim" <anonymous@discussions.microsoft.com> wrote in message news:79F803D9-B933-46B9-8E49-9E22079E22C1@microsoft.com... How do you sort 5 or more...

Outlook 2003
I have four different accounts setup in Outlook. I know that it is possible to use a default signature for new messages and replies with each account. However, when selecting New to start a new message, naturally, it uses the default mail account and inserts the corresponding signature. However, when changing the sent from account in the Accounts drop down, one would expect the "default" signature to be used; i.e. Outlook would delete the signature that it initially inserted and replace it with the default signature for the selected account. This is not what happens. Seemingly, ...

Saving Selected Cells to a Text File w/o using a VBA script
I am trying to save only a range of cells in a worksheet to a tab-delimited text file. I have seen entries posted in this discussion group and have implemented a VBA script (based on some recommnedations posted) that does what I need to do. But my question is: Is there a way to save only a selected portion of a worksheet within a workbook to a text file? (i.e., NOT the entire worksheet, just a selection of cells). Thanks for any help you can give. Hi without VBA not possible -- Regards Frank Kabel Frankfurt, Germany "patm" <patm@discussions.microsoft.com> schrieb im...