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..
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..
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.
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
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.
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.
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?
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.
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..
-> no error: the report is based on a query selected by parameters inside the form.If the form is open and the form's fields contain values, can you open the report named, "Attestati_2014", without any errors?
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..An additional comment I would like to make is, you cannot (without some fancy footwork) use Dynamic Parameterized Queries in DAO Recordsets.
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"
I followed the tutorial..
the code is the following
Now, no more error "Return without GoSub".. but Runtime error 3061, with debug at line "Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)"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
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"
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.
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"
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.
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!!!!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
What do you mean with "use VBA"? Which example? Do you mean the one of the video?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?
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"