[AccessD] Form saves changes to Form Property: Order By

[Gary Kjos]

Got this one too.

On Sun, Nov 15, 2020 at 6:41 PM Borge Hansen <pcs.accessd at gmail.com> wrote:
>
> Hi everyone,
> I read with interest Tina's predicament with the Form no longer loading records.
>
> I have a somewhat similar problem with the setting and saving of a
> Form's properties, in this case the "Order By" property on a
> Continuous Form.
>
> Currently using MS Access 365 / Access 2019 !
>
> So for years we have been using the following setup:
> In the header above the row of txtControl controls we have labels with
> the following function on the "On Click" event :
>
> =columnsort([name of txtControl below])
>
> the function is :
> +++++++
> Function ColumnSort(ColumnToBeSorted)
>   'ColumnToBeSorted : Name of txtControl to sort on
>   On Error GoTo Err_ColumnSort_Click
>
>     Dim frm
>     frm = Screen.ActiveForm.Name
>     If IsNull(Forms(frm).Tag) Or Forms(frm).Tag = "" Then Forms(frm).Tag = 0
>     Forms(frm)(ColumnToBeSorted).SetFocus
>
>     If Forms(frm).Tag = 0 Then
>       RunCommand acCmdSortAscending
>       Forms(frm).Tag = 1
>     Else
>       RunCommand acCmdSortDescending
>       Forms(frm).Tag = 0
>     End If
> Exit_ColumnSort_Click:
>     Exit Function
> Err_ColumnSort_Click:
>    MyMsgBox Err.Number & "  " & Err.Description
>    Resume Exit_ColumnSort_Click
> End Function
> ++++++++
>
> When I open a Continuous Form directly from the navigation panel and
> in sequence sort on three or more different columns and then close the
> Form by a right click and close form, the "Order By" parameters are
> saved to the Form's "Order By" property - and not just the most recent
> sort column but all columns having been sorted on, like this :
>
> Order By: [frmPurgeReviveContact].[RWCode],
> [frmPurgeReviveContact].[FullNameSorting] DESC,
> [frmPurgeReviveContact].[UFN]
>
> Strangely, the Form's Tag property change is not saved!
>
> I am pretty sure that previous versions of Access (Access 2003 comes
> to mind) did not have this behaviour.
>
> The expected behavior (at least mine) is for a Form that is opened in
> view mode to close without saving any changes to the Form's properties
> that may have been changed through vba code while in view mode....
>
> I see on https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openform
> that the docmd.openform optional datamode has as its default setting :
> DataMode
> Optional
> AcFormOpenDataMode
> An AcFormOpenDataMode constant that specifies the data entry mode for the form.
> This applies only to forms opened in Form view or Datasheet view. The
> default value is acFormPropertySettings.
>
> And the acFormPropertySettings will save any changes to the Form's properties!
>
> All of our Forms opened from vba code has so far been using a simple
> docmd.OpenForm
>
> We have another continuous Form that people are using constantly to
> filter and sort and the "Order By" parameters are never saved on
> closing this form with a simple docmd.Close
>
> Now, on this particular Form - what is different here - the record
> source keeps changing and the ColumnSort() function here takes as
> parameter the name of the txtControl which is different from the
> underlying control source property referencing the field or column in
> the recordsource query. I don't know if that is what makes the form
> not save the sort parameters on the Form's "Order By" property.
>
> With the first continuous Form that now is a problem the Form is
> opened from a command button's Hyperlink Address property referencing
> the Form and in order for the form not to save the the "Order By" form
> property changes I have had to change the
> docmd.Close
> to
>     DoCmd.Close acForm, "frmPurgeReviveContact", acSaveNo
>
>
> So my question is :
> Did MS change the default value of the AcFormOpenDataMode from one
> older MS Access version to a newer one? Because this behavior is new
> to me.
>
> And is there a way to change this default setting other than now
> having to change all instances of
> docmd.close
> for closing a form to one that specifically says "and don't save any
> changes to the Form's properties that User may have done while working
> on the form"
>
> /borge
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



-- 
Gary Kjos
garykjos at gmail.com