Figure

The new PDF selection for saving a database object.

FIGURE 12.30

An email message with an attached PDF file created from an Access report.

FIGURE 12.30

Clicking the "Send Reorder Requests" button works similarly; it creates a PDF file from the Products to Reorder report and emails it as an attachment. Figure 12.31 shows the Products to Reorder report.

FIGURE 12.31

The Products to Reorder report.

FIGURE 12.31

The Products to Reorder report.

The cmdReorderlnventory event procedure is listed here: Private Sub cmdReorderInventory_Click()

This error handler skips to the CreateSnapshot section if there is an error on the line that outputs the file to the PDF format.

On Error GoTo CreateSnapshot strCurrentPath = Application.CurrentProject.Path strReport = "rptProductsToReorder"

First try to export the Products to Reorder report to PDF (this will only work if you have installed the Save to PDF utility)

strReportFile = strCurrentPath & "\Products To Reorder.pdf" Debug.Print "Report and path: " & strReportFile DoCmd.OutputTo objecttype:=acOutputReport, _ objectname:=strReport, _ outputformat:=acFormatPDF, _ outputfile:=strReportFile

If the PDF file was created successfully, go to the CreateEmail section, skipping the CreateSnapshot section of code.

GoTo CreateEmail On Error GoTo ErrorHandler CreateSnapshot:

Export the report to snapshot format.

strReportFile = strCurrentPath & "\Products To Reorder.snp" Debug.Print "Report and path: " & strReportFile DoCmd.OutputTo objecttype:=acOutputReport, _ objectname:=strReport, _ outputformat:=acFormatSNP, _ outputfile:=strReportFile

CreateEmail:

Create an Outlook email message, fill in its subject, and attach the PDF or snapshot file to the message:

Set appOutlook = GetObject(, "Outlook.Application") Set msg = appOutlook.Createltem(olMailltem) msg.Attachments.Add strReportFile msg.Subject = "Products to reorder for " _

& Format(Date, "dd-mmm-yyyy") msg.Save

Ask for confirmation to set all ReorderAmount values to zero, and add the amount ordered to UnitsOnOrder:

strTitle = "Confirmation"

strPrompt = "Clear reorder and on order amounts?" intReturn = MsgBox(strPrompt, vbQuestion + vbYesNo, _

strTitle) If intReturn = vbYes Then DoCmd.SetWarnings False strSQL = "UPDATE qryProductsToReorder SET " _ & "qryProductsToReorder.UnitsOnOrder = " _ & "[UnitsOnOrder]+[ReorderAmount], " _ & "qryProductsToReorder.ReorderAmount = 0;" Debug.Print "SQL string: " & strSQL DoCmd.RunSQL strSQL End If

Display the Outlook email message with the PDF or snapshot attachment: msg.Display

DoCmd.Close acForm, Me.Name

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

0 0

Post a comment