Leban's Report to PDF question

[JessicaZ]

Hi everyone! I'm new here and have a question regarding Leban's report to pdf tool. I am working on a database where we are using this code to send a report out to pdf(duh) BUT what I need to do is a bit more complicated.

Basically the db keeps track of stock in various locations. Each month, a report must be run for the 76 locations and put into pdf so the reports can be put onto sharepoint .

I would like the database to loop through the various AreaID codes and create a pdf report for each area using the AreaName as the report name. The report is being selected from a list box and when we click on the create pdf button, the pdf is being created, but is being named the report name rather than the area name. (I dont have it looping yet as I would like to be sure I can get one report saved correctly before I do 76!)

The part of the code that refers to what to name the pdf is this:
Me.lstRptName.V alue & ".pdf"

my question (for now) is how to access the AreaName from the report shown in the list box and use that to name the pdf?

Thanks!

Jessica

[Stewart Ross]

Hi Jessica, and Welcome to Bytes!

If you don't have the areaname in the listbox at present the simplest way to get it passed as the name of the pdf file (it really it is not possible to get it from the report itself, which is not open at that stage) is to include it in the query which is the source for the listbox, then set the column width for that column to 0 so it is not shown to the user.

You can use the Column property of the listbox to refer to a different column than the default. In a combo or listbox the columns are numbered from 0, with Column (0) the default (first) column, column (1) the second and so on.

You don't say which column your areaname is in (if indeed it is already in the listbox). If I assume it is the second column, you would obtain the areaname from your reportname listbox as follows:

Me.lstRptName.C olumn(1) & ".pdf"

If in fact the areaname is in some other column, just substitute the correct column number in place of the 1 shown, remembering that the numbering starts at 0.


-Stewart

[JessicaZ]

Hi Jessica, and Welcome to Bytes!

If you don't have the areaname in the listbox at present the simplest way to get it passed as the name of the pdf file (it really it is not possible to get it from the report itself, which is not open at that stage) is to include it in the query which is the source for the listbox, then set the column width for that column to 0 so it is not shown to the user.
-Stewart

Thanks Stewart
I'm not sure this will work as it is my intent to run 76 pdf's off of this one report. This is what I am thinking:

open a recordset with a list of all the AreaIDs
loop through each area ID, using that area as the parameter/filter for the report
have each report go through Leban's pdf code and turn it into a pdf with the AreaName as the file name.

Is there an easier way to do this?

I should also mention that the report also has a parameter for the ReportDate (month and year) so as it stands now, I click the button and the report asks me to input the ReportDate and AreaID then outputs the report as a pdf (which currently comes up with the reportname as the file name, so all reports are just named OpCoReport.pdf and the next one I run would overwrite it). Because there are 76 areas I have to either repeat this manually 76 times (and rename each pdf before I do the next) or take out the areaID parameter and run one large report which also doesnt work because the I would still have to split it up into 76 individual ones.

Thanks for your help!
Jessica

[Stewart Ross]

Hi Jessica. The approach you are suggesting seems fine to me. Create a new query based upon your report's data source query that is grouped to list the unique area names, then open that query as a recordset in code and loop through all area names just as you suggest, using these (and any dates etc) to filter your reports. You should remove the AreaID and report date parameter prompts from the report's underlying source query - to stop these being prompted for on every pass. If these values must be different for every report you run you will need to think of some way to pass the relevant value to the 76 report instances, but if it is the same value every time you should place unbound controls on the form from which you will run the reports and refer to these in your code when filtering the reports.

I would also alter the Lebans code to pass the name of the report as an optional parameter - being optional, the Lebans code could function as now when the parameter is not supplied, substituting the given report name only when the parameter has a specified value.

I do not have the Lebans code in front of me to guide you where to place the parameter and insert an IF to check its value - you will need to do that yourself.

Optional parameters come last in the argument list of VBA routines:

Public Sub SomeStuff (param1 as type1, param2 as type 2, Optional ByVal newreportname as string = "")

-Stewart

[ADezii]

Hello Stetwart, Jessica, sorry for jumping in late. I created a General Code Template which hopefully will point you in the right direction, and may actually work (LOL). The following code, at least in Theory, will:

1. Create a Recordset consisting of Unique User IDs.
2. Loop through this Recordset.
3. Open your Base Report in Design/Hidden View.
4. Dynamically set the Record Source of the Report based on the Current Record Pointer in the Recordset ([UserID]).
5. Close/Save the Report.
6. Execute Mr. Leban's PDF Conversion Code on the Report for each and every Unique UserID.
7. Save the Report (*.pdf) as the strUserName.pdf for each [UserID]. strUserName is a Variable that derives its value from a DLookup() Function referencing the [UserID].
8. I have no way of actually testing this, but if you wish to try it, let me know how you make out.

Dim MyDB As DAO.Database
Dim rstUniqueIDs As DAO.Recordset
Dim strSQL As String
Dim strSQL_2 As String
Dim strUserName As String

Set MyDB = CurrentDb()

'*********************************************************************
'Create a Recordset consisting of Unique User IDs                    *
strSQL = "SELECT DISTINCT YourTableName.UserID FROM YourTableName;" '*
Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       '*
'*********************************************************************

With rstUniqueIDs
  If .BOF Or .EOF Then Exit Sub     'No Records
  Do While Not .EOF
    'Open your Report in Hidden/Design Mode, dynamically changing its Record
    'Source based on the ![UserID] value in the Recordset, then Save it!
    strSQL_2 = "Select * From YourTableName Where YourTableName.UserID " & _
               "= " & ![UserID]
    strUserName = DLookup("[UserName]", "YourTableName", "[UserID] = " & ![UserID])
    DoCmd.OpenReport "YourReportName", acViewDesign, , , acHidden
    Reports![YourReportName].RecordSource = strSQL_2
    DoCmd.Close acReport, "YourReportName", acSaveYes
      'Open and Convert Report to PDF Format here using the Leban's Code
      'Pass the File Name Parameter as: strUserName & ".pdf"
       .MoveNext
  Loop
End With

rstUniqueIDs.Close
Set rstUniqueIDs = Nothing

P.S.:

1. Substitute your actual Table Name for 'YourTableName'
2. Substitute your actual Report Name for 'YourReportName '
3. The code assumes [UserID] is a Numeric Value.
4. The code assumes each and every [UserID] has a unique [UserName] associated with it.
5. Obviously, there cannot be no Prompts in the Record Source for the Report, as previously indicated by Stewart.

[JessicaZ]

Thsanks! Ill take a look at this and let you know what happenes, but I may not get back to it until Monday.

Jessica

[ADezii]

Thsanks! Ill take a look at this and let you know what happenes, but I may not get back to it until Monday.

Jessica

Absolutely no rush. As previously stated, it's just designed to be a Template, make your own revisions as needed, and let us know how you make out.

[ADezii]

Just as a side note, if there is a Unique correllation between [UserID] and [UserName], such as that indicated below, the code can be revised to contain [UserName] in the Recordset itself which would be more efficient (see Lines 11, 22, and 27):

UserID	UserName
  3	   Leverling
  4	   Peacock
  9	   Dodsworth
  4	   Peacock
  8	   Callahan
  6	   Suyama
  1	   Davoliol
  2	   Fuller
  6	   Suyama
  7	   King
  5	   Buchanan
  9	   Dodsworth
  9	   Dodsworth

Dim MyDB As DAO.Database
Dim rstUniqueIDs As DAO.Recordset
Dim strSQL As String
Dim strSQL_2 As String
Dim strUserName As String
  
Set MyDB = CurrentDb()
  
'*************************************************  ********************
'Create a Recordset consisting of Unique User IDs
strSQL = "SELECT DISTINCT YourTableName.UserID, YourTableName.UserName FROM YourTableName;" '*
Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       '
'*************************************************  ********************
  
With rstUniqueIDs
  If .BOF Or .EOF Then Exit Sub     'No Records
  Do While Not .EOF
    'Open your Report in Hidden/Design Mode, dynamically changing its Record
    'Source based on the ![UserID] value in the Recordset, then Save it!
    strSQL_2 = "Select * From YourTableName Where YourTableName.UserID " & _
               "= " & ![UserID]
    strUserName = ![UserName]
    DoCmd.OpenReport "YourReportName", acViewDesign, , , acHidden
    Reports![YourReportName].RecordSource = strSQL_2
    DoCmd.Close acReport, "YourReportName", acSaveYes
      'Open and Convert Report to PDF Format here using the Leban's Code
      'Pass the File Name Parameter as: strUserName & ".pdf"
       .MoveNext
  Loop
End With
  
rstUniqueIDs.Close
Set rstUniqueIDs = Nothing
Set rstUniqueIDs = Nothing

[JessicaZ]

Edited: Hold on, I think I got it.........

Stay tuned
Thanks!

Jessica

[JessicaZ]

OK, its working but with a couple minor problems.

Here is the code

Private Sub btnPDFOpCoRpt_Click()

If IsNull(Me.cboDates) Then
  MsgBox "Please choose a Date for the Report."
  Me.cboDates.SetFocus
  Cancel = True
Else
  MsgBox "Press ctrl-Break to pause or stop the report."
 
  
Dim MyDB As DAO.Database
Dim rstUniqueIDs As DAO.Recordset
Dim strSQL As String
Dim strSQL_2 As String
Dim strAreaName As String

Set MyDB = CurrentDb()

'*************************************************    ********************
'Create a Recordset consisting of Unique User IDs
strSQL = "SELECT DISTINCT tblOpco.AreaID, tblOpCo.Opco FROM tblOpCo;" '*
Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       '
'*************************************************    ********************
  
With rstUniqueIDs
  If .BOF Or .EOF Then Exit Sub     'No Records
  Do While Not .EOF
    'Open your Report in Hidden/Design Mode, dynamically changing its Record
    'Source based on the ![UserID] value in the Recordset, then Save it!
    strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID]
    'And [OpCo Query].Description not like "*aka" And [OpCo Query].CategoryID <> 22

    strAreaName = ![OpCo]
    DoCmd.OpenReport "OpCo Report", acViewDesign, , , acHidden
   Reports![OpCo Report].RecordSource = strSQL_2
    DoCmd.Close acReport, "OpCo Report", acSaveYes
      'Open and Convert Report to PDF Format here using the Leban's Code
      'Pass the File Name Parameter as: strareaName & ".pdf"


' You must pass either RptName or SnapshotName or set the ShowSaveFileDialog param to TRUE.
' Any file names you pass to this function must include the full path. If you only include the
' filename for the output PDF then your document will be saved to your My Documents folder.

On Error GoTo err_blRet

Dim blRet As Boolean


' Call our convert function
' Please note the last param signals whether to perform
' font embedding or not. I have turned font embedding ON for this example.
blRet = ConvertReportToPDF("OpCo Report", vbNullString, Me.cboDates.Value & "- SYSCO Stocking Report -" & strAreaName & ".pdf", False, True, 150, "", "", 0, 0, 0)
' To modify the above call to force the File Save Dialog to select the name and path
' for the saved PDF file simply change the ShowSaveFileDialog param to TRUE.
       
       
  .MoveNext
  Loop
End With
  
rstUniqueIDs.Close
Set rstUniqueIDs = Nothing
Set rstUniqueIDs = Nothing
End If
Exit_blRet:
Exit Sub
err_blRet:
    MsgBox Err.Description
    Resume Exit_blRet
End Sub

I have a combo box(cboDates) on the form with the available date for the report - Right now there is just July 2008 and September 2008 in there. What is happening is I have July 2008 selected in the combo box and then run the report. When the report comes up, the title of the first report is named "July 2008 - Sysco Stocking Report - etc.pdf" BUT the report itself has September 2008 data in it. The rest of the reports all come up as July, but the first one doesn't.

The second thing is that the reports all open in pdf as they are made - which means we have 70+ pdf's popping up, which can get annoying :D
Is there a way to just save the reports as pdf but without Adobe opening each report?

Thanks so much for all your help!

Jessica

[JessicaZ]

One more thing, even though I have SELECT DISTINCT, it is still giving me some duplicates ...

[ADezii]

OK, its working but with a couple minor problems.

Here is the code

Private Sub btnPDFOpCoRpt_Click()

If IsNull(Me.cboDates) Then
  MsgBox "Please choose a Date for the Report."
  Me.cboDates.SetFocus
  Cancel = True
Else
  MsgBox "Press ctrl-Break to pause or stop the report."
 
  
Dim MyDB As DAO.Database
Dim rstUniqueIDs As DAO.Recordset
Dim strSQL As String
Dim strSQL_2 As String
Dim strAreaName As String

Set MyDB = CurrentDb()

'*************************************************    ********************
'Create a Recordset consisting of Unique User IDs
strSQL = "SELECT DISTINCT tblOpco.AreaID, tblOpCo.Opco FROM tblOpCo;" '*
Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       '
'*************************************************    ********************
  
With rstUniqueIDs
  If .BOF Or .EOF Then Exit Sub     'No Records
  Do While Not .EOF
    'Open your Report in Hidden/Design Mode, dynamically changing its Record
    'Source based on the ![UserID] value in the Recordset, then Save it!
    strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID]
    'And [OpCo Query].Description not like "*aka" And [OpCo Query].CategoryID <> 22

    strAreaName = ![OpCo]
    DoCmd.OpenReport "OpCo Report", acViewDesign, , , acHidden
   Reports![OpCo Report].RecordSource = strSQL_2
    DoCmd.Close acReport, "OpCo Report", acSaveYes
      'Open and Convert Report to PDF Format here using the Leban's Code
      'Pass the File Name Parameter as: strareaName & ".pdf"


' You must pass either RptName or SnapshotName or set the ShowSaveFileDialog param to TRUE.
' Any file names you pass to this function must include the full path. If you only include the
' filename for the output PDF then your document will be saved to your My Documents folder.

On Error GoTo err_blRet

Dim blRet As Boolean


' Call our convert function
' Please note the last param signals whether to perform
' font embedding or not. I have turned font embedding ON for this example.
blRet = ConvertReportToPDF("OpCo Report", vbNullString, Me.cboDates.Value & "- SYSCO Stocking Report -" & strAreaName & ".pdf", False, True, 150, "", "", 0, 0, 0)
' To modify the above call to force the File Save Dialog to select the name and path
' for the saved PDF file simply change the ShowSaveFileDialog param to TRUE.
       
       
  .MoveNext
  Loop
End With
  
rstUniqueIDs.Close
Set rstUniqueIDs = Nothing
Set rstUniqueIDs = Nothing
End If
Exit_blRet:
Exit Sub
err_blRet:
    MsgBox Err.Description
    Resume Exit_blRet
End Sub

I have a combo box(cboDates) on the form with the available date for the report - Right now there is just July 2008 and September 2008 in there. What is happening is I have July 2008 selected in the combo box and then run the report. When the report comes up, the title of the first report is named "July 2008 - Sysco Stocking Report - etc.pdf" BUT the report itself has September 2008 data in it. The rest of the reports all come up as July, but the first one doesn't.

The second thing is that the reports all open in pdf as they are made - which means we have 70+ pdf's popping up, which can get annoying :D
Is there a way to just save the reports as pdf but without Adobe opening each report?

Thanks so much for all your help!

Jessica

I have a combo box(cboDates) on the form with the available date for the report - Right now there is just July 2008 and September 2008 in there. What is happening is I have July 2008 selected in the combo box and then run the report. When the report comes up, the title of the first report is named "July 2008 - Sysco Stocking Report - etc.pdf" BUT the report itself has September 2008 data in it. The rest of the reports all come up as July, but the first one doesn't.

Jessica, I'm a little confused, is the Title of the Report not what it should be, or the Record Source not reflective of the Data?

The second thing is that the reports all open in pdf as they are made - which means we have 70+ pdf's popping up, which can get annoying :D
Is there a way to just save the reports as pdf but without Adobe opening each report?

On of the Arguments of the ConvertReportsT oPDF() Function should control this behavior (open/not open the Server Application). It would be an Argument with a True/False Value, I'm not familiar with Mr. LeBan's code, and do not have the actual Function in front of me, but it would more than likely be a True value that must be set to False, probably between the False, True, 150 Arguments.

[ADezii]

Jessica, I'm a little confused, is the Title of the Report not what it should be, or the Record Source not reflective of the Data?

On of the Arguments of the ConvertReportsT oPDF() Function should control this behavior (open/not open the Server Application). It would be an Argument with a True/False Value, I'm not familiar with Mr. LeBan's code, and do not have the actual Function in front of me, but it would more than likely be a True value that must be set to False, probably between the False, True, 150 Arguments.

It is giving you Unique Values based in the combination of [AreaID] and [Opco], as in:

[AreaID]  [Opco]
   1         A
   1         B
   1         C
   2         L
   3         K
   4         Y
   4         A
   1         A (no good, since Unique combination 
                already exists in Line #2)

[ADezii]

Hello Jessica, back again. The 5th Argument is the one that you should be concerned with. It reads as follows:

Optional StartPDFViewer As Boolean = True

which means that it will Default to True (Open Reader). Try changing Line #53 in Post #12 to:

blRet = ConvertReportToPDF("OpCo Report", vbNullString, Me.cboDates.Value & "- SYSCO Stocking Report -" & strAreaName & ".pdf", False, False, 150, "", "", 0, 0, 0)

'

[JessicaZ]

Jessica, I'm a little confused, is the Title of the Report not what it should be, or the Record Source not reflective of the Data?

The title is reflective of what is chosen in the combo box, but the data is wrong, just for the first report. The subsequent reports are all correct.

I will take a look at the rest of the suggestions after this lovely 2 1/2 hour meeting I have this afternoon. Yay.

Jessica