How to stop Access from prompting "Do you want to save changes to the layout of query"

Question

How can I stop Access from prompting "Do you want to save changes to the layout of query" when I try to close a form that has a subform in datasheet view?

I have a form with a subform in Datasheet view with the .SourceObject set to a temporary pivot / crosstab query (no actual form object). If the user changes the width of a column and closes the window with the built-in Access close button, the user (and annoyed developer) is always presented with the "Do you want to save changes to the layout of query" prompt.

I am able to avoid this prompt by setting MySubform.SourceObject = "" in the click of my Close button but anyone clicking the [x] button or pressing CTRL+F4 or CTRL+W gets the prompt.

I have put breakpoints in the Form_Close and Form_Unload events but this prompt appears before they fire.

I want to clarify further that this subform object is Unbound and not based on a form object. I am building a dynamic Crosstab SQL statement, creating a QueryDef with the SQL, and then setting MySubform.SourceObject = "query.qry_tmp_Pivot" This is a neat technique for a crosstab/pivot query because the columns can vary and I don't think a form object would support that.

I am able to use the Watch window on the MySubform object and I can see that a MySubform.Form object exists. It has a Controls collection containing a control for all of the columns in my query. I have an optional routine that I can run that will loop through all of the controls and set their .ColumnWidth = -2, which will auto-size the column width based on the widest data of the visible rows in the datasheet. When this routine was running I was noticing that every time I closed the form (not using my Close button) I was getting the save prompt. I have disabled this routine for debugging but a user will still get the prompt if they manually adjust any column width.

I feel like I need to explain this extra detail so you realize this is not an Access 101 issue. You probably know that if you've read this far. Here's another thought I had: Maybe I could trap the Unload event in the subform control before the prompt happens. Because there is no true Form object to put test code in, I created a class object and passed MySubform to it. The class uses WithEvents and creates events like OnClose and OnCurrent on the class module's mForm object. Sample class code is below.


Private WithEvents mForm As Access.Form  ' This is the form object of the Subform control

Public Sub InitalizeSubform(Subform As Access.Subform)

    Set mForm = Subform.Form
    Debug.Print Subform.Name, mForm.Name, mForm.Controls.count
    
    ' Create some events to intercept the default events.
    mForm.OnClick = "[Event Procedure]"
    mForm.OnClose = "[Event Procedure]"
    mForm.OnUnload = "[Event Procedure]"
    mForm.OnCurrent = "[Event Procedure]"

End Sub

Private Sub mForm_Click()
    Debug.Print "Clicking " & mForm.Name
End Sub

Private Sub mForm_Current()
    Debug.Print "On Current " & mForm.Name, "Record " & mForm.CurrentRecord & " of " & mForm.RecordsetClone.RecordCount
End Sub

Private Sub mForm_Unload(Cancel As Integer)
    Debug.Print "Unloading " & mForm.Name
End Sub

Private Sub mForm_Close()
    Debug.Print "Closing " & mForm.Name
End Sub

The VBE Watch window shows my new events on the mForm object but unfortunately they never fire. I know the class works because I used it with a bound subform and all of the events are intercepted by the class. I'm not sure what else to try.

Can disable X close with form properties and trap Ctrl+key combinations with an AutoKeys macro. Is right click menu Close also an issue? There are ways to stabilize a CROSSTAB query so the field names will be same each time. — June7, Aug 30 '19 at 06:48
If your class works with a bound form, why not write the output of the crosstab to a temp table and bind a subform to this? Slightly clumsy, but if it works ... and that dialogue box _is_ disturbing. — Gustav, Aug 30 '19 at 07:11
What about **[DoCmd.SetWarnings method (Access)](https://learn.microsoft.com/en-us/office/vba/api/access.docmd.setwarnings)**? Maybe it can help you out, but test it — Foxfire And Burns And Burns, Aug 30 '19 at 08:56
@June7, I don't want to go down the path to disable [x] and trap key combinations. My Crosstab query can have different columns each time it runs so I am dynamically creating the `IN ()` list to append to the PIVOT statement.--- @foxfireandburnsandburns, I don't think that will help. I don't want to turn off warnings for the entire operation of the form. I just need to disable them when the form closes, which is where I'm having trouble finding an event to capture it. — Ben, Sep 02 '19 at 03:33
@Gustav, I tried out your idea to bind to a temp table. Unfortunately if I change the width or sort of a column I'm getting the save prompt, though now for the temp table. — Ben, Sep 02 '19 at 03:43

Erik A · Accepted Answer · 2019-08-30T09:31:16.037

2

Events on the subform never fire because it's a lightweight form (without a module). See this Q&A and the docs. Lightweight forms don't support event listeners, but do support calling public functions from an event, e.g. mForm.OnClick = "SomePublicFunction()"

Note that the workaround described in this answer also opens up the possibility of displaying a crosstab query in a form without saving it at all.

Alternatively, you could try capturing the event on your main form, and suppress saving there.

edited Aug 30 '19 at 09:31

answered Aug 30 '19 at 09:15

Erik A

31,639
12
42
67

This is the kind of extra help I was looking for. After setting the `.SourceObject` property of my subform from a button on my main form, I was able to set `.Form.OnCurrent = "=SomePublicFunction()"` *(note the = is required before the function name)*. After I verified that it was firing for OnCurrent, I added the function to a variety of events: OnUnload, OnLostFocus, OnClose, OnDeactivate. Unfortunately none of them would fire before the save prompt if I closed the parent form with the [x]. I also tried to capture the event on the main form and still the save prompt happens first. :( – Ben Sep 02 '19 at 03:07
2

I recommend the workaround. I developed that specifically to display crosstab queries in subforms, even though the shared version only does tables. I can share the adapted version that works with SQL queries/recordsets – Erik A Sep 02 '19 at 05:21
Your workaround was clearly written and easy to implement so I gave you credit even though I posted an answer. I hope this is acceptable conduct on SO. I didn't have any trouble assigning the `.Recordset` to a query instead of a table. I'm not getting errors. Is there something I should look out for? – Ben Sep 02 '19 at 06:07
1

One thing: if you decide to use a temporary querydef instead of a stored query and parameters in your crosstab query, Access will hard-crash upon filtering/sorting from the top menu (no VBA error but an irrecoverable crash which might leave your database in an inconsistent state). It's the only case where I advocate using string concatenation. But I see you're using a stored query, so that's not an issue for your current implementation – Erik A Sep 02 '19 at 07:33
Good tip. For anyone else reading this, a temporary QueryDef is one that is defined with no name. More info at this [link](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-createquerydef-method-dao) – Ben Sep 03 '19 at 21:38

score 1 · Answer 2 · answered Sep 02 '19 at 06:04

I gave answer credit to @ErikA for this question. He directed me to an answer to a somewhat related question here which is what I ended up implementing and it works. His instructions were very straightforward and easier to implement than I first anticipated.

The answer to my question seems to be that it may not be possible to avoid the save prompt when using a lightweight form object. If someone comes along with a solution I'd still like to hear it.

What I learned from this experience:

An unbound subform that has its .SourceObject set to a table or query will not have a code module. This considered to be a lightweight object.
Public functions can be added to event properties on lightweight forms but they don't seem to fire before the save prompt appears as the parent form is closing.
Binding a pivot query or temp table to a real form object that has 254 controls on it is the only way I found that doesn't prompt to save design changes when the parent form closes. Mapping the query/table columns to the 254 datasheet controls is super fast.
A class is a nice way to intercept events if you're not using a lightweight object. A class also lets you open multiple instances of the same object.
I ended up not implementing my solution without using a class because there were no events I needed to capture. At this point I don't need my frmDynDS to be used for other purposes and I'd like to keep the object count down (this is a large app with 1400+ objects).

I put the following code in a function that gets called when the subform is loaded or refreshed.

With subCrosstab
    ' Set the subform source object to the special Dynamic Datasheet form.  This could be set at design time on the Subform control.
    .SourceObject = "Form.frmDynDS"
    ' Run the code to assign the form controls to the Recordset columns.
    .Form.LoadTable "qry_tmp_Pivot" ' A query object works as well as a table.

    ' Optional code
    .Form.OnCurrent = "=SomePublicFunction()"
    .Form.AllowAdditions = False
    .Form.AllowEdits = False
    .Form.AllowDeletions = False
    .Form.DatasheetFontHeight = 9   
End With

I can now resize the columns and never get a prompt to save layout changes no matter how I close the parent form.

How to stop Access from prompting "Do you want to save changes to the layout of query"

How can I stop Access from prompting "Do you want to save changes to the layout of query" when I try to close a form that has a subform in datasheet view?

2 Answers2