Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Output to Filtered Snapshot Report (Access XP)

    Hi

    Hope someone can help.

    I am trying to produce a report in snapshot view, but wish to only produce the report for one record. I have previously created the output print preview report as follows:

    DoCmd.OpenReport strRepName, acViewPreview, strFilter, strWhere, acWindowNormal

    the StrWhere is:

    strWhere = "Assetno = '" & Form_FrmAssets.txtAssetNo & "' "

    The code I have used for the snapshot view is :

    DoCmd.OutputTo acOutputReport, strDocName, acFormatSNP, "h:" & txtAssetNo.Value & ".snp"

    However I don't know where to put the StrWhere so that I can filter it. This works but it produces a file holding every record in my table when I only want to produce the snapshot for the selected record.

    If you'd like any further information please let me know.

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Output to Filtered Snapshot Report (Access XP)

    The DoCmd.OutputTo method does not export the filtered report as displayed on screen, but opens the report directly from the database window, ignoring your Where-condition.

    To get around this, you must put the Where-condition in the record source of the report. If you want to be able to open the report by itself too, you will want to make a copy of the report for this. If the report is based on a table, create a query based on this table, and set the criteria line for AssetNo to Forms!frmAssets.txtAssetNo, then use this query as record source of the report. If the record source is a query already, just add the criteria as described above (if you prefer, do this in a copy of the query.)

  3. #3
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to Filtered Snapshot Report (Access XP)

    Many thanks, that's brilliant, was hoping to use VB but have put it in the query and it works fine.

    Thank you very much.

  4. #4
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Output to Filtered Snapshot Report (Access XP)

    I have a a similar problem in that I have a list of about 1,000 classmates that I want to publish as a web page. This code does just that perfectly.

    DoCmd.OutputTo acReport, "rptCreateClassmateList", "MS-DOSText(*.txt)", "C:ClassmateList.htm", False, "", 0

    I have a form with a command button whose code that I want to create a separate page each time the first letter of the last name changes. So I would end up with 26 pages total (Example:ClassmateList_A.htm, ClassmateList_B.htm, etc.). The code above generates all the names on one page. The detail area of the report has a field with the following code if this helps: =Trim(' ' & [FullName] & "
    "). I have a sub report for the Report Header and Footer that contains the necessary html begiining and ending page code.

    I could of course create 26 different reports using (Like "a*") as the Filter value changing the letter for each. Then just create a line of OutPutTo code for each.

    Isn't there a way to feed a Filter value to the OutputTo code? If not, how about some code to modify the reports Filter value so I won't have to create 26 reports?

    Do you or does any one else have a more streamlined approach? I see web pages like this everywhere, so I have to believe that someone has already invented this wheel.

    Thank you,
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

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

    Re: Output to Filtered Snapshot Report (Access XP)

    Group the report by the first letter of the last name, and specify Before Section in the Page Break property of the group header. Then export to HTML. That automatically creates a separate HTML file for each page in the report:

    DoCmd.OutputTo acOutputReport, "rptCreateClassmateList", acFormatHTML, "C:ClassmateList.htm"

    (You can safely omit the last arguments, since you provided the default values anyway)

  6. #6
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Output to Filtered Snapshot Report (Access XP)

    Thank you for your quick response. I have a few questions.

    1) Will your suggestion work with the "MS-DOSText(*.txt)" type rather then acFormatHTML. It has been my limited experience that if the html is chosen it produces its' own htm heading code, etc. I need to use all of my own html code because the page has to be formatted with the proper FrontPage theme. It also creates a new html page for each page of the report.
    2) How do I tell it to group on the first letter of the last name? Would I have to move the fields from the report detail to the newly created last name group detail? What choices do I make in the "Sorting and Grouping" properties box?
    3) I noticed that you used the "DoCmd.OutputTo acOutputReport" syntax and I used "DoCmd.OutputTo acReport". Is there different results for each?
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

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

    Re: Output to Filtered Snapshot Report (Access XP)

    1) No, it will not work with output to a text file, it would create one file, even if the extension is .htm.
    2) You can specify that you want to group on one or more prefix characters in the Sorting and Grouping window (see screenshot.)
    3) acOutputReport and acReport both have value 3; there is no difference in the result. IntelliSense automatically popped up constants starting with acOutput on my system when I typed DoCmd.OutputTo.

    I will try to post a solution later, I don't have enough time now. Perhaps somebody else will help in the meantime.
    Attached Images Attached Images
    • File Type: png x.PNG (10.0 KB, 0 views)

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

    Re: Output to Filtered Snapshot Report (Access XP)

    You could do the following:
    <UL><LI>Create an unbound form frmExport with a text box txtLetter and a command button cmdExport. Set the Visible property of the text box to No.
    <LI>Create a query based on your classmates table, with the fields you need for your report. Set the criteria for the last name field to

    Like [Forms]![frmExport]![txtLetter] & "*"

    <LI>Set the Record Source of the report to the name of this query.
    <LI>Put the following code in the On Click event of cmdExport on the form:

    Private Sub cmdExport_Click()
    Dim i As Long
    For i = 1 To 26
    Me.txtLetter = Chr(64 + i)
    DoCmd.OutputTo acOutputReport, "rptCreateClassmateList", acFormatTXT, "C:ClassmateList_" & Me.txtLetter & ".htm"
    Next i
    End Sub

    acFormatTXT is the symbolic constant for "MS-DOSText(*.txt)"[/list]The code will successively fill the text box with the letters A through Z. When the report is output, the query is opened, and this picks up the letter from the form to use in the criteria.

  9. #9
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Output to Filtered Snapshot Report (Access XP)

    Once again I truely appreciate all your help. I did as you suggested. What I'm getting is the 26 files with each containing the correct names. However,
    after about a dozen or so names, which is about the amount that will fit on a page between the text contained in my top and bottom sub reports headers, the Page Header and Footer are reprinting. So the TopOfPage and BottomOfPage sub reports print multiple times on a page. I created Word doc of screen shots for your review but it was too large to post so you can access it at the following link: http://home.earthlink.net/~ftpfiles/
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

  10. #10
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Output to Filtered Snapshot Report (Access XP)

    Well I solved my own problem from my last post. I had the sub reports placed in the PAGE Header and Footer, rather then the REPORT Header and Footer. This portion works beautifully now!!! THANK YOU!!!!

    The only open issue I have is now the numbering starts over with each page, rather then continuing where the sequence left off on the previous page. Any ideas on how get the correct number back?
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

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

    Re: Output to Filtered Snapshot Report (Access XP)

    The following works in my test situation, but I recommend testing thoroughly on your database (or rather a copy of it):
    <UL><LI>Add another text box txtNumber to the frmExport form. Set its Visible property to False.
    <LI>Add the following line to cmdExport_Click above For i = 1 To 26 to initialize txtNumber:

    Me.txtNumber = 0

    <LI>Use the On Print event of the page header of the report to increment txtNumber:

    Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
    Forms!frmExport!txtNumber = Forms!frmExport!txtNumber + 1
    End If
    End Sub

    <LI>Instead of the standard page number, use a text box in the page footer of the report with control source

    =[Forms]![frmExport]![txtNumber][/list]You could also use a global variable instead of the text box txtNumber.

  12. #12
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Output to Filtered Snapshot Report (Access XP)

    I'm getting weird results. The first page is ok. The Page Footer for each of the 26 pages returns 2 for the first page, 4 for the second page, 6 for the third page, etc.

    I used my txtLineNumber box to reference the txtCount box I added to the Page Footer. The first line of the second page starts at 3 and increments in 3's. The first line of the third page starts with 5 and increments by 5's. The first line of the 4th page starts with 7 and increments by 7. The first line of the fith page.... etc.?
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

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

    Re: Output to Filtered Snapshot Report (Access XP)

    I don

  14. #14
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Output to Filtered Snapshot Report (Access XP)

    I have two fields in the detail area of the report as follows:
    -- txtLineNumber with control source set to "=[txtCount]"
    -- txtName with control source set to "=Trim(' ' & [FullName] & "
    ")"

    Then I have txtCount control source in the Page Footer set to "=Forms!frmExport!txtNumber"
    The Page Header has the code you supplied. frmExport has the txtNumber field and code you supplied.
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

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

    Re: Output to Filtered Snapshot Report (Access XP)

    I'm sorry, there must be something we don't know yet. I don't think we can help further without seeing a stripped down version of the database. If possible:
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it. (Of course, this only helps if you're using Access 2000 or later.)
    <LI>Attach the zip file to a reply.[/list]Thanks.

Page 1 of 2 12 LastLast

Posting Permissions

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