Run-time sorting causing "Save changes" dialog

Hi.

Using Office 365 - Microsoft Access on  Windows 10.  

My database has several forms so I have a navigation pane.  I am using continuous forms.

In one of my forms, the user has the option to change the form sort at run time.

By example
Me.OrderBy = "[StockNum] ASC": Me.OrderByOn = True:

Open in new window


The problem is that when the user moves to another form,  Microsoft Access will produce a popup with the following "Do you want to save changes to the design ....."

I would like the sort option to be available, but I would prefer that this not be seen as a design change (which causes the popup).
peispudAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
when the user moves to another form, are you closing the first one?  If so, there is a parameter that can either save or not

'close and don't save
DoCmd.Close acForm, Me.Name, acSaveNo

'close and save
DoCmd.Close acForm, Me.Name, acSaveYes
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you can also try setting AllowDesignChanges to false, which you can do in code before you sort:
me.AllowDesignChanges = false

Open in new window

you can also set this for the form before YOU save and distribute it.  Although this option no longer shows on the property sheet, it can be set through code.

Alternately, you can:
DoCmd.SetWarnings False

Open in new window

and then open other form and then on its open event:
DoCmd.SetWarnings True

Open in new window

However, using AllowDesignChanges would be preferable.
0
peispudAuthor Commented:
I've been trying some of the suggestions.    It's not working for me.

I am reluctant to  mess with the DoCmd.SetWarnings =true /false commands because this would block all warnings.

I even tried the following, but still get prompted when go from one tab to another on the navigation control.

Me.OrderBy = "[StockNum] ASC": Me.OrderByOn = True:
DoCmd.Close acForm, Me.Name, acSaveYes

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Since this is a Navigation control, there are things going on behind the scenes ... is there anything the user changes besides the sorting?
0
peispudAuthor Commented:
The user is given the option to see the continuous form by incrementing [stock number] or by [Record ID] which is "no sort".

For testing purposes, I put the sort command in the Form Load event.   The sort works fine.

I would be ok with disabling warnings with one command between the "Off" and the "On".
I am less comfortable in using this command on a form close event.

If I rem out
Me.OrderBy = "[StockNum] ASC": Me.OrderByOn = True:

Open in new window

 then everything is fine.
If I activate that same code, and then use
DoCmd.Close acForm, Me.Name, acSaveYes

Open in new window

, the problem is not resolved.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the form Load event is a better place.

For a navigation control, if the user is just switching to another tab, then the "smarts" behind-the-scenes does the closing ... so I am not sure you have control over this.

You can, however, put code in the form Unload event ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peispudAuthor Commented:
Microsoft Access determines that the  design of the form is changed when I do a sort. I cannot seem to neutralize this determination or save the changes at run time.

The popup happens before the form unload event and the form close event so that did not work either.

DoCmd.SetWarnings False
Me.OrderBy = "[StockNum] ASC": Me.OrderByOn = True: lblSortOrder.Caption = "Form sort by [StockNum]."
DoCmd.Close acForm, Me.Name, acSaveYes
DoCmd.SetWarnings True

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
in the Unload event, the form is already closing ... perhaps
DoCmd.SetWarnings False

Open in new window



And then
DoCmd.SetWarnings True

Open in new window

in the form Close event?

~~~

if that doesn't work:

have an option on the main part of the form for the sort order and evaluate it on the form Load event.
0
peispudAuthor Commented:
Thank you for your input.   I  believe this is a bug.  See   https://goo.gl/2qp4Bd

From this site though,  I found a workaround.

For all  Navigation Tabs      in the Enter Event                        DoCmd.SetWarnings False
For all forms ...                     in the Form_Load event             DoCmd.SetWarnings True

It's not my preferred solution, but it does work.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help

glad you got something that works .... be sure to also put DoCmd.SetWarnings True in the Close event of your navigation form! ... and in the Open event of any forms or reports you popup or open

Personally, I don't usually use the Navigation control much because it does not work with complex forms, so I still use the tab control and do my own loading and unloading ... but the navigation control does handle that automatically so better use of resources.

warm regards,
crystal

~ have an awesome day ~
0
peispudAuthor Commented:
I will consider what you said about not using the Navigation Control.  It does make things more simple,  but it requires the project to be more simple as well.  I am  committed on this project, but I will explore in the future.

Thank you for your help.  Have a great day.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help

warm regards,
crystal

~ have an awesome day ~
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.