By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Leban's Report to PDF question

P: 17
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
Oct 3 '08 #1
Share this Question
Share on Google+
35 Replies


Expert Mod 2.5K+
P: 2,545
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
Oct 3 '08 #2

P: 17
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
Oct 3 '08 #3

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub SomeStuff (param1 as type1, param2 as type 2, Optional ByVal newreportname as string = "") 
-Stewart
Oct 3 '08 #4

ADezii
Expert 5K+
P: 8,679
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstUniqueIDs As DAO.Recordset
  3. Dim strSQL As String
  4. Dim strSQL_2 As String
  5. Dim strUserName As String
  6.  
  7. Set MyDB = CurrentDb()
  8.  
  9. '*********************************************************************
  10. 'Create a Recordset consisting of Unique User IDs                    *
  11. strSQL = "SELECT DISTINCT YourTableName.UserID FROM YourTableName;" '*
  12. Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       '*
  13. '*********************************************************************
  14.  
  15. With rstUniqueIDs
  16.   If .BOF Or .EOF Then Exit Sub     'No Records
  17.   Do While Not .EOF
  18.     'Open your Report in Hidden/Design Mode, dynamically changing its Record
  19.     'Source based on the ![UserID] value in the Recordset, then Save it!
  20.     strSQL_2 = "Select * From YourTableName Where YourTableName.UserID " & _
  21.                "= " & ![UserID]
  22.     strUserName = DLookup("[UserName]", "YourTableName", "[UserID] = " & ![UserID])
  23.     DoCmd.OpenReport "YourReportName", acViewDesign, , , acHidden
  24.     Reports![YourReportName].RecordSource = strSQL_2
  25.     DoCmd.Close acReport, "YourReportName", acSaveYes
  26.       'Open and Convert Report to PDF Format here using the Leban's Code
  27.       'Pass the File Name Parameter as: strUserName & ".pdf"
  28.        .MoveNext
  29.   Loop
  30. End With
  31.  
  32. rstUniqueIDs.Close
  33. Set rstUniqueIDs = Nothing
  34.  
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.
Oct 4 '08 #5

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

Jessica
Oct 5 '08 #6

ADezii
Expert 5K+
P: 8,679
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.
Oct 5 '08 #7

ADezii
Expert 5K+
P: 8,679
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):
Expand|Select|Wrap|Line Numbers
  1. UserID    UserName
  2.   3       Leverling
  3.   4       Peacock
  4.   9       Dodsworth
  5.   4       Peacock
  6.   8       Callahan
  7.   6       Suyama
  8.   1       Davoliol
  9.   2       Fuller
  10.   6       Suyama
  11.   7       King
  12.   5       Buchanan
  13.   9       Dodsworth
  14.   9       Dodsworth
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstUniqueIDs As DAO.Recordset
  3. Dim strSQL As String
  4. Dim strSQL_2 As String
  5. Dim strUserName As String
  6.  
  7. Set MyDB = CurrentDb()
  8.  
  9. '*************************************************  ********************
  10. 'Create a Recordset consisting of Unique User IDs
  11. strSQL = "SELECT DISTINCT YourTableName.UserID, YourTableName.UserName FROM YourTableName;" '*
  12. Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       '
  13. '*************************************************  ********************
  14.  
  15. With rstUniqueIDs
  16.   If .BOF Or .EOF Then Exit Sub     'No Records
  17.   Do While Not .EOF
  18.     'Open your Report in Hidden/Design Mode, dynamically changing its Record
  19.     'Source based on the ![UserID] value in the Recordset, then Save it!
  20.     strSQL_2 = "Select * From YourTableName Where YourTableName.UserID " & _
  21.                "= " & ![UserID]
  22.     strUserName = ![UserName]
  23.     DoCmd.OpenReport "YourReportName", acViewDesign, , , acHidden
  24.     Reports![YourReportName].RecordSource = strSQL_2
  25.     DoCmd.Close acReport, "YourReportName", acSaveYes
  26.       'Open and Convert Report to PDF Format here using the Leban's Code
  27.       'Pass the File Name Parameter as: strUserName & ".pdf"
  28.        .MoveNext
  29.   Loop
  30. End With
  31.  
  32. rstUniqueIDs.Close
  33. Set rstUniqueIDs = Nothing
  34. Set rstUniqueIDs = Nothing
Oct 5 '08 #8

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

Stay tuned
Thanks!

Jessica
Oct 8 '08 #9

P: 17
OK, its working but with a couple minor problems.

Here is the code
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnPDFOpCoRpt_Click()
  2.  
  3. If IsNull(Me.cboDates) Then
  4.   MsgBox "Please choose a Date for the Report."
  5.   Me.cboDates.SetFocus
  6.   Cancel = True
  7. Else
  8.   MsgBox "Press ctrl-Break to pause or stop the report."
  9.  
  10.  
  11. Dim MyDB As DAO.Database
  12. Dim rstUniqueIDs As DAO.Recordset
  13. Dim strSQL As String
  14. Dim strSQL_2 As String
  15. Dim strAreaName As String
  16.  
  17. Set MyDB = CurrentDb()
  18.  
  19. '*************************************************    ********************
  20. 'Create a Recordset consisting of Unique User IDs
  21. strSQL = "SELECT DISTINCT tblOpco.AreaID, tblOpCo.Opco FROM tblOpCo;" '*
  22. Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       '
  23. '*************************************************    ********************
  24.  
  25. With rstUniqueIDs
  26.   If .BOF Or .EOF Then Exit Sub     'No Records
  27.   Do While Not .EOF
  28.     'Open your Report in Hidden/Design Mode, dynamically changing its Record
  29.     'Source based on the ![UserID] value in the Recordset, then Save it!
  30.     strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID]
  31.     'And [OpCo Query].Description not like "*aka" And [OpCo Query].CategoryID <> 22
  32.  
  33.     strAreaName = ![OpCo]
  34.     DoCmd.OpenReport "OpCo Report", acViewDesign, , , acHidden
  35.    Reports![OpCo Report].RecordSource = strSQL_2
  36.     DoCmd.Close acReport, "OpCo Report", acSaveYes
  37.       'Open and Convert Report to PDF Format here using the Leban's Code
  38.       'Pass the File Name Parameter as: strareaName & ".pdf"
  39.  
  40.  
  41. ' You must pass either RptName or SnapshotName or set the ShowSaveFileDialog param to TRUE.
  42. ' Any file names you pass to this function must include the full path. If you only include the
  43. ' filename for the output PDF then your document will be saved to your My Documents folder.
  44.  
  45. On Error GoTo err_blRet
  46.  
  47. Dim blRet As Boolean
  48.  
  49.  
  50. ' Call our convert function
  51. ' Please note the last param signals whether to perform
  52. ' font embedding or not. I have turned font embedding ON for this example.
  53. blRet = ConvertReportToPDF("OpCo Report", vbNullString, Me.cboDates.Value & "- SYSCO Stocking Report -" & strAreaName & ".pdf", False, True, 150, "", "", 0, 0, 0)
  54. ' To modify the above call to force the File Save Dialog to select the name and path
  55. ' for the saved PDF file simply change the ShowSaveFileDialog param to TRUE.
  56.  
  57.  
  58.   .MoveNext
  59.   Loop
  60. End With
  61.  
  62. rstUniqueIDs.Close
  63. Set rstUniqueIDs = Nothing
  64. Set rstUniqueIDs = Nothing
  65. End If
  66. Exit_blRet:
  67. Exit Sub
  68. err_blRet:
  69.     MsgBox Err.Description
  70.     Resume Exit_blRet
  71. End Sub
  72.  

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
Oct 9 '08 #10

P: 17
One more thing, even though I have SELECT DISTINCT, it is still giving me some duplicates ...
Oct 9 '08 #11

ADezii
Expert 5K+
P: 8,679
OK, its working but with a couple minor problems.

Here is the code
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnPDFOpCoRpt_Click()
  2.  
  3. If IsNull(Me.cboDates) Then
  4.   MsgBox "Please choose a Date for the Report."
  5.   Me.cboDates.SetFocus
  6.   Cancel = True
  7. Else
  8.   MsgBox "Press ctrl-Break to pause or stop the report."
  9.  
  10.  
  11. Dim MyDB As DAO.Database
  12. Dim rstUniqueIDs As DAO.Recordset
  13. Dim strSQL As String
  14. Dim strSQL_2 As String
  15. Dim strAreaName As String
  16.  
  17. Set MyDB = CurrentDb()
  18.  
  19. '*************************************************    ********************
  20. 'Create a Recordset consisting of Unique User IDs
  21. strSQL = "SELECT DISTINCT tblOpco.AreaID, tblOpCo.Opco FROM tblOpCo;" '*
  22. Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       '
  23. '*************************************************    ********************
  24.  
  25. With rstUniqueIDs
  26.   If .BOF Or .EOF Then Exit Sub     'No Records
  27.   Do While Not .EOF
  28.     'Open your Report in Hidden/Design Mode, dynamically changing its Record
  29.     'Source based on the ![UserID] value in the Recordset, then Save it!
  30.     strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID]
  31.     'And [OpCo Query].Description not like "*aka" And [OpCo Query].CategoryID <> 22
  32.  
  33.     strAreaName = ![OpCo]
  34.     DoCmd.OpenReport "OpCo Report", acViewDesign, , , acHidden
  35.    Reports![OpCo Report].RecordSource = strSQL_2
  36.     DoCmd.Close acReport, "OpCo Report", acSaveYes
  37.       'Open and Convert Report to PDF Format here using the Leban's Code
  38.       'Pass the File Name Parameter as: strareaName & ".pdf"
  39.  
  40.  
  41. ' You must pass either RptName or SnapshotName or set the ShowSaveFileDialog param to TRUE.
  42. ' Any file names you pass to this function must include the full path. If you only include the
  43. ' filename for the output PDF then your document will be saved to your My Documents folder.
  44.  
  45. On Error GoTo err_blRet
  46.  
  47. Dim blRet As Boolean
  48.  
  49.  
  50. ' Call our convert function
  51. ' Please note the last param signals whether to perform
  52. ' font embedding or not. I have turned font embedding ON for this example.
  53. blRet = ConvertReportToPDF("OpCo Report", vbNullString, Me.cboDates.Value & "- SYSCO Stocking Report -" & strAreaName & ".pdf", False, True, 150, "", "", 0, 0, 0)
  54. ' To modify the above call to force the File Save Dialog to select the name and path
  55. ' for the saved PDF file simply change the ShowSaveFileDialog param to TRUE.
  56.  
  57.  
  58.   .MoveNext
  59.   Loop
  60. End With
  61.  
  62. rstUniqueIDs.Close
  63. Set rstUniqueIDs = Nothing
  64. Set rstUniqueIDs = Nothing
  65. End If
  66. Exit_blRet:
  67. Exit Sub
  68. err_blRet:
  69.     MsgBox Err.Description
  70.     Resume Exit_blRet
  71. End Sub
  72.  

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.
Oct 9 '08 #12

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. [AreaID]  [Opco]
  2.    1         A
  3.    1         B
  4.    1         C
  5.    2         L
  6.    3         K
  7.    4         Y
  8.    4         A
  9.    1         A (no good, since Unique combination 
  10.                 already exists in Line #2)
  11.  
Oct 9 '08 #13

ADezii
Expert 5K+
P: 8,679
Hello Jessica, back again. The 5th Argument is the one that you should be concerned with. It reads as follows:
Expand|Select|Wrap|Line Numbers
  1. Optional StartPDFViewer As Boolean = True
which means that it will Default to True (Open Reader). Try changing Line #53 in Post #12 to:
Expand|Select|Wrap|Line Numbers
  1. blRet = ConvertReportToPDF("OpCo Report", vbNullString, Me.cboDates.Value & "- SYSCO Stocking Report -" & strAreaName & ".pdf", False, False, 150, "", "", 0, 0, 0) 
'
Oct 9 '08 #14

P: 17
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
Oct 9 '08 #15

ADezii
Expert 5K+
P: 8,679
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
Expand|Select|Wrap|Line Numbers
  1. '*************************************************      ******************** 
  2. 'Create a Recordset consisting of Unique User IDs 
  3. strSQL = "SELECT DISTINCT tblOpco.AreaID, tblOpCo.Opco FROM tblOpCo;" '* 
  4. Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       ' 
  5. '*************************************************      ******************** 
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.
Oct 9 '08 #16

P: 17
Expand|Select|Wrap|Line Numbers
  1. '*************************************************      ******************** 
  2. 'Create a Recordset consisting of Unique User IDs 
  3. strSQL = "SELECT DISTINCT tblOpco.AreaID, tblOpCo.Opco FROM tblOpCo;" '* 
  4. Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       ' 
  5. '*************************************************      ******************** 
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
Oct 10 '08 #17

P: 17
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?
Oct 14 '08 #18

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. ...AND [Opco Query].ReportDate = #" & Forms![<Your Form Name>]![cboDates] & "#;"
Oct 14 '08 #19

P: 17
Try:
Expand|Select|Wrap|Line Numbers
  1. ...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 #

Expand|Select|Wrap|Line Numbers
  1. 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
Oct 14 '08 #20

ADezii
Expert 5K+
P: 8,679
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 #

Expand|Select|Wrap|Line Numbers
  1. 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:
    Expand|Select|Wrap|Line Numbers
    1. strSQL_2 = "SELECT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
    2.            " And [Opco Query].ReportDate = #" & _
    3.            Forms![Sysco Reporting Database]![cboDates] & "#;"
Oct 14 '08 #21

P: 17
  1. Is [ReportDate] contained in the Opco Query?
  2. Try the SQL Statement with the '#' Delineters and a slight change in Syntax:
    Expand|Select|Wrap|Line Numbers
    1. strSQL_2 = "SELECT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
    2.            " And [Opco Query].ReportDate = #" & _
    3.            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
Oct 14 '08 #22

P: 17
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:
Expand|Select|Wrap|Line Numbers
  1.  strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID] 
  2.  
  3.     strAreaName = ![OpCo] 
  4.     DoCmd.OpenReport "OpCo Report", acViewDesign, , , acHidden 
  5.    Reports![OpCo Report].RecordSource = strSQL_2 
  6.     DoCmd.Close acReport, "OpCo Report", acSaveYes 
  7.  
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"

Expand|Select|Wrap|Line Numbers
  1. strSQL_2 = "SELECT DISTINCT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
  2.            " And [Opco Query].ReportDate = #" & _
  3.            Forms![sysco reporting database]![cboDates] & "#;"
  4.  
  5. ..... 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
Oct 20 '08 #23

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1.  strSQL_2 = "SELECT distinct * FROM [OpCo Query] WHERE [OpCo Query].AreaID " & "= " & ![AreaID] 
  2.  
  3.     strAreaName = ![OpCo] 
  4.     DoCmd.OpenReport "OpCo Report", acViewDesign, , , acHidden 
  5.    Reports![OpCo Report].RecordSource = strSQL_2 
  6.     DoCmd.Close acReport, "OpCo Report", acSaveYes 
  7.  
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"

Expand|Select|Wrap|Line Numbers
  1. strSQL_2 = "SELECT DISTINCT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
  2.            " And [Opco Query].ReportDate = #" & _
  3.            Forms![sysco reporting database]![cboDates] & "#;"
  4.  
  5. ..... 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.
Oct 20 '08 #24

P: 17
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
Oct 22 '08 #25

ADezii
Expert 5K+
P: 8,679
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.
Oct 22 '08 #26

P: 17
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
Oct 24 '08 #27

ADezii
Expert 5K+
P: 8,679
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.
Oct 24 '08 #28

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstUniqueIDs As DAO.Recordset
  3. Dim strSQL As String
  4. Dim strSQL_2 As String
  5. Dim strAreaName As String
  6. Dim blRet As Boolean
  7.  
  8. If IsNull(Me.cboDates) Then
  9.   MsgBox "Please choose a Date for the Report."
  10.   Me.cboDates.SetFocus
  11.     Me![cboDates].Dropdown
  12.       Exit Sub
  13. Else
  14.   MsgBox "Press ctrl-Break to pause or stop the report."
  15. End If
  16.  
  17. Set MyDB = CurrentDb()
  18.  
  19. '*************************************************    ********************
  20. 'Create a Recordset consisting of Unique User IDs
  21. strSQL = "SELECT DISTINCT tblOpco.AreaID, tblOpCo.Opco FROM tblOpCo;" '*
  22.  
  23. Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)       '
  24. '*************************************************    ********************
  25.  
  26. With rstUniqueIDs
  27.   Do While Not .EOF
  28.     'Open your Report in Hidden/Design Mode, dynamically changing its Record
  29.     'Source based on the ![UserID] value in the Recordset, then Save it!
  30.  
  31.     '********************************************* Test Code ************************************
  32.     strSQL_2 = "SELECT DISTINCT * FROM [OpCo Query] WHERE [OpCo Query].AreaID = " & ![AreaID] & _
  33.                " And [Opco Query].ReportDate = '" & Forms![sysco reporting database]![cboDates] & "';"
  34.     '********************************************* Test Code ************************************
  35.  
  36.     strAreaName = ![OpCo]
  37.     Debug.Print strAreaName & " ==> " & ![AreaID] & " ==> " & Forms![sysco reporting database]![cboDates]
  38.     DoCmd.OpenReport "OpCo Report", acViewDesign, , , acHidden
  39.     Reports![OpCo Report].RecordSource = strSQL_2
  40.     DoCmd.Close acReport, "OpCo Report", acSaveYes
  41.       'Open and Convert Report to PDF Format here using the Leban's Code
  42.       'Pass the File Name Parameter as: strareaName & ".pdf"
  43.  
  44.     ' You must pass either RptName or SnapshotName or set the ShowSaveFileDialog param to TRUE.
  45.     ' Any file names you pass to this function must include the full path. If you only include the
  46.     ' filename for the output PDF then your document will be saved to your My Documents folder.
  47.  
  48.     ' Call the convert function
  49.  
  50.     blRet = ConvertReportToPDF("OpCo Report", vbNullString, Me.cboDates.Value & _
  51.         "- SYSCO Stocking Report -" & strAreaName & ".pdf", False, False, 150, "", "", 0, 0, 0)
  52.     .MoveNext
  53.   Loop
  54. End With
  55.  
  56. rstUniqueIDs.Close
  57. Set rstUniqueIDs = Nothing
Oct 25 '08 #29

P: 17
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
Oct 27 '08 #30

P: 17
It works! It works! THANK YOU!!
Oct 27 '08 #31

ADezii
Expert 5K+
P: 8,679
It works! It works! THANK YOU!!
You ar quite welcome, Jessica. Always like a happy ending! (LOL).
Oct 27 '08 #32

P: 2
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.
Nov 17 '08 #33

ADezii
Expert 5K+
P: 8,679
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.
Nov 18 '08 #34

P: 2
  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 :)
Nov 18 '08 #35

ADezii
Expert 5K+
P: 8,679
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.
Nov 18 '08 #36

Post your reply

Sign in to post your reply or Sign up for a free account.