Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

RTF or XL report filtered (Allen Browne)

Discussion in 'Information Technology' started by lmv, Jul 28, 2006.

  1. lmv

    lmv
    Expand Collapse
    Guest

    I have this great filtering code but what do I need to change in the
    following code to have a button export to RTF or xl rather than preview the
    report and then have to export or is it possible?
    Thanks!!

    Private Sub cmdPreview_Click()
    On Error GoTo Err_Handler
    'Purpose: Open the report filtered to the items selected in the list box.
    'Author: Allen J Browne, 2004. http://allenbrowne.com
    Dim varItem As Variant 'Selected items
    Dim strWhere As String 'String to use as WhereCondition
    Dim strDescrip As String 'Description of WhereCondition
    Dim lngLen As Long 'Length of string
    Dim strDelim As String 'Delimiter for this field type.
    Dim strDoc As String 'Name of report to open.

    'strDelim = """" 'Delimiter appropriate to field type. See
    note 1.
    strDoc = "rptProducts by Category"

    'Loop through the ItemsSelected in the list box.
    With Me.lstCategory
    For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    'Build up the filter from the bound column (hidden).
    strWhere = strWhere & strDelim & .ItemData(varItem) &
    strDelim & ","
    'Build up the description from the text in the visible
    column. See note 2.
    strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
    End If
    Next
    End With

    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
    strWhere = "[Categories].[CategoryID] IN (" & Left$(strWhere,
    lngLen) & ")"
    lngLen = Len(strDescrip) - 2
    If lngLen > 0 Then
    strDescrip = "Categories: " & Left$(strDescrip, lngLen)
    End If
    End If

    'Report will not filter if open, so close it. For Access 97, see note 3.
    If CurrentProject.AllReports(strDoc).IsLoaded Then
    DoCmd.Close acReport, strDoc
    End If

    'Omit the last argument for Access 2000 and earlier. See note 4.
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
    OpenArgs:=strDescrip

    Exit_Handler:
    Exit Sub

    Err_Handler:
    If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
    MsgBox "Error " & Err.Number & " - " & Err.Description, ,
    "cmdPreview_Click"
    End If
    Resume Exit_Handler
    End Sub
     
  2. Loading...

    Similar Threads Forum Date
    Report Emphasizes Shortfall in Medicare (washingtonpost.com) Interfaith Dialogues Mar 25, 2005
    India Of 50 ministerial portfolios in Punjab, 27 held by Badal family Breaking News Sep 21, 2012
    Sikh News Punjab sure of procuring surplus milk, Haryana fears shortfall (Express India) Breaking News May 15, 2008
    Sikh News Punjab fears shortfall of 2.4m tons in wheat output (Dawn) Breaking News Apr 7, 2008
    http://www.CertForums.co.uk Link Partners Aug 23, 2004

  3. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    Take a look at docmd.transferspreadsheet and docmd.transfertext.
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The code you posted builds the string strWhere, and uses it for the
    WhereCondition of OpenReport. If you want to export the query or report
    instead, you could either:
    a) rewrite the query statement before export of the query, or
    b) handle the filter in the report's Open event.

    Option (a): Export the query
    =====================
    You can copy the stub of the SQL statement (everything before the WHERE
    clause) and the tail (everything after the WHERE clause), patch in the
    strWhere string you built in your code, assign it to an existing QueryDef,
    and export that:

    Dim strSql As String
    Const strcStub = "SELECT * FROM Table1 WHERE "
    Const strcTail = " ORDER BY Field1;"
    Const strcQuery = "Query1"
    'use the code you already have to built the strWhere
    dbEngine(0)(0).QueryDefs(strcQuery).SQL = strcStub & strWhere & strcTail
    DoCmd.TransferText acExportDelim, , strcQuery, "C:\MyExport.txt", True

    Option (b): Export the report
    =====================
    When you use OutputTo with a report, there is no WhereCondition. You
    therefore need to pass the filter string to the report somehow. In A2002 or
    2003 you could use the OpenArgs to do that, but I prefer to use a public
    string variable because it works in all versions and leaves OpenArgs free
    for other purposes.

    1. In a standard module, General Declarations section (top, with the Option
    statements):
    Public gstrReportFilter As String

    2. In the Open event procedure of the report, apply the string as a filter,
    and clear it:
    Private Sub Report_Open(Cancel As Integer)
    If gstrReportFilter <> vbNullString Then
    Me.Filter = gstrReportFilter
    Me.FilterOn = True
    gstrReportFilter = vbNullString
    End If
    End Sub

    3. In the code that builds up strWhere, assign to the public variable before
    you export the report:
    gstrReportFilter = strWhere
    DoCmd.OutputTo acOutputReport, "Report1", acFormatRTF, "C\MyExport.rtf"

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "lmv" <lmv@discussions.microsoft.com> wrote in message
    news:64948823-252E-46CC-977F-5F11F167C28D@microsoft.com...
    >I have this great filtering code but what do I need to change in the
    > following code to have a button export to RTF or xl rather than preview
    > the
    > report and then have to export or is it possible?
    > Thanks!!
    >
    > Private Sub cmdPreview_Click()
    > On Error GoTo Err_Handler
    > 'Purpose: Open the report filtered to the items selected in the list
    > box.
    > 'Author: Allen J Browne, 2004. http://allenbrowne.com
    > Dim varItem As Variant 'Selected items
    > Dim strWhere As String 'String to use as WhereCondition
    > Dim strDescrip As String 'Description of WhereCondition
    > Dim lngLen As Long 'Length of string
    > Dim strDelim As String 'Delimiter for this field type.
    > Dim strDoc As String 'Name of report to open.
    >
    > 'strDelim = """" 'Delimiter appropriate to field type. See
    > note 1.
    > strDoc = "rptProducts by Category"
    >
    > 'Loop through the ItemsSelected in the list box.
    > With Me.lstCategory
    > For Each varItem In .ItemsSelected
    > If Not IsNull(varItem) Then
    > 'Build up the filter from the bound column (hidden).
    > strWhere = strWhere & strDelim & .ItemData(varItem) &
    > strDelim & ","
    > 'Build up the description from the text in the visible
    > column. See note 2.
    > strDescrip = strDescrip & """" & .Column(1, varItem) & """,
    > "
    > End If
    > Next
    > End With
    >
    > 'Remove trailing comma. Add field name, IN operator, and brackets.
    > lngLen = Len(strWhere) - 1
    > If lngLen > 0 Then
    > strWhere = "[Categories].[CategoryID] IN (" & Left$(strWhere,
    > lngLen) & ")"
    > lngLen = Len(strDescrip) - 2
    > If lngLen > 0 Then
    > strDescrip = "Categories: " & Left$(strDescrip, lngLen)
    > End If
    > End If
    >
    > 'Report will not filter if open, so close it. For Access 97, see note
    > 3.
    > If CurrentProject.AllReports(strDoc).IsLoaded Then
    > DoCmd.Close acReport, strDoc
    > End If
    >
    > 'Omit the last argument for Access 2000 and earlier. See note 4.
    > DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
    > OpenArgs:=strDescrip
    >
    > Exit_Handler:
    > Exit Sub
    >
    > Err_Handler:
    > If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
    > MsgBox "Error " & Err.Number & " - " & Err.Description, ,
    > "cmdPreview_Click"
    > End If
    > Resume Exit_Handler
    > End Sub
     

Share This Page