Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Filtered Outputto Objects

Posted by: azolder Apr 15 2016, 12:58 PM

There have been a few requests lately to filter OutputTo Method output. I've used elaborate procedures to actually change object properties, output the object, then reset previous properties. While trying to resolve problems outputing tables, I ran accross the https://msdn.microsoft.com/en-us/library/office/ff197651.aspx?f=255&MSPPError=-2147217396. A little experimentation led to the realization that filtering the OutputTo Method isn't that difficult (as long as you avoid the Microsoft's restrictions and shortfalls).

The module listed below summarizes what I've learned. It provides a generalize OutputTo with filters for forms, queries, reports and tables. FilteredOpenQuery and FilteredOpenTable provide WhereCondition capabilities to the OpenQuery and OpenTable methods.

The code should be usable in Access 2007 and above (I've tested 2007 and 2013).

CODE
Option Compare Database
Option Explicit

'api to pass control to Operating System, pausing code execution
Public Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)

'************************************
' FilteredOutputTo
'  filters or removes filter from Output object executes OutputTo, then resets filter
'  Arguments, except for WhereCondition, are the same as for the OutputTo method
'  WhereConditon is a SQL WHERE clause without the where
Public Sub FilteredOutputTo(ObjectType As AcOutputObjectType, ObjectName As Variant, _
      Optional WhereCondition As Variant, Optional OutputFormat As String, _
      Optional OutputFile As Variant, Optional AutoStart As Variant = False, _
      Optional TemplateFile As Variant, _
      Optional Encoding As Variant, Optional OutputQuality As _
         AcExportQuality = acExportQualityPrint)

   Const conProcedure As String = "FilteredOutputTo"
   Dim oType As AcObjectType
   Dim bClose As Boolean
   Dim oReport As Object
  
   Select Case OutputFormat
      Case acFormatSNP, acFormatIIS, acFormatASP
         Err.Raise vbObjectError + 1000, conProcedure, _
            "SNP, IIS and ASP formats are not supported."
         'SNP since Access 2010.  IIS and ASP not listed in Access 1013
   End Select
  
   Select Case ObjectType
      Case acOutputForm
         DoCmd.OpenForm FormName:=ObjectName, WhereCondition:=WhereCondition, _
            WindowMode:=acHidden
      
      Case acOutputQuery
         DoCmd.OpenQuery ObjectName
         If Len(WhereCondition) > 0 Then _
            DoCmd.ApplyFilter WhereCondition:=WhereCondition
         DoCmd.Minimize  'can't hide the object, but we can minimize it
                                    'note Access needs to be in Overlapping Windows mode
        
      Case acOutputReport
         Select Case OutputFormat
            Case acFormatIIS, acFormatASP, acFormatXLSX, acFormatXLSB
               Err.Raise vbObjectError + 1001, conProcedure, _
                  "Access doesn't support XLSX and XLSB report formats."
         End Select
        
         DoCmd.OpenReport ReportName:=ObjectName, WhereCondition:=WhereCondition, _
            WindowMode:=acHidden, View:=acViewPreview
        
      Case acOutputTable
         DoCmd.OpenTable ObjectName
         If Len(WhereCondition) > 0 Then _
            DoCmd.ApplyFilter WhereCondition:=WhereCondition
         DoCmd.Minimize
        
      Case Else
            Err.Raise vbObjectError + 1002, conProcedure, _
               "acOutputFunction, acOutputModule, acOutputServerView, and " & _
               "acOutputStoredProcedure are not supported by this procedure."
   End Select
  
   DoCmd.OutputTo ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, _
      TemplateFile, Encoding, OutputQuality
    
   'pause until the file has been created and object closed
   Do Until Len(Dir(OutputFile)) > 0
      Sleep 250   '1/4 second
      DoEvents
   Loop
  
   'determine ObjectType from acOutputOutputType
   Select Case ObjectType
      Case acOutputForm: oType = acForm
      Case acOutputQuery: oType = acQuery
      Case acOutputReport: oType = acReport
      Case acOutputTable: oType = acTable
   End Select
  
   DoCmd.Close oType, ObjectName
  
  
End Sub

'****************************
' FilteredOpenQuery
'  opens query and applies filter
'  Arguments, except for WhereCondition, are the same as for the OpenQuery method
'  WhereConditon is a SQL WHERE clause without the where
Public Sub FilteredOpenQuery(QueryName As Variant, Optional WhereCondition As Variant, _
      Optional View As AcView = acViewNormal, Optional DataMode As AcOpenDataMode = acEdit)
  
   DoCmd.OpenQuery QueryName, View, DataMode
   DoCmd.ApplyFilter WhereCondition:=WhereCondition

End Sub

'****************************
' FilteredOpenTable
'  opens table and applies filter
'  Arguments, except for WhereCondition, are the same as for the OpenTable method
'  WhereConditon is a SQL WHERE clause without the where
Public Sub FilteredOpenTable(TableName As Variant, Optional WhereCondition As Variant, _
      Optional View As AcView = acViewNormal, Optional DataMode As AcOpenDataMode = acEdit)

   DoCmd.OpenTable TableName, View, DataMode
   DoCmd.ApplyFilter WhereCondition:=WhereCondition  'Access 2010 on only

End Sub