Work with 2 sort orders simultaneously

[OldBirdman]

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?

[FishVal]

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.

[OldBirdman]

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.

[NeoPa]

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.

[FishVal]

Well.

If ListBox.BoundColumn=1 then does it help to use ListBox.Value property to get set highlighted row?

[OldBirdman]

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!

[NeoPa]

Pleased to hear it OB. Thanks for the update :)