Continuous Form Date Sorting

[LGDGlen]

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:

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:

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:

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]

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

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]

@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]

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;

Me.FilterOn = False
Me.OrderByOn =  False

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

 

[LGDGlen]

@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]

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 may be interested in this to give you even more flexibility.

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

 

[LGDGlen]

@Gasman thanks i'll take a look

 

[LGDGlen]

@MajP looking, thank you

 

[MajP]

@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

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.

 

[LGDGlen]

@MajP wow, ok that sounds perfect