Page 3 of 7 FirstFirst 1234567 LastLast
Results 31 to 45 of 100
  1. #31
    ingarchsf is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2014
    Posts
    50

    But.. the tutorial made only that..
    how can I export PDFs? I need them to be saved in single pages.. and the first CODE, the one in the first posts, doesn't work as it is..

  2. #32
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The video tutorial shows how to build a dynamic parameterized query that uses a form for inputs. Use the query as report RecordSource. Open report. Here is another from DataPig http://www.datapigtechnologies.com/f...mtoreport.html.

    The link referenced in first post shows opening report as well as the code you posted. The referenced link was a solved issue and provides good example of exporting to PDF.

    I am totally lost. If you don't want to provide db for analysis, I doubt I can help further.
    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. #33
    ingarchsf is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2014
    Posts
    50
    Ok, I've tried something else: instead of OpenQuery, I used OpenReport in Preview visualization.. and it opens the Report based on the query (crazy.. Report on Query ok, Query only ko.. pfff..!!

    Now I have to understand how to export each page of the Report to a single PDF.. using fields "Cognome" and "Data_modulo" as PDF's name!
    Any idea?

    I used the code posted in the first post here.. but it doesn't work.. ["Attestati_2014" is the Report, "Selezione_corso_2014" is the Query, "Cognome" is the first field that has to be put in the name of the PDF, "Data_modulo" is the second field that has to be put in the name of the PDF]

    Code:
    Private Sub Command0_Click()
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim strSQL As String
    Dim strRptName As String
    Dim count As Integer
    
    strRptName = "Attestati_2014"
    strSQL = "Select Selezione_corso_2014.[Cognome] From Selezione_corso_2014;"
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    With MyRS
    Do While Not MyRS.EOF
    DoCmd.OpenReport strRptName, acViewPreview, , "[Cognome]='" & ![Cognome] & "'"
    DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\Attestati\" & ![Cognome] & Format(!Data_modulo, "YYYYMMDD") & ".pdf", False
    DoCmd.Close acReport, strRptName, acSaveNo
    .MoveNext
    Loop
    
    End With
    MyRS.Close
    Set MyRS = Nothing
    End Sub

  4. #34
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Instead of
    strSQL = "Select Selezione_corso_2014.[Cognome] From Selezione_corso_2014;"

    try
    strSQL = "Select Selezione_corso_2014.[Cognome], Selezione_corso_2014.[Data_modulo] From Selezione_corso_2014;"

    or
    strSQL = "Select Selezione_corso_2014.[Cognome], [Data modulo] From Selezione_corso_2014;"

    or
    strSQL = "Select Selezione_corso_2014.[Cognome], Selezione_corso_2014.[Data_modulo] From Selezione_corso_2014;"

    I placed three options here because I am not positive how Data_modulo is spelled in the table, and which table/query it resides, and if other tables/queries within your query have the same field name.

  5. #35
    ingarchsf is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2014
    Posts
    50
    Uhm.. I've tried, but, as before, the error is "Runtime error 3061".. not enough parameters, 3 needed..

    I think that my edits on the code, which was made for only 1 field in the name, don't work.. but I don't know how to edit it to use 2 fileds for the name of the PDF..

    "Cognome" is a text field, "Data_modulo" is a date field.

  6. #36
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Do you get an option to Debug when the error happens? If you choose Debug, what line of code throws the exception?

    If the form is open and the form's fields contain values, can you open the report named, "Attestati_2014", without any errors?

  7. #37
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I also remembered one other issue. If you could answer my previous questions it will be helpful. An additional comment I would like to make is, you cannot (without some fancy footwork) use Dynamic Parameterized Queries in DAO Recordsets.

  8. #38
    ingarchsf is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2014
    Posts
    50
    well.. I need to correct what I wrote: the error occurs just before, in opening the Form that has the parameters used to make the selection on the query on which is based the report. So no debug..
    The error is "Return without GoSub".

    When I open the Form, 3 popups are shown, asking the 3 parameters.. if i enter a value or nothing, the error occurs.. (so the problem is in the code for exporting PDFs).

    Before was all ok with the Report, so..

    If the form is open and the form's fields contain values, can you open the report named, "Attestati_2014", without any errors?
    -> no error: the report is based on a query selected by parameters inside the form.

    An additional comment I would like to make is, you cannot (without some fancy footwork) use Dynamic Parameterized Queries in DAO Recordsets.
    I don't know.. that's why I need help.. I don't know how to edit the code to fit my need and situation..

  9. #39
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by ingarchsf View Post
    ...The error is "Return without GoSub"...
    It sounds as though you have many issues. I suggest you get your form to open properly and then worry about other stuff.

    I would start by looking at the code behind the form. Make sure that you have a Procedure Header and Signature for each Event handler, Function, Sub that is behind your form.

    Glance over the code and look for obvious stuff. You should have a header line for each procedure
    Private Sub Command0_Click()

    You should have a signature for each procedure
    End Sub or End Function

    After looking at the VBA, compile you VBA using the Debugger>Compile Database. This should point out issues overlooked regarding the "Return without GoSub"

  10. #40
    ingarchsf is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2014
    Posts
    50
    I followed the tutorial..
    the code is the following

    Code:
    Option Compare Database
    Private Sub Comando14_Click()
    Me.qncorso1.Value = ""
    Me.qnmodulo1.Value = ""
    Me.qcognome1.Value = ""
    End Sub
    Private Sub Comando15_Click()
    DoCmd.OpenReport "Attestati_2014", acViewPreview
    End Sub
    Private Sub Command0_Click()
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim strSQL As String
    Dim strRptName As String
    Dim count As Integer
    strRptName = "Attestati_2014"
    strSQL = "Select Selezione_corso_2014.[Cognome], Selezione_corso_2014.[Data_modulo] From Selezione_corso_2014;"
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    With MyRS
    Do While Not MyRS.EOF
    DoCmd.OpenReport strRptName, acViewPreview, , "[Cognome]='" & ![Cognome] & "'"
    DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\Attestati\" & ![Cognome] & Format(![Data_modulo], "YYYYMMDD") & ".pdf", False
    DoCmd.Close acReport, strRptName, acSaveNo
    .MoveNext
    Loop
    End With
    MyRS.Close
    Set MyRS = Nothing
    End Sub
    Private Sub Form_Current()
    End Sub
    Now, no more error "Return without GoSub".. but Runtime error 3061, with debug at line "Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)"


    The form is ok, also June7 has said. And the Report through the Form works as I need.
    What has to be built is the CODE for exporting each page of the obtained Report with the name that contains the fields "Cognome" and "Data_modulo"

  11. #41
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well blast! Even dynamic parameters that reference controls on form won't work. Now I remember why I NEVER use dynamic parameters in queries.

    Will have to capture the parameters with VBA and include them in the SQL statement to open recordset.

    strSQL = "Select Selezione_corso_2014.[Cognome], Selezione_corso_2014.[Data_modulo] From Selezione_corso_2014 WHERE [some field name]='" & Me.controlname & "';"

    Cannot use OutputTo method to send each page of report to individual PDF. The entire report will export as a multipage PDF. So I am guessing the filter criteria for Cognome results in a single page report.

    What is the exact message of error 3061? I don't see anything wrong with code.
    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.

  12. #42
    ingarchsf is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2014
    Posts
    50
    I've not understood everything, ahaha.. but I guessed the general meaning!

    Well.. so, if I use a query, on which I've made a selection by parameters, I won't ever be able to export each page as a single PDF, will I?

    The error is: "Runtime error '3061'. Not enough parameters. 3 needed"

  13. #43
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, that error is because of the dynamic parameters in query. It is now established that a recordset cannot have a dynamic parameterized query as source, regardless of how the parameters are input.

    Remove the dynamic parameters from query. Use VBA to capture the inputs and concatenate as shown in example.

    Does report filtered by ![Cognome] result in a single page?
    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.

  14. #44
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ingarchsf View Post
    Well.. so, if I use a query, on which I've made a selection by parameters, I won't ever be able to export each page as a single PDF, will I?

    I found some code to export reports to PDF. AFAICT, opens a recordset and loops through, exporting one record at a time. I have not tested the code yet. I will be adapting the code to use a multi-select list box to select just a few records to output to PDF.

    At the risk of jumping too far ahead, when you get your query working, substitute your query name, report name, criteria and save location in the following code. (You will probably have to modify the code)
    Code:
    'Private Sub Command0_Click()
        Dim MyDB As DAO.Database
        Dim MyRS As DAO.Recordset
        Dim strSQL As String
        Dim strRptName As String
        Dim count As Integer
    
    
        strRptName = "FieldReconnFormReport"
        strSQL = "Select RptQry_List_Table_For_Entech_Use.[FacilityID] From RptQry_List_Table_For_Entech_Use;"
    
        Set MyDB = CurrentDb
        Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
        With MyRS
    
            Do While Not MyRS.EOF
    
                DoCmd.OpenReport strRptName, acViewPreview, , "[FacilityID]=" & ![FacilityID]
                DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\Temporary FR Forms\" & ![FacilityID] & "_FRECON.pdf", False
                DoCmd.Close acReport, strRptName, acSaveNo
                .MoveNext
            Loop
    
        End With
    
        MyRS.Close
        Set MyRS = Nothing
    
    'End Sub

  15. #45
    ingarchsf is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2014
    Posts
    50
    Quote Originally Posted by ssanfu View Post
    I found some code to export reports to PDF. AFAICT, opens a recordset and loops through, exporting one record at a time. I have not tested the code yet. I will be adapting the code to use a multi-select list box to select just a few records to output to PDF.

    At the risk of jumping too far ahead, when you get your query working, substitute your query name, report name, criteria and save location in the following code. (You will probably have to modify the code)
    Code:
    'Private Sub Command0_Click()
         Dim MyDB As DAO.Database
         Dim MyRS As DAO.Recordset
         Dim strSQL As String
         Dim strRptName As String
         Dim count As Integer
    
    
         strRptName = "FieldReconnFormReport"
         strSQL = "Select RptQry_List_Table_For_Entech_Use.[FacilityID] From RptQry_List_Table_For_Entech_Use;"
    
         Set MyDB = CurrentDb
         Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
         With MyRS
    
             Do While Not MyRS.EOF
    
                DoCmd.OpenReport strRptName, acViewPreview, , "[FacilityID]=" & ![FacilityID]
                 DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\Temporary FR Forms\" & ![FacilityID] & "_FRECON.pdf", False
                 DoCmd.Close acReport, strRptName, acSaveNo
                 .MoveNext
             Loop
    
         End With
    
         MyRS.Close
         Set MyRS = Nothing
    
     'End Sub
    Thank you! But.. that's the starting point.. the thread began from that code! Thank you by the way!!!!


    Quote Originally Posted by June7 View Post
    Okay, that error is because of the dynamic parameters in query. It is now established that a recordset cannot have a dynamic parameterized query as source, regardless of how the parameters are input.

    Remove the dynamic parameters from query. Use VBA to capture the inputs and concatenate as shown in example.

    Does report filtered by ![Cognome] result in a single page?
    What do you mean with "use VBA"? Which example? Do you mean the one of the video?

    about the filtered report: if I filter the report only by "Cognome", many pages appear because what identify each page is the combination of "Cognome" and "Data_modulo".. so 1 page for each "Cognome"+"Data_modulo"

Page 3 of 7 FirstFirst 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-04-2014, 01:25 PM
  2. Export as PDF
    By lugnutmonkey in forum Import/Export Data
    Replies: 1
    Last Post: 02-19-2013, 11:23 AM
  3. Export to Word
    By Monterey_Manzer in forum Import/Export Data
    Replies: 0
    Last Post: 12-20-2012, 02:06 PM
  4. CSV Export
    By mchadwick in forum Access
    Replies: 1
    Last Post: 09-06-2011, 03:19 PM
  5. Export
    By vvasudev in forum Import/Export Data
    Replies: 2
    Last Post: 01-20-2010, 11:42 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