Save query using VBA

Question

I have form with multiple textboxes that are used to filter Subforms. The Subform Source Objects are queries. In order to filter the Subforms I update the query SQL and reassign the query as the source object on the subform.

Set qdf = db.QueryDefs("MyQuery")
qdf.SQL = strNewSQL
mySubForm.SourceObject = "Query.MyQuery"

The filtering works correctly. However when I close my form, I get a message asking if I want to save changes to the queries. How can I avoid this message?

Edit:- Following comment by @June7, closing form using

DoCmd.Close acForm, "MyForm", acSaveNo

solved my issue.

It is also possible to close the form using an embedded macro and setting prompt = No.

I tested code and don't get prompt. How are you closing form? Edit question with full procedure — June7
Thank you for this. I was using an embedded macro to close the form. I have now changed this to use DoCmd.Close acForm, "MyForm", acSaveNo and now I do not get the prompt. — jcro001

June7 June7 · Accepted Answer · 2019-11-09T20:02:48

Using VBA instead of embedded macro solved your issue but consider that code can be simplified to:

CurrentDb.QueryDefs("MyQuery").SQL = strNewSQL
Me.mySubForm.SourceObject = "Query.MyQuery"

Save query using VBA

1 Answers