Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113

    acViewPreview Vs acViewNormal

    Hello all,


    My problem today is as follows: My code is

    Code:
     DoCmd.OpenReport "EXInvoiceReport",acViewPreview , , "InvoiceNumber=" & CurrentInvoice
    When I use the acViewPreview gives the single record Invoicenumber correctly, However when I use the acViewNormal, the end result is the Invoice that I want plus all the other records in the recordset. The problem is that I don't want to preview all the reports that I have to print. So is there any code that could filter the data the same that the acViewPreview Does? Thank you for your help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The report should apply filter same way for either output parameter.

    What is CurrentInvoice - textbox or fieldname or VBA variable?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    Hello June7, The CurrentInvoice is a VBA variable.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Post your entire procedure code or provide db for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    My Procedure Code is as follows:
    Code:
    Private Sub PrintInvoices_Click()
    If IsNull(Me.CboInvoiceNumberFrom) Or IsNull(Me.CboInvoiceNumberTo) Or (Me.CboInvoiceNumberFrom > Me.CboInvoiceNumberTo) Then
    MsgBox "Enter Correct Criteria"
    Exit Sub
    End If
    
    Dim CurrentInvoice As Integer
    CurrentInvoice = Me.CboInvoiceNumberFrom
    Do Until CurrentInvoice = CboInvoiceNumberTo + 1
    If DSum("EX", "Orders", "InvoiceNumber=" & CurrentInvoice) > 0 Then
        If DSum("Discount", "Orders", "InvoiceNumber=" & CurrentInvoice) > 0 Then
            DoCmd.OpenReport "EXInvoiceReport", acViewPreview, , "InvoiceNumber=" & CurrentInvoice
            DoCmd.OutputTo acOutputReport, "EXInvoiceReport", acFormatPDF, "C:\Users\User\Documents\CustomerInvoices\" & CurrentInvoice & ".pdf", False
            DoCmd.PrintOut acPrintAll
            DoCmd.Close acReport, "EXInvoiceReport"
        Else
            DoCmd.OpenReport "EXInvoiceReportNoDisc", acViewPreview, , "InvoiceNumber=" & CurrentInvoice
            DoCmd.OutputTo acO, utputReport, "EXInvoiceReportNoDisc", acFormatPDF, "C:\Users\User\Documents\CustomerInvoices\" & CurrentInvoice & ".pdf", False
            DoCmd.PrintOut acPrintAll
            DoCmd.Close acReport, "EXInvoiceReportNoDisc"
        End If
    Else
       If DSum("Discount", "Orders", "InvoiceNumber=" & CurrentInvoice) > 0 Then
            DoCmd.OpenReport "InvoiceReport", acViewPreview, , "InvoiceNumber=" & CurrentInvoice
            DoCmd.OutputTo acOutputReport, "InvoiceReport", acFormatPDF, "C:\Users\User\Documents\CustomerInvoices\" & CurrentInvoice & ".pdf", False
            DoCmd.PrintOut acPrintAll
            DoCmd.Close acReport, "InvoiceReport"
        Else
            DoCmd.OpenReport "InvoiceReportNoDisc", acViewPreview, , "InvoiceNumber=" & CurrentInvoice
            DoCmd.OutputTo acOutputReport, "InvoiceReportNoDisc", acFormatPDF, "C:\Users\User\Documents\CustomerInvoices\" & CurrentInvoice & ".pdf", False
            DoCmd.PrintOut acPrintAll
            DoCmd.Close acReport, "InvoiceReportNoDisc"
        End If
    End If
            CurrentInvoice = CurrentInvoice + 1
    Loop
            DoCmd.Close acForm, Me.Name
    End Sub
    I am trying to make pdf and printing at the same time without Previews for the whole range of invoices. Maybe you know of a cleaner way of doing this. Thank you for your help.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So if you change the code to acViewNormal, it prints just the desired record but the PDF output has all records?

    I don't think you can get filtered PDF output without preview unless you make the report RecordSource a dynamic parameterized query. So instead of passing the InvoiceNumber in the OpenReport argument, the query parameter would reference control on form, something like:

    SELECT * FROM Invoices WHERE InvoiceNumber=Forms!formname!tbxInvoice;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    Yes, that is the problem... So I just don't know how to make a dynamic parameterized query. I learned something about parameter queries but what makes them dynamic? On the internet all I found is how to make parameter queries, but they don't mention anything about being dynamic. Could you please shed some light on this subject as I am new at this? . Thank you for your help

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I show a dynamic parameterized query in previous post. It's dynamic because the filter criteria is not static, it is dependent on some input by the user. In the example, that input is provided through a control on a form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    I see, so First I have to create a "formname" with two unbound controls for the start and end invoice#. Then when I make the DoCmd.OpenReport "EXInvoiceReport", acViewNormal, , "SELECT * FROM Invoices WHERE InvoiceNumber=Forms!formname!tbxInvoice;" It should get the value from the Form. Is that correct?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No.

    For one thing, the WHERE CONDITION argument of OpenReport would not be a full SQL statement.

    For another, the query would be the RecordSource of report. All the code would do is open the report without any WHERE CONDITION.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    I think that the parameterized query is way above what I can do for now... So I will just put up with the Preview and close it. Thank you for all your help

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Interesting, dynamic parameterized query is what most novices start with, long before they learn coding with macros or VBA. Somehow you leaped over that stage.

    The query would be simply as I show in post 6. Use the query builder to help.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't have the problem you describe because of the way I generate the PDF.

    My report has a query as the row source. The query has criteria it gets from a form.
    You have the criteria in the VBA code and have to open the report to get the data.

    Here is the relevant code for my method (Export as PDF button):
    Code:
                            For Each varItm In ctl.ItemsSelected
                                tmpLast = vbNullString
                                tmpFirst = vbNullString
                                tmpMI = vbNullString
                                Me.ubEndPK = vbNullString
    
                                tmpLast = ctl.Column(2, varItm)
                                tmpFirst = ctl.Column(3, varItm)
                                tmpMI = ctl.Column(4, varItm)
                                Me.ubEndPK = ctl.ItemData(varItm)
                                strSaveFileName = sPath & "\" & tmpLast & "_" & tmpFirst & "_" & tmpMI & ".pdf"
                                DoCmd.OutputTo acOutputReport, Me.lstForm, acFormatPDF, strSaveFileName, False
                            Next varItm
    So I have a multi-select list box. The code loops through the selected items, putting the PK number into an unbound text box (ubEndPK) on the form.
    The report query gets the PK when the report is output to the PDF format.
    I have a separate button to display/print a report one at a time.

    It wouldn't be hard to modify your code and query to use this method.

  14. #14
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    Well Thank you both for your help.
    I have tried the parameterized query and I have a few questions... To see if I get it. Your SQL was
    SELECT * FROM Invoices WHERE InvoiceNumber=Forms!formname!tbxInvoice;
    So I am thinking that "Invoices" is the table where I have the InvoiceNumber field, which in my case would be table Orders. Then The "Formname", should be the name of the form that I made for the user to input the data. And "tbxInvoice" Should be the values entered by the user? Then I name this query something like QrySelectingParameters and make it the record source for the Report. All the reports above are based on the same query Namely "InvoiceBody". So how do I hook both queries or should I add all the fields needed for the report to this new query?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What 'all' reports? If there is more than one report object based on this same query, do you want them all filtered to a single invoice?

    I gave you an example of parameterized query that pulls data from table. You can use your existing InvoiceBody query and modify it to include the dynamic parameter.

    Options for the query:

    1. save a query object with dynamic parameter and reference it in the RecordSource

    2. build the SQL statement with dynamic parameter directly in the RecordSource property

    In either case, this query can pull data from table or from another query (such as your InvoiceBody)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-15-2014, 07:22 PM
  2. acViewPreview clearing form of data in textboxes
    By Markb384 in forum Programming
    Replies: 9
    Last Post: 03-07-2014, 10:06 AM
  3. Help, acViewPreview not executing as expected
    By justphilip2003 in forum Access
    Replies: 3
    Last Post: 05-08-2013, 04:23 PM
  4. acViewPreview command suddenly not working
    By kelann in forum Reports
    Replies: 2
    Last Post: 10-16-2012, 07:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums