Continuous Form Date Sorting (1 Viewer)

LGDGlen

Member
Local time
Today, 22:34
Joined
Jun 29, 2021
Messages
229
Hi

I have a continuous form that is by default sorted by a date field of a record. If a user enters a 10 new records which have the same date the records are sorted in a random order, which i assume is as expected, so as an example:

1631613773096.png

As you can see the last field is the record ID (which is normally not shown) and the records are not in order of entry.

So all records for the 09/09/2021 should be in the order of entry:

1631613838436.png


What i want to do is allow the user to sort by Depart Date or Dock Date and when they do it adds to the sort the secondary sort of Record ID in ascending order.

Is that something that is possible to catch when the user selects a new sort or new filter by right clicking on the data and adding a sort of the Record ID. As an example the above screenshots were filtered like:

1631614195478.png

And then sorted by the Record ID field in ascending order which is how the users expected to see the data and were asking why it wasn't as they expected.

i'm not sure if i've described the issue right (i hope i have) and whether its possible or whether its just something that i need to explain to the users why its the case that if you filter on a specific date or sort by a date those records with the same dates will be grouped together but will be in potentially an unexpected order

thanks in advance
 

Minty

AWF VIP
Local time
Today, 22:34
Joined
Jul 26, 2013
Messages
10,355
In your forms record source you can set a sort order, and it can cover more than one field.
So you could set the forms record source like

Code:
SELECT Field1, Field2, Field3, EntryDate, RecordID, Field6, Field7
FROM YourTable
ORDER By EntryDate, RecordID

Which would sort by the date then the ID which would at least be repeatable.
 

LGDGlen

Member
Local time
Today, 22:34
Joined
Jun 29, 2021
Messages
229
@Minty thank you, that would at least set the default sorted view but i'm trying to append the RecordID sort bit to any time the user resorts the data themselves. again tbh i'm not sure what i am asking is possible so happy if someone says "nope not possible" but just trying to capture when a user sorts the data themselves by "right clicking" on a column and selecting a sort, or filters the data themselves again using the right click capability
 

Minty

AWF VIP
Local time
Today, 22:34
Joined
Jul 26, 2013
Messages
10,355
If they resort it it becomes a moot point.
What I would do is put a "Reset Filters" command button on the form and if they press it do the following;

Code:
Me.FilterOn = False
Me.OrderByOn =  False

This would reset the form back to the default settings in your form original recordsource.
 

LGDGlen

Member
Local time
Today, 22:34
Joined
Jun 29, 2021
Messages
229
@Minty i have a reset sort button on the page so thats where i'll put the defaults, and i think you've pretty much answered what i thought which is its not possible to capture the event and override it
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:34
Joined
Sep 21, 2011
Messages
14,048
Could you use the OnFilter event to check if the ID is in the filter and if not append it?

Not used that even myself though.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:34
Joined
May 21, 2018
Messages
8,463
You may be interested in this to give you even more flexibility.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:34
Joined
May 21, 2018
Messages
8,463
@MajP looking, thank you
The point of that thread is that you drop the class module into your application then simply add the cmd buttons to the form. The entire code to make this work is then

Code:
Dim cf As classCommandFilters

Private Sub Form_Load()
    Set cf = New classCommandFilters
    With butt
        .add Me!Command37, "id"
        .add Me!Command60, "drawing"
        .add Me!Command61, "spool"
        .add Me!Command62, "MaxSize"
        .add Me!Command63, "heatnumber"
        .add Me!Command64, "length"
        .add Me!Command65, "paintcode"
        .add Me!Command66, "storagegrid"
        .add Me!Command67, "InventoryQty"
        .add Me!Command68, "InventoryDate"
        .add Me!Command69, "Remarks"
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set cf = Nothing
End Sub
That gives you the ability to do complex filters and sorts on any column you add.
 

Users who are viewing this thread

Top Bottom