-
I have restored my database back to the original version and used the previous code you gave me. Now from here can you help me rename my report?
Code:
Private Sub Command11_Click()
'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset
'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "DECO Import File"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.
myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand " & rst.Fields(0).Value
stCaption = stSubject & Format(Now(), " mm-dd-yyyy")
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "DECO Import File", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend
End Sub
Sub RewriteQuerySQL(strQueryName As String, strParameter As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)
qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"
End Sub
-
OK, so in the original state, is my code working as you asked for? Except the report name?
-
Yes, I had modified the database but changed it back to original version after things started to get messed up. Here are the few changes I would like to make and when I do, the database messes up and eliminates data from the reports.
1. I would like to change the report name in my DB to "Collateral Demand" and then for the report to specify that. Here is a link to the database I am currently looking at. It shouldn't be any different than yours but just in case. Link: Collateral Database Test - Download - 4shared - Valerie Wallace
2. I would like to change the subject so it references the "PortfolioName". I have added a column in my DECO Import File that will match the data in the "Counterparty Data" table. Here is the link to the new file: DECO Import File - Download - 4shared - Valerie Wallace
Any help would be greatly appreciated!
-
Yes, the original code is working except for the 2 points listed in my message at 2:27 PM (CST). Thank you again for all your help!
-
Also, I realize that when changing the subject to reference the Portfolio Name it will be hard when the file saves because it won't have a unique identifier separate from the others.. any thoughts?
-
Just checking to make sure you were able to receive the more recent files? Thanks so much again, it's greatly appreciated!!
-
1 Attachment(s)
You don't need to add that PortfolioName column in the Excel file. It is already there in the CounterParty Data table within the Access database. Here, try this.
Code:
Option Compare Database
Private Sub Command11_Click()
'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset
'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.
myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail, [Counterparty Data].PortfolioName" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(2).Value
stCaption = stSubject & Format(Now(), " mm-dd-yyyy")
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "Collateral Demand", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend
End Sub
Sub RewriteQuerySQL(strQueryName As String, strParameter As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQueryName)
qdf.SQL = "SELECT [DECO Import File].CurrentDate, [DECO Import File].COBDate, [Counterparty Data].BrokerName, [Counterparty Data].PortfolioName, [DECO Import File].Exposure, [DECO Import File].[Support Req], [DECO Import File].[Collateral Pledged/Held], [DECO Import File].[Minimum Transfer Amt (Broker)], [DECO Import File].[Collateral To Receive], [Counterparty Data].BankName, [Counterparty Data].[CashAccount#], [Counterparty Data].CashAccountName, [Counterparty Data].CashABA, [Counterparty Data].[SecuritiesAccount#], [Counterparty Data].SecuritiesAccountName, [Counterparty Data].SecuritiesABA, [Counterparty Data].CPEmail" & vbCrLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbCrLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0) AND (([Counterparty Data].Combo)=" & Chr(34) & strParameter & Chr(34) & "));"
End Sub
-
Okay the report file attached to the email is still named "DECO Import File" after I inserted your code and changed the report title. Also, since the file does not have a completely unique Identifier and since some titles are the same, some files do not save. (PortfolioName is the same for some demands.. the only thing completely unique is Combo except for that is not an identifier I would like the counterparties to see on their emails) Would it be possible to have the subject & saved file be a combinantion of "PortfolioName" - "BrokerName" (including date) so that these can save properly?
-
Are you saying that the name of the PDF file attached to the email is still named DECO Import File? Can you show this to me via teamviewer?
For the other requirement, use this
Code:
Private Sub Command11_Click()
'This will print the reports
Dim stEmailMessage As String
Dim stReport As String
Dim stCaption As String
Dim myPath As String
Dim strSQL As String
Dim stSubject As String
Dim rst As Recordset
'This sets the strings for each part of the email
stEmailMessage = "Please see the attached collateral call for today."
stReport = "Collateral Demand"
'This will change the caption of the report (which is what the filename is saved as" to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.
myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
strSQL = "SELECT [DECO Import File].Combo, [Counterparty Data].CPEmail, [Counterparty Data].PortfolioName, [Counterparty Data].BrokerName" & vbLf & _
"FROM [Counterparty Data] INNER JOIN [DECO Import File] ON [Counterparty Data].Combo = [DECO Import File].Combo" & vbLf & _
"WHERE ((([DECO Import File].[Collateral To Receive])>0));"
Set rst = CurrentDb.OpenRecordset(strSQL)
While Not (rst.BOF And rst.EOF)
stSubject = "Collateral Demand - " & rst.Fields(2).Value & " - " & rst.Fields(3).Value & Format(Now(), " mm-dd-yyyy")
stCaption = stSubject
RewriteQuerySQL "Report Query", rst.Fields(0).Value
DoCmd.OpenQuery "Report Query"
DoCmd.Close acQuery, "Report Query"
DoCmd.OpenReport "Collateral Demand", acViewNormal
DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst.Fields(1).Value, "derivcollateral@rgare.com", , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rst.MoveNext
Wend
End Sub
-
That definitely fixes the issue for saving the file and the subject so thank you very much!! My computer at work does not allow for this software to run so I am putting the link below to a screenshot of what I see. Please keep in mind that I deleted out the email addresses manually, these are working correctly.
Screenshot - Download - 4shared - Valerie Wallace
