Suppress prompt "Do you want to save changes to the layout of query"

Question

I have a subform in Datasheet View with a SourceObject set, in code, to a query. This works great except if a user changes the format of the datasheet, like changing column width, and closes the form the user gets the prompt: "Do you want to save changes to the layout of query 'queryname'?"

Is there a way to prevent this prompt, even if it means preventing the user from modifying the format of the datasheet?

So far I've tried unchecking the box "Enable design changes for tables in Datasheet view" in Access Options.  I've also tried inserting the following code in the form unload event:

DoCmd.SetWarnings False
subfrmForm1.SourceObject = ""
DoCmd.SetWarnings True

This doesn't work because the message box appears before the unload event.

Answer 1

As long as you don't use DoCmd: No. This is the default behavior.

But as users like additional close buttons: Add one with

DoCmd.Close acForm, AFormName, ACloseSave

And set ACloseSave to True/False according to your needs.

Answer 2

I was hoping to avoid using a command button and disabling the close buttons.  People are familiar with close buttons and the command button will take up space and there is the programming involved in disabling the close buttons.

Answer 3

Hello LegacySW,

>>I have a subform in Datasheet View with a SourceObject set, in code, to a query.

It seems that you are trying to change the subform's source object dynamically. When will you change the source object? In my test, the prompt only occurs after I changed the subform's source object and then close the form. Once I set the subform's source object and reopen the form, whatever I do to like changing the column and then close the form does not cause the prompt.

Could you reproduce your issue if you does not change the subform's source object?

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

@Terry, there are multiple reasons for setting the SourceObject only during runtime. Sometimes the data is not available at form open, e.g. import scenarios. Or only loading when visible on a PageControl.

Answer 5

>>I have a subform in Datasheet View with a SourceObject set, in code, to a query.

It seems that you are trying to change the subform's source object dynamically.

In all my applications I only use (the same generalized) forms that are tuned dynamically, including the setting of RecordSource, SourceObject. etc. This has never resulted in the question to save any of the changes.

I have no experience with Datasheet Views. Perhaps that is the problem.

Imb.