MS Access - Suppress Save Prompt When Using Subform Filtering

Question

I have a form set-up that has a subform window and a few command buttons. The subform loads in a crosstab query that allows user to manipulate the data to a desired dataset before exporting (exporting done with a command button). This manipulation is only to be single use and discarded when the form is closed. When the form closes, I am being prompted to save the crosstab query. I would like to find a way to shut off this prompt and discard the changes.

To clarify: Form is opened, query loads in, user filters data to the data they want to view/export, when form is closed a prompt to save the query is produced. This prompt appears only if a filter had been applied.

I attempted to set-up a form to use as a subform initially but quickly realized that new controls will not be created when new columns are added (the whole reason why I used a crosstab). When the form is closed with the "Close Form" command button, I have the line of code

DoCmd.Close , , acSaveNo

which works perfectly. When the form is closed any other way, the prompt appears (closing the form with the "x", exiting the database, etc.). I do not want to shut off warnings as this will save the changes to the crosstab query. I have been researching everywhere for a workaround but the fact that I am using and must use a crosstab query becomes a significant factor.

The code I am using to apply the crosstab query filtering to the export command:

Private Sub ExportCmd_Click()

Dim NewSQL As String
Dim strPart1 As String
Dim strPart2 As String
Dim strPart3 As String
Dim OrigSQL As String

On Error GoTo Cancelled_Export

OrigSQL = CurrentDb.QueryDefs("basicrecordextractcrosstab").SQL
If Forms("exportlogdataform").Controls("Child290").Form.Filter & vbNullString = vbNullString Then
    NewSQL = OrigSQL
Else
    strPart1 = Left(OrigSQL, InStr(1, OrigSQL, "GROUP BY", vbTextCompare) - 1)
    strPart2 = "WHERE " & Replace(Forms("exportlogdataform").Controls("Child290").Form.Filter, "[BasicRecordExtractCrosstab].", "", , , vbTextCompare)
    strPart3 = Right(OrigSQL, Len(OrigSQL) - Len(strPart1))
    NewSQL = strPart1 & strPart2 & Chr(13) & strPart3
End If

CurrentDb.QueryDefs("basicrecordextractcrosstab").SQL = NewSQL

DoCmd.OutputTo acOutputQuery, "BasicRecordExtractCrosstab", "ExcelWorkbook(*.xlsx)", "", True, "", , acExportQualityPrint

Restore_SQL_Def:

CurrentDb.QueryDefs("basicrecordextractcrosstab").SQL = OrigSQL

Exit Sub

Cancelled_Export:
    If Err = 2501 Then
        MsgBox "Export to Excel Action Cancelled", vbInformation + vbOKOnly, "Export Cancelled"
    ElseIf Err = 2302 Then
        MsgBox "Unable to save export file.  Make sure the file is not currently open.", vbExclamation + vbOKOnly, "Export Failed"
    Else
        MsgBox Err & ": " & Error$
    End If

Resume Restore_SQL_Def

End Sub

Tested code. It does not alter query displayed on subform but does export altered SQL. Does not trigger prompt when I close form with X. Cannot replicate issue. — June7
The issue is presented when a filter is applied to the data (i.e. all entries from a particular day). What I assume is happening is the filter is altering the query design leading to the save prompt. — ExcelTinkerer
Interesting.... I'll have to see if I can put together a mock up of the DB for testing/evaluation. If you have any ideas of alternate techniques to perform these actions, I'd be very interested to hear them. — ExcelTinkerer

ExcelTinkerer ExcelTinkerer · Accepted Answer · 2019-07-22T18:30:31

I didn't want to go this route, but I hard coded the default SQL definition and turned off warnings when the form loads. I have the warnings turning back on in the form's close event.

Private Sub Form_Load()

Dim OrigSQL as String

OrigSQL = "Default SQL Def string here"

CurrentDb.QueryDefs("basicrecordextractcrosstab").SQL = OrigSQL

DoCmd.SetWarnings False

End Sub

Private Sub Form_Close()

DoCmd.SetWarnings True

End Sub

MS Access - Suppress Save Prompt When Using Subform Filtering

1 Answers