Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering a report (XP)

    Hi,

    I am writing an interface where the user can select a report and then apply a filter and request an output type. Most of the code is done but i am having trouble adding the filter to the report where the output type is snapshot or email.

    The working code i am using is :
    DoCmd.OpenReport stDocName, acPreview, , strWhere

    how do i get the strWhere value into the line for the snapshot ...
    DoCmd.OutputTo acOutputReport, stDocName, acFormatSNP, " h:" & stDocName & ".snp"

    or the email item
    DoCmd.SendObject acSendReport, stDocName, acFormatRTF, , , , "Information Report : " & Now(), "Please find attached the requested report.", -1

    TIA

    Fatherjack

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a report (XP)

    Both DoCmd.OutputTo and DoCmd.SendObject don't use the already opened, filtered report, but open a new, unfiltered copy. Look at the thread starting at <post#=375863>post 375863</post#> and the links provided in the replies for ideas how to export/send a filtered report.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a report (XP)

    Hans,
    thanks for the response, it wasnt what i was expecting so i did a bit more digging around in the VBA help in Access (is it me or is the help in XP worse than that in 97????) and i have cobbled together this :
    Select Case fraOutput
    Case 1 ' Preview
    DoCmd.OpenReport strRepName, acPreview, , strWhere

    Case 2 ' Snapshot file
    With DoCmd
    ' open the report
    .OpenReport strRepName, acViewDesign, , , acHidden
    ' change the filter so that we only get the records we want
    Reports(strRepName).Filter = strWhere
    ' save changes
    .Save acReport, strRepName
    ' output the report
    .OutputTo acOutputReport, _
    strRepName, _
    acFormatSNP, _
    " C:" & strRepName & ".snp"
    ' re-open report
    .OpenReport strRepName, acViewDesign
    ' remove filter
    Reports(strRepName).Filter = ""
    ' re-save the report
    .Save acReport, strRepName
    ' close report, with save , just to make sure
    .Close acReport, strRepName, acSaveYes
    End With

    Case 3 ' email
    With DoCmd
    ' open the report
    .OpenReport strRepName, _
    acViewDesign, , , acHidden
    ' change the filter so that we only get the records we want
    Reports(strRepName).Filter = strWhere
    ' save changes
    .Save acReport, strRepName

    ' output the report
    .SendObject acSendReport, _
    strRepName, _
    acFormatRTF, , , , _
    "ConnectIt Report : " & Format(Now(), "dd/mmm/yyyy"), _
    "Please find attached the ConnectIt report.", -1
    ' re-open report
    .OpenReport strRepName, acViewDesign
    ' remove filter
    Reports(strRepName).Filter = ""
    ' re-save the report
    .Save acReport, strRepName
    ' close report, with save , just to make sure
    .Close acReport, strRepName, acSaveYes
    End With
    Case Else
    MsgBox "This function is not yet available", vbOKOnly + vbCritical, "Error"
    End Select
    (I know the code is ugly and i could pick out the repeated lines into seperate subs but this is a quick fix!!!)

    It works OK apart from the snapshot version which errors with :
    The report snapshot was not created because you dont have enough free disk space for temporary work files.

    There is 512Mb RAM, 3Gb HD space - any ideas? This happens on all reports i have tried, regardless of their size (one was 126 pages, another was 517) they all count up the pages but then error before output.

    thanks

    Fatherjack

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a report (XP)

    The online help seems to be getting harder to use with each new version. All the information is there, but you must already know where it is in order to find it <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    This code can only be used in a single-user setting. If you have multiple users, you won't be able to open a report in design view, then modify and save it. (Also see your question in <post#=376055>post 376055</post#>)

    There are several possible explanations for the error you get:
    - You use the report name in the name of the snapshot file. Perhaps the report name contains characters that are not allowed in a file name.
    - You don't have sufficient permissions in the root folder C: (in some network environments, C: is protected to prevent users making the system unusable)
    - Your Temp folder contains very many files; it doesn't hurt to clean out Temp from time to time.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Filtering a report (XP)

    In reference to error msg, recommend see MSKB 226526:

    ACC: Error Message Using OutputTo to Output a Report to Another Format

    Article notes:

    "You may receive one of the errors that is mentioned in the "Symptoms" section if any one of the following is true of the OutputFile argument of the OutputTo action or method:

    - You did not specify a file name.

    - The file name that you supplied contains an invalid character (!@#$%^&*():[img]/forums/images/smilies/wink.gif[/img].

    - The path that you specify does not exist."

    To avoid error, specify file name, ensure no invalid characters, and that path exists. Access will let you name report with any number of "illegal" filename characters, such as "", which if used in filename would result in probable non-existent path. Recommend modify code to test for these conditions, and ensure report names do not include illegal characters.

    HTH

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a report (XP)

    same code as in previous poct but with an added line of:
    strFileName = "C:" & Replace(strRepName, " ", "") & ".snp"

    and a change to this line:
    .OutputTo acOutputReport, strRepName, acFormatSNP, strFileName

    looks like the spaces in the name were the problem. Thanks for all the help.

    Hans, re your points, This report is sitting in the front end part of a multi user database so they will be adding and removing the filter on an individual copy of the report so i dont think there will be any issues - am i correct? I was sharing development with someone, we only have a single dev vopy of the front end ....


    thanks again

    Fatherjack

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a report (XP)

    If your users have individual copies of the front end, modifying the design of the report will not cause a conflict.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering a report (XP)

    It won't cause problems. The filters are temporary conditions, not design changes, and each user is working with the copy of the database loaded in RAM on their machine when they run the report. So each is running a virtual instance of the application. Filters and Where conditions apply to their instance, but design changes won't be allowed unless there is only one user in the database.
    Charlotte

Posting Permissions

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