Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    82

    Unanswered: Filter reports for email?

    Hi,

    I use DoCmd.SendObject to enable email of reports. This brings up the mailer program and the said report is attached as a txt or rtf or xls file. I wish to give the user the functionality to filter the report prior to emailing it - ie, make use of a where clause so only the parts pertinent to the recipient is sent. The SendObject command does not have this as an argument. Any ideas?

    J.

  2. #2
    Join Date
    Apr 2004
    Location
    Denmark
    Posts
    2
    Quote Originally Posted by Jablonski
    Hi,

    I use DoCmd.SendObject to enable email of reports. This brings up the mailer program and the said report is attached as a txt or rtf or xls file. I wish to give the user the functionality to filter the report prior to emailing it - ie, make use of a where clause so only the parts pertinent to the recipient is sent. The SendObject command does not have this as an argument. Any ideas?

    J.
    One solution could be to create a query using your code, and call the report using the new query. Eg:

    'Ensure the query does not allready exist:
    On Error Resume Next
    DoCmd.Close acQuery, "TempQuery"
    DoCmd.DeleteObject acQuery, "TempQuery"

    On Error Resume err_ShowReport
    'Build Query used be the report
    strSql=" Build your SQL here with or without where statement"
    Set qdf = db.CreateQueryDef("TempQuery", strSql)
    Set rst = qdf.OpenRecordset()
    If (rst.RecordCount > 0) Then
    'Show, print, mail or what ever - the report
    DoCmd.OpenReport "GrpPkStillUsed", acViewPreview
    DoCmd.Hourglass False
    Else
    'No data for the user...
    End If
    rst.Close

    Hope it helps.
    Bent Cold

  3. #3
    Join Date
    Mar 2004
    Posts
    82
    Quote Originally Posted by BentCold
    One solution could be to create a query using your code, and call the report using the new query. Eg:

    'Ensure the query does not allready exist:
    On Error Resume Next
    DoCmd.Close acQuery, "TempQuery"
    DoCmd.DeleteObject acQuery, "TempQuery"

    On Error Resume err_ShowReport
    'Build Query used be the report
    strSql=" Build your SQL here with or without where statement"
    Set qdf = db.CreateQueryDef("TempQuery", strSql)
    Set rst = qdf.OpenRecordset()
    If (rst.RecordCount > 0) Then
    'Show, print, mail or what ever - the report
    DoCmd.OpenReport "GrpPkStillUsed", acViewPreview
    DoCmd.Hourglass False
    Else
    'No data for the user...
    End If
    rst.Close

    Hope it helps.
    Bent Cold
    Could you explain?
    I already have a query that runs the report. I also have a filter that pulls up a grouped form, where the choices are preview, print, and email. The first two work - ie, they pull up the filtered report to preview or prints it. Both use the DoCmd.OpenReport statement that allows a where clause. The email functionality uses DoCmd.SendObject which does not.
    Is there an alternative way of emailing that uses the filters to send the report running off of the identified query?
    Thanks,
    J.

  4. #4
    Join Date
    Apr 2004
    Location
    Denmark
    Posts
    2

    Alernate filter

    I asume you currently have a static query
    (fx "MyQuery" with a statement like "Select * from MyData"),
    and a report that uses "MyQuery"

    You then use something like
    Cmd.OpenReport "MyReport", acViewPreview, , "MyField = '" & MyData & "'"

    I.e you specify your selection criteria on the OpenReport command.
    I do not know how to do this with the sendObject command. (And don't think it is possible)

    But if you instead build the query dynamically for each report inclusive your required filtering data, you do not have to specify the filter on the open/SendObject command The filter is instead part of the query. Voila - your mail report is filtered. Example: (without ther error handling)

    strSql="SELECT * FROM MyData Where MyField = '" & MyData & "'"
    Set qdf = db.CreateQueryDef("MyQuery", strSql)
    DoCmd.OpenReport "MyReport", acViewPreview

    I hope that explains my idea a little better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •