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.Value & ".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.Column(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 ConvertReportsToPDF() 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 ConvertReportsToPDF() 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

[ADezii]

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

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

Hello Jessica, the only think that makes any sense to me as far as the data being wrong only in the 1st Report, is that either tblOpco.AreaID or tblOpco.Opco contains a NULL Value in the 1st Record of the Recordset, namely rstUniqueIDs. Other than this, I have no other explanation at this point.

[JessicaZ]

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

Hello Jessica, the only think that makes any sense to me as far as the data being wrong only in the 1st Report, is that either tblOpco.AreaID or tblOpco.Opco contains a NULL Value in the 1st Record of the Recordset, namely rstUniqueIDs. Other than this, I have no other explanation at this point.

I let the code create all the reports and took a second look at them - Whats happening is that every other report has the wrong month. They all have The right titles, but the code is pulling alternate months for each report. I think there must be some iteration problem.


Jessica

[JessicaZ]

Hmmm...well I finally got a chance to review the code and discovered I have no place that the code actually says to only use the date in cboDates for the report. So, what would be the best/easiest way to change this? I would guess here:
strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID]

would it work to just add
AND {Opco Query].ReportDate = cboDates.value

Or is there some other way to do this? Earlier I had been trying to filter the WHERE clause further and it wasn't pulling what I needed so now Im worried this wont work either.

Thanks

Jess

Edited to add: Nope, that didnt work. Other ideas?

[ADezii]

Hmmm...well I finally got a chance to review the code and discovered I have no place that the code actually says to only use the date in cboDates for the report. So, what would be the best/easiest way to change this? I would guess here:
strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID]

would it work to just add
AND {Opco Query].ReportDate = cboDates.value

Or is there some other way to do this? Earlier I had been trying to filter the WHERE clause further and it wasn't pulling what I needed so now Im worried this wont work either.

Thanks

Jess

Edited to add: Nope, that didnt work. Other ideas?

Try:

...AND [Opco Query].ReportDate = #" & Forms![<Your Form Name>]![cboDates] & "#;"

[JessicaZ]

Try:

...AND [Opco Query].ReportDate = #" & Forms![<Your Form Name>]![cboDates] & "#;"

Thanks, that also doesnt work. I should mention that the ReportDate is a text column as it is just month and year and not an actual date. So I took out the #

strSQL_2 = "SELECT * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID] And [Opco Query].ReportDate = " &  Forms![Sysco Reporting Database]![cboDates]  & "

I keep getting a Runtime Error '2465'
SYSCO Reporting Database cant find the field '|' referred to in your expression

What next?

Jess

[ADezii]

Thanks, that also doesnt work. I should mention that the ReportDate is a text column as it is just month and year and not an actual date. So I took out the #

strSQL_2 = "SELECT * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID] And [Opco Query].ReportDate = " &  Forms![Sysco Reporting Database]![cboDates]  & "

I keep getting a Runtime Error '2465'
SYSCO Reporting Database cant find the field '|' referred to in your expression

What next?

Jess

1. Is [ReportDate] contained in the Opco Query?
2. Try the SQL Statement with the '#' Delineters and a slight change in Syntax:
   

   strSQL_2 = "SELECT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
              " And [Opco Query].ReportDate = #" & _
              Forms![Sysco Reporting Database]![cboDates] & "#;"

[JessicaZ]

1. Is [ReportDate] contained in the Opco Query?
2. Try the SQL Statement with the '#' Delineters and a slight change in Syntax:
   

   strSQL_2 = "SELECT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
              " And [Opco Query].ReportDate = #" & _
              Forms![Sysco Reporting Database]![cboDates] & "#;"

yes ReportDate is in the OpCo Query which is pulled from tblDateItem (there are a total of 5 tables the query is based on)

I tried your most recent code and did not get an error! But....the reports were all blank (Report Header was there, just no data) Before we added the ReportDate filter all the data was pulled, just not correctly.

I really wish I was more adept at this!

Jessica

[JessicaZ]

I'm back <waving>

I took a little break on this code thinking if I went on to something else that I wouldn't pull my hair out anymore. Well, I now have the rest of the db coded and still havent gotten this one part to work. :(

I started out with this:

 strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID] 
 
    strAreaName = ![OpCo] 
    DoCmd.OpenReport "OpCo Report", acViewDesign, , , acHidden 
   Reports![OpCo Report].RecordSource = strSQL_2 
    DoCmd.Close acReport, "OpCo Report", acSaveYes 
 

Unfortunately that code was pulling all of the data from all of the dates for each OpCo and every other page had a different Date on it because of that. So we changed the code to this"

strSQL_2 = "SELECT DISTINCT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
           " And [Opco Query].ReportDate = #" & _
           Forms![sysco reporting database]![cboDates] & "#;"
 
..... etc etc

And now there is no data in the actual report, but the Header has the correct date on it ;)

Anyway, does anyone have a suggestion as to what is happening here?

Thanks!

Jessica

[ADezii]

I'm back <waving>

I took a little break on this code thinking if I went on to something else that I wouldn't pull my hair out anymore. Well, I now have the rest of the db coded and still havent gotten this one part to work. :(

I started out with this:

 strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID] 
 
    strAreaName = ![OpCo] 
    DoCmd.OpenReport "OpCo Report", acViewDesign, , , acHidden 
   Reports![OpCo Report].RecordSource = strSQL_2 
    DoCmd.Close acReport, "OpCo Report", acSaveYes 
 

Unfortunately that code was pulling all of the data from all of the dates for each OpCo and every other page had a different Date on it because of that. So we changed the code to this"

strSQL_2 = "SELECT DISTINCT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
           " And [Opco Query].ReportDate = #" & _
           Forms![sysco reporting database]![cboDates] & "#;"
 
..... etc etc

And now there is no data in the actual report, but the Header has the correct date on it ;)

Anyway, does anyone have a suggestion as to what is happening here?

Thanks!

Jessica

It's extremely difficult at this point to isolate the problem. If you can send me the Database, or a subset of it, and are in absolutely no rush, I'll be happy to start from scratch and have a look at it whenever I can. Let me know.

[JessicaZ]

It's extremely difficult at this point to isolate the problem. If you can send me the Database, or a subset of it, and are in absolutely no rush, I'll be happy to start from scratch and have a look at it whenever I can. Let me know.

If you dont mind, I would appreciate the help. Can you PM me your email address?

Jessica

[ADezii]

If you dont mind, I would appreciate the help. Can you PM me your email address?

Jessica

Check for a Private Message, it will be there.

[JessicaZ]

Thanks. I emailed you this morning. I appreciate your wiillingness to help out! I know you are busy and I will continue to try and figure it out myself, but any insight is much appreciated!

Jess

[ADezii]

Thanks. I emailed you this morning. I appreciate your wiillingness to help out! I know you are busy and I will continue to try and figure it out myself, but any insight is much appreciated!

Jess

I downloaded you Database, starting from the very beginning on this Thread, and I'll see if I come up with anything in a couple of days. Stay tuned.

[ADezii]

Thanks. I emailed you this morning. I appreciate your wiillingness to help out! I know you are busy and I will continue to try and figure it out myself, but any insight is much appreciated!

Jess

Jessica, I do not have a required *.dll to run the Leban's code, but I did make some modifications on-the-fly. Try this new code in the Click() Event of the Export to .PDF Command Button:

Dim MyDB As DAO.Database
Dim rstUniqueIDs As DAO.Recordset
Dim strSQL As String
Dim strSQL_2 As String
Dim strAreaName As String
Dim blRet As Boolean
 
If IsNull(Me.cboDates) Then
  MsgBox "Please choose a Date for the Report."
  Me.cboDates.SetFocus
    Me![cboDates].Dropdown
      Exit Sub
Else
  MsgBox "Press ctrl-Break to pause or stop the report."
End If
 
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
  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!
 
    '********************************************* Test Code ************************************
    strSQL_2 = "SELECT DISTINCT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
               " And [Opco Query].ReportDate = '" & Forms![sysco reporting database]![cboDates] & "';"
    '********************************************* Test Code ************************************
 
    strAreaName = ![OpCo]
    Debug.Print strAreaName & " ==> " & ![AreaID] & " ==> " & Forms![sysco reporting database]![cboDates]
    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.
 
    ' Call the convert function
 
    blRet = ConvertReportToPDF("OpCo Report", vbNullString, Me.cboDates.Value & _
        "- SYSCO Stocking Report -" & strAreaName & ".pdf", False, False, 150, "", "", 0, 0, 0)
    .MoveNext
  Loop
End With
 
rstUniqueIDs.Close
Set rstUniqueIDs = Nothing

[JessicaZ]

Thanks so much for your help! I havent had a chance to look at this yet, but I will do it tomorrow (Monday) and let you know what happens

Jess

[JessicaZ]

It works! It works! THANK YOU!!

[ADezii]

It works! It works! THANK YOU!!

You ar quite welcome, Jessica. Always like a happy ending! (LOL).

[tbahadur]

It works! It works! THANK YOU!!

Hi Jessica,

I'm using Leban's report as well to convert to PDF but when I look at the code you have online to e-mail I'm really not sure where you're putting it since I keep getting errors :( I'm as beginner as you get with all this stuff and I'm wondering if you had a sample that you could e-mail showing how you've incorporated this. I too have fields that criteria in the report and I can see that I have to move that now...I'm sooooo confused I've been at this thing for 2 months now. Thank you for any help you can provide.

[ADezii]

Hi Jessica,

I'm using Leban's report as well to convert to PDF but when I look at the code you have online to e-mail I'm really not sure where you're putting it since I keep getting errors :( I'm as beginner as you get with all this stuff and I'm wondering if you had a sample that you could e-mail showing how you've incorporated this. I too have fields that criteria in the report and I can see that I have to move that now...I'm sooooo confused I've been at this thing for 2 months now. Thank you for any help you can provide.

1. The Code can be executed from the Click() Event of a Command Button on a Form.
2. To be perfectly honest, if you are a Beginner, you are tackling something that may be a little over your head.
3. The sample code that I worked on was from Jessica's Database. Even if I still had the test code, it wouldn't exactly be proper to provide it to you.
4. I would suggest sending a Private Message to the Originator of this Thread (Jessica), and ask her for some sample code.

[tbahadur]

1. The Code can be executed from the Click() Event of a Command Button on a Form.
2. To be perfectly honest, if you are a Beginner, you are tackling something that may be a little over your head.
3. The sample code that I worked on was from Jessica's Database. Even if I still had the test code, it wouldn't exactly be proper to provide it to you.
4. I would suggest sending a Private Message to the Originator of this Thread (Jessica), and ask her for some sample code.

Thank you, I had tried getting this to Jessica but I thought I had to post on the forum, I've taken your advice and sent a PM. I know that I'm really close, I just know it has something to do with 2 lines of code (I think that my beginner knowledge is working against me with these 2 lines) and I'll muddle through I guess until a light comes on. Thanks again :)

[ADezii]

Thank you, I had tried getting this to Jessica but I thought I had to post on the forum, I've taken your advice and sent a PM. I know that I'm really close, I just know it has something to do with 2 lines of code (I think that my beginner knowledge is working against me with these 2 lines) and I'll muddle through I guess until a light comes on. Thanks again :)

Why not simply post your code with special attention to the 2 lines that youo think are the cause of the problem. We will then see what we can do.