Hello,
Afraid you would ask, thanks for offering to have a look. Cool program
you wrote.
Below is the function that calls it. ( I am a structural engineer and
only took a software module in school so apologies if the code is poorly
written) I have attached a text file with the whole form's code on it.
Private Sub PayButton_Click()
Dim strEmailAddy As String
If IsNull(Me.Emails.Column(2)) Then
strEmailAddy = ""
Else
strEmailAddy = Me.Emails.Column(2)
End If
If (Me.InvoiceVAT1 + InvoiceVAT2 + InvoiceVAT3 + Me.INVOICEVAT4) <> 0
Then
'GENERATE FILE NAME
Dim WorkBasez As Database
Dim WorkRS1a As Recordset
Dim SQA As String
PayCertNoSQL = "SELECT COUNT(*) AS Vat1AmountR2 FROM Payments WHERE
OrderID=" & [Forms]![Payments]![OrderID] & ";"
Set WorkBasez = CurrentDb
Set WorkRS1a = WorkBasez.OpenRecordset(PayCertNoSQL)
WorkRS1a.MoveFirst
strCertNumber = WorkRS1a!Vat1AmountR2 + 1
strFileName = "S" & Me.SubConID & "O" & Me.OrderID & "C" &
strCertNumber & ""
Select Case Me.Payfield
Case 1
'GENERATE PDF FOR PRINT ONLY / PRINT & EMAIL see
http://www.lebans.com/reporttopdf.htm
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("PaymentCertificate", vbNullString, _
"Z:\A-Database\Payment Certificates\" & strFileName & ".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.
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
With oMail
.To = strEmailAddy
.body = "Please find attached Payment Certificate. Please invoice
the amount shown and send it to our head office for attention of
accounts."
.Subject = "Payment certificate for invoicing"
.Attachments.Add ("Z:\A-Database\Payment Certificates\" &
strFileName & ".pdf")
.Display
End With
Set oMail = Nothing
Set oLook = Nothing
Case 2
'GENERATE PDF FOR PRINT ONLY / PRINT & EMAIL see
http://www.lebans.com/reporttopdf.htm
' 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("PaymentCertificate", vbNullString, _
"Z:\A-Database\Payment Certificates\" & strFileName & ".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.
Case 3
'GENERATE PDF FOR EMAIL ONLY see
http://www.lebans.com/reporttopdf.htm
' 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("PaymentCertificate", vbNullString, _
"Z:\A-Database\Payment Certificates\" & strFileName & ".pdf",
False, False, 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.
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
With oMail
.To = strEmailAddy
.body = "Please find attached Payment Certificate. Please invoice
the amount shown and send it to our head office for attention of
accounts."
.Subject = "Payment certificate for invoicing"
.Attachments.Add ("Z:\A-Database\Payment Certificates\" &
strFileName & ".pdf")
.Display
End With
Set oMail = Nothing
Set oLook = Nothing
Case Else
MsgBox ("Error")
End Select
inputresult = MsgBox("Print/Send OK?", 4, ", 2007")
If inputresult = 6 Then
Payment_Retention = Me.Retention * Me.RetentionState
If Me.RetentionState = 0.5 Then
Payment_Retention = 0
End If
'Dateofupdate = Format(DATE, "Short Date") ' not used
SQLUPDATE = "INSERT INTO Payments
(OrderID,Vat1Amount,Vat2Amount,Vat3Amount,Vat4Amount,Retention,[Date],InvoiceNumber,
BarCodeNumber) VALUES (" & Me![OrderID] & "," & InvoiceVAT1 & "," &
InvoiceVAT2 & "," & InvoiceVAT3 & "," & INVOICEVAT4 & "," &
Payment_Retention & ",'" & Now & "','" & InvoiceNumberIFK & "','" &
strFileName & "');"
Me.Previous_ValuationVAT1 = Me.Current_ValuationVAT1
Me.Previous_ValuationVAT2 = Me.Current_ValuationVAT2
Me.Previous_ValuationVAT3 = Me.Current_ValuationVAT3
Me.Previous_ValuationVAT4 = Me.Current_ValuationVAT4
' update statement using retention in ? SQLUPDATE = "INSERT INTO
Payments
(OrderID,Vat1Amount,Vat2Amount,Vat3Amount,Vat4Amount,Retention,[Date],InvoiceNumber)
VALUES (" & Me![OrderID] & "," & InvoiceVAT1 & "," & InvoiceVAT2 & "," &
InvoiceVAT3 & "," & INVOICEVAT4 & "," & (InvoiceVAT1 + InvoiceVAT2 +
InvoiceVAT3 + INVOICEVAT4) / (1 - Retention) * Retention & ",'" & Now &
"','" & InvoiceNumberIFK & "');"
'Dim WorkBase As Database
'Dim WorkRS1 As Recordset
'Set WorkBase2 = CurrentDb
' MsgBox (SQLUPDATE)
'Set WorkRS5 = WorkBase2.OpenRecordset(SQLUPDATE, dbOpenTable,
dbAppendOnly)
'MsgBox (SQLUPDATE)
DoCmd.RunSQL (SQLUPDATE)
DoCmd.RunCommand (acCmdRefresh)
' WorkRS5.Close
' WorkBase2.Close
' Count amount in payments and insert into Amount already paid on
page THERE ARE TWO OF THESE ON THIS PAGE See above
SQVAT1 = "SELECT SUM(Vat1Amount) AS Vat1AmountR FROM Payments
WHERE OrderID=" & Me![OrderID] & ";"
SQVAT2 = "SELECT SUM(Vat2Amount) AS Vat2AmountR FROM Payments
WHERE OrderID=" & Me![OrderID] & ";"
SQVAT3 = "SELECT SUM(Vat3Amount) AS Vat3AmountR FROM Payments
WHERE OrderID=" & Me![OrderID] & ";"
SQVAT4 = "SELECT SUM(Vat4Amount) AS Vat4AmountR FROM Payments
WHERE OrderID=" & Me![OrderID] & ";"
SQPrevRet = "SELECT SUM((Vat1Amount + Vat2Amount + Vat3Amount +
Vat4Amount) / (1 - Retention) * Retention ) AS PrevRet FROM Payments
Where OrderID =" & Me![OrderID] & ";"
Set WorkBase = CurrentDb
Set WorkRS1 = WorkBase.OpenRecordset(SQVAT1)
WorkRS1.MoveFirst
Me![TotalPaidVAT1] = WorkRS1!Vat1AmountR
If Len(Nz([TotalPaidVAT1], "")) = 0 Then Me![TotalPaidVAT1] = 0
Set WorkRS2 = WorkBase.OpenRecordset(SQVAT2)
WorkRS2.MoveFirst
Me![TotalPaidVAT2] = WorkRS2!Vat2AmountR
If Len(Nz([TotalPaidVAT2], "")) = 0 Then Me![TotalPaidVAT2] = 0
Set WorkRS3 = WorkBase.OpenRecordset(SQVAT3)
WorkRS3.MoveFirst
Me![TotalPaidVAT3] = WorkRS3!Vat3AmountR
If Len(Nz([TotalPaidVAT3], "")) = 0 Then Me![TotalPaidVAT3] = 0
Set WorkRS4 = WorkBase.OpenRecordset(SQVAT4)
WorkRS4.MoveFirst
Me![TotalPaidVAT4] = WorkRS4!Vat4AmountR
If Len(Nz([TotalPaidVAT4], "")) = 0 Then Me![TotalPaidVAT4] = 0
Set WorkRS5 = WorkBase.OpenRecordset(SQPrevRet)
WorkRS5.MoveFirst
Me!PreviousRetention = WorkRS5!PrevRet
If Len(Nz([PreviousRetention], "")) = 0 Then
Me![PreviousRetention] = 0
' MsgBox (SQPrevRet)
'Text72.Text = SQA
WorkRS1.Close
WorkRS2.Close
WorkRS3.Close
WorkRS4.Close
WorkRS5.Close
WorkBase.Close
End If
Else
MsgBox ("Error! You are trying to generate a 0 value payment")
End If
End Sub
"Stephen Lebans"
Post the code behind your form that is failing.
--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
I am using A2000ReportToPDF. There is something weird going on, this
maybe
a bug.
The Background...
-It is being launched from a form.
-It works fine for the first record. Get the PDF great.
-It does not work for subsequent records, (using the navigation buttons
or
filter to move to a different record) all you get is the box saying
outputting SNP, then nothing.
The access report works fine for all records, so I don't see why
ReportToPDF doesn't. Weird eh?
Works perfect on A2007
How are you doing ReportToPDF. If you are using the MS Access
functionality, that was added to 2007. It is not a feature in Access
2003.
If you want that capability in 2003, try using Stephen Lebans'
A2000ReportToPDF. I do not know if you can use it with Access 2007.
A2000ReportToPDF is an Access 2000 database containing a function to
convert Reports and Snapshot files to PDF documents. No PDF Printer
driver is required. This archive contains new versions of the
StrStorage
and DynaPDF libraries. Please make sure you delete your old versions,
especially if you copied them into your Windows\System32 folder.
http://www.lebans.com/reporttopdf.htm
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Russell wrote:
Hi..
Can someone quickly point me in the right direction to
troubleshooting
ReportToPDF?
I have not got VisualC++ on my machine, not allowed...
ReporttoPDF is working fine on my A2007 but moved to my boss' machine
A2003, all proud of the new functionality, I press the command
button...it says outputting snp file... then nothing. He laughed, I
scratched. No errors, no files, zilch.
Where do I begin..?
