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
35 2808
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
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
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
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: - Create a Recordset consisting of Unique User IDs.
- Loop through this Recordset.
- Open your Base Report in Design/Hidden View.
- Dynamically set the Record Source of the Report based on the Current Record Pointer in the Recordset ([UserID]).
- Close/Save the Report.
- Execute Mr. Leban's PDF Conversion Code on the Report for each and every Unique UserID.
- 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].
- 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.: - Substitute your actual Table Name for 'YourTableName'
- Substitute your actual Report Name for 'YourReportName'
- The code assumes [UserID] is a Numeric Value.
- The code assumes each and every [UserID] has a unique [UserName] associated with it.
- Obviously, there cannot be no Prompts in the Record Source for the Report, as previously indicated by Stewart.
Thsanks! Ill take a look at this and let you know what happenes, but I may not get back to it until Monday.
Jessica
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.
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
Edited: Hold on, I think I got it.........
Stay tuned
Thanks!
Jessica
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
One more thing, even though I have SELECT DISTINCT, it is still giving me some duplicates ...
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.
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)
-
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)
'
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
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.
- '************************************************* ********************
-
'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
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?
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] & "#;"
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
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
- Is [ReportDate] contained in the Opco Query?
- 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] & "#;"
- Is [ReportDate] contained in the Opco Query?
- 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
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
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.
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
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.
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
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.
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
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
It works! It works! THANK YOU!!
It works! It works! THANK YOU!!
You ar quite welcome, Jessica. Always like a happy ending! (LOL).
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.
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.
- The Code can be executed from the Click() Event of a Command Button on a Form.
- To be perfectly honest, if you are a Beginner, you are tackling something that may be a little over your head.
- 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.
- I would suggest sending a Private Message to the Originator of this Thread (Jessica), and ask her for some sample code.
- The Code can be executed from the Click() Event of a Command Button on a Form.
- To be perfectly honest, if you are a Beginner, you are tackling something that may be a little over your head.
- 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.
- 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 :)
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ty Smith via AccessMonster.com |
last post by:
I noticed that the week numbers in Stephan Leban's MonthCalendar are not consistent with Microsoft Outlook (they are shifted one week forward). Is there any way I can sync these two up by changing...
|
by: dan.roffman |
last post by:
Is there a way to span a report over multiple pages. I think you could
do something like count the number of lines and if it exceeds a certain
number, then put the rest of th text into another RTF...
|
by: alexanderhulse |
last post by:
Is it possible to create a single PDF file from many SNP files with
Leban's ReportToPDF functions? Or by any other method? (Preferably
inside Access so that there's no deployment to client...
|
by: Chesne |
last post by:
Does anyone know if it is possible to link Leban's Calendar to a table
so that when it is activated it will show previous dates which have
been booked?
TIA - Ray
|
by: vtashore |
last post by:
I downloaded Steve Leban's RTF2 control and it works as advertised.
Good news!
After reading reference material on the RTF standard codes, I have been
able to write update queries to universally...
|
by: Pascal Hagedorn |
last post by:
I downloaded Steve Leban's RTF2 control and it shows me the report as
wanted.
The Problem is, if i want to print it, it comes a PopUp where it says
"properties write protected" (german:...
|
by: beltex63 |
last post by:
Does anyone know how to:
Send active info from an open form (report references fields from open form) to a pdf document (which would get the document name from form fields) that would in turn open...
|
by: Tom |
last post by:
Hi all:
I'm having an odd problem with Stephen Leban's PDF creator. I'm
trying to output to 11x17 sized paper. The PDF creator does its thing
and when the PDF file opens, its at 800% zoom and...
|
by: OzairKhalid |
last post by:
Hi all,
I am using Stephen Leban's Report To PDF tool since many months on my
Ms Access 2003 application.
I am happy to use it because it is really a very good tool.
BUT now I need some...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |