473,288 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Work with 2 sort orders simultaneously

675 512MB
I have a form, fMain, that displays a single record from a table tMovies. This table has several fields, amongst them Key (PK), Title, DateSeen

Also on the form are 2 listboxes and 5 command buttons:
lbxAlphabet.RowSource = "*","#","A","B","C",..."Z"
lbxTitles.RowSource = "SELECT Key, Title FROM tMovies WHERE ... ORDER BY Title"

cmdCreateFilter: creates the string strWHERE for use in lbxTitles.RowSource from input on a modal form and from lbxAlphabet
cmdFirstRec: moves to the first record shown in lbxTitles
cmdPriorRec: moves to the previous record from the highlighted (selected) row in lbxTitles
cmdNextRec: moves to the next record from the highlighted (selected) row in lbxTitles
cmdLasttRec: moves to the last record shown in lbxTitles

cmdXxxxRec (any of the above navigation buttons) changes the highlighted (selected) row of lbxTitles as well as changing the form data record.

The form data is displayed by fMain.Filter = "Key=" & lbxTitles.Column(0)

Assume for this question that lbxTitles has at least 1 row. If it doesn't, then cmdXxxxRec controls are set to .Enabled=False and cannot be used

All of the above works!

My question: On occasion I want the comXxxxRec buttons to use the DateSeen, sorted ASCending. So after filtering, say for all movies seen in 2008, lbxTitles would display "3:10 to Yuma" as the first row, and "Zodiac Killer" as the last row. I have selected "Men of Honor", about in the middle of the list, but seen 3 Mar08.
If I click cmdFirstRec, "Passenger 57" should be highlighted in lbxTitles, and the form should display that record, because I saw it 2 Jan08. cmdNextRec would highlight and display "The Flight of the Phoenix" seen 3 Jan08. lbxTitles would remain in alphabetic order.

How do I do this?
Jun 15 '09 #1
6 1617
FishVal
2,653 Expert 2GB
Hello, OldBirdMan.
  • You may open an additional recordset ordered by [DateSeen] - listbox choice moves cursor to a record with a given [Key] value, navigation buttons move cursor recordwise.
  • The recordset mentioned above could be the form's recordset. Thus your ListBox is ordered by [Title] and form is ordered by [DateSeen].

Regards,
Fish.
Jun 15 '09 #2
OldBirdman
675 512MB
Thanks Fish -

You may open an additional recordset ordered by [DateSeen] - listbox choice moves cursor to a record with a given [Key] value, navigation buttons move cursor recordwise.
I thought of this, and it works for displaying the data, but to highlight the Title in lbxTitles I have to loop through the listbox searching for the key. In the example given, there are only a few thousand rows at most, but even here Access does not populate the entire listbox until an attempt is made to access rows not populated. I'm not sure how Access keeps track, but it runs slower than might be expected.
My program for keeping track of birds has 300K+ records in one table, and this solution seems somewhat awkward. I will use this logic in at least 3 of my projects, Movies is the simplist and so used in my original post.


The recordset mentioned above could be the form's recordset. Thus your ListBox is ordered by [Title] and form is ordered by [DateSeen].
This has the same problems as the other idea, I cannot know the row of the listbox without a loop to search for the key. Although not stated in my original post, I have one case where I would want 3 sort orders, and this method is not expandable.

I am really hoping to keep the highlighted current record in lbxTitles if practical.
Jun 15 '09 #3
NeoPa
32,554 Expert Mod 16PB
What springs to mind is that any sorting should always be applied to both the form and the ListBox simultaneously. .Requery should also be applied where necessary. Otherwise the record sets may get out of sequence and thus cause problems similar to those you're experiencing.
Jun 15 '09 #4
FishVal
2,653 Expert 2GB
Well.

If ListBox.BoundColumn=1 then does it help to use ListBox.Value property to get set highlighted row?
Jun 15 '09 #5
OldBirdman
675 512MB
Please forgive my slow response to the last suggestion. I needed to look at many projects to see if this would work.

As it turns out, all of these listboxes had ListBox.BoundColumn = 0 which made things quite simple. ListBox = ListBox + 1 highlighted the next row and .Column(0) produced the Key for the record. FishVal's suggestion of using .BoundColumn = 1 does exactly what I need. More work to implement, but that is to be expected if I want more functionality.

Thank you!
Jun 17 '09 #6
NeoPa
32,554 Expert Mod 16PB
Pleased to hear it OB. Thanks for the update :)
Jun 17 '09 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: David | last post by:
Hi, I have a MySQL database and in the front-end software I am using to Run MySQL, I have created a query on the tables which runs in the front-end, similar to the MS Access query grid. I now...
2
by: BjoernJackschina | last post by:
Hello, I just look for a capability to sort several words in view of alphabet. An example: stop is 'opst' reach is 'aechr' This should read in a new file so that I can look for same letter...
5
by: Dr. Ann Huxtable | last post by:
Hello All, I am reading a CSV (comma seperated value) file into a 2D array. I want to be able to sort multiple columns (ala Excel), so I know for starters, I cant be using the array, I need...
19
by: David | last post by:
Hi all, A while back I asked how to sort an array of strings which would have numerals and I wanted to put them in sequential numerical order. For example: myArray = "file1"; myArray =...
6
by: Les Juby | last post by:
I need to extract records from a database subject to conditions and only thereafter give the users the choice of which fields to sort the results on. In this situation I can't write back to a...
48
by: Jimmy | last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far: <% Dim oConn, oRS, randNum Randomize() randNum = (CInt(1000 * Rnd) + 1) *...
10
by: kruzin24 | last post by:
I have a worksheet that I run a list of macros from, and a sheet called Orders.xls that is edited by the worksheet. It has 4 columns, and a maximum of 3000 rows. I recorded a macro to sort the...
8
by: Mark12345 | last post by:
Hi folks, thanks for looking. I have a database that takes down order details. Each customer has there own priceid in the price table. the errors that I get say enter parameter value for:...
3
by: printline | last post by:
Hello All I have a table with some information in the rows on orders placed by my customers. Each column in the table represents some data on the product in the row. What i want to do is make...
5
kcdoell
by: kcdoell | last post by:
I have a form that I want to change the way the subform (continuous) is displaying the info (the sort order). In another words, I want to give the user the ability to see the same information in...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.