Figure

The main menu of the Northwind Plus sample database.

Northwind Plus

rfp

Backup

M

Exit

0

Select Orders for Shipping

Email Shipping and Reordering Reports

1 Documents Path... J

E:\Do cuments\

1 Templates Path... ]

E:\Ternplate s\

I could have determined which orders were ready to ship just by examining the available inventory and the date the order is required, but in the real world these are not the only factors to consider — if there aren't enough people to do the shipping, or all the trucks are out on the road, you can't ship the product, even if there is enough inventory.

Figure 12.3 shows the order selection form.

FIGURE 12.3

The form used for inspecting orders and marking them for shipment.

FIGURE 12.3

The form used for inspecting orders and marking them for shipment.

The Select Orders for Shipping form (frmSelectOrdersForShipping) is a main form with a datasheet subform. It is used to inspect orders that are in the correct time range for shipping and mark them for shipping now. The main form displays fields from the orders, and the datasheet subform lists the products on the selected order. On the main form, the "Ship Partial" checkbox is checked if a partial order can be shipped, in case there is enough inventory to ship at least one product on the order. The "Ready to Ship" checkbox indicates that the order is ready to ship, taking into account factors over and above the available inventory.

To print shipping labels with extra information, I made a query

(qryNorthwindShippingLabels), based on qryNorthwindAll (a query that includes all the linked Northwind data tables), containing all the information to print on the labels. Apart from the shipping name and address, the query also includes the following fields:

■ RequiredDate (with a criterion of >Date()) And <DateAdd("d",3 0,Date( just include orders required from tomorrow to less than 30 days in the future

■ DateShipped

■ Supplier (an alias for CompanyName in tblSuppliers)

■ ProductName

■ NoCases (an alias for Quantity)

■ CategoryName

■ ReadyToShip, a Boolean field with a criterion of True to select orders that are ready to ship

The cmdCreateLabels event procedure is listed here, with explanation of how it processes the sets of labels to print:

Private Sub cmdCreateLabels_Click()

On Error GoTo ErrorHandler

Dim appWord As Word.Application Dim blnShipPartial As Boolean Dim dbs As DAO.Database Dim doc As Word.Document Dim fil As Scripting.File

Dim fso As New Scripting.FileSystemObject

Dim lngCaseNo As Long

Dim lngNoCases As Long

Dim lngCasesInStock As Long

Dim lngCount As Long

Dim lngSet As Long

Dim lngNoSets As Long

Dim lngOrderID As Long

Dim lngSetNo As Long

Dim lngSubtract As Long

Dim rstOrder As DAO.Recordset

Dim rstShip As DAO.Recordset

Dim strCategory As String

Dim strDocsPath As String

Dim strOrderDate As String

Dim lngProductID As Long

Dim strProductName As String

Dim strPrompt As String

Dim strQueryShip As String

Dim strQueryOrder As String

Dim strSaveName As String

Dim strSaveNameAndPath As String

Dim strShipAddress As String

Dim strShipCityStatePC As String

Dim strShipCountry As String

Dim strShipDate As String

Dim strShipName As String

Dim strSQL As String

Dim strSupplier As String

Dim strTemplate As String

Dim strTemplateNameAndPath As String

Dim strTemplatePath As String

Dim strTitle As String

Dim varValue As Variant

Set the Word Application variable:

Set appWord = GetObject(, "Word.Application") appWord.Visible = True

Get User Templates and Documents paths from the user's selections on the main menu, using two functions that pick up the saved paths from tblInfo:

strTemplatePath = GetTemplatesPath Debug.Print "Template path: " & strTemplatePath strDocsPath = GetDocumentsPath

Debug.Print "Documents folder: " & strDocsPath strTemplate = "Avery 5164 Shipping Labels.dotx" strTemplateNameAndPath = strTemplatePath & strTemplate Debug.Print "Template name and path: " _ & strTemplateNameAndPath

On Error Resume Next

Look for the template in the templates folder, by attempting to set a FileSystemObject File variable to it:

Set fil = fso.GetFile(strTemplateNameAndPath) If fil Is Nothing Then strPrompt = "Can't find " & strTemplate & " in " _

& strTemplatePath & "; canceling" MsgBox strPrompt, vbCritical + vbOKOnly GoTo ErrorHandlerExit End If

On Error GoTo ErrorHandler

Calculate the number of sets of labels to print:

"qrySelectedNorthwindShippingLabels")) Me![lblSetsToPrint].Caption = lngSelected _ & " sets of shipping labels to print"

Exit with a message if no orders have been selected:

If lngSelected = 0 Then strTitle = "Can't print labels"

strPrompt = "No orders selected; please mark some " _

& "orders for shipping" MsgBox strPrompt, vbExclamation + vbOKOnly, strTitle GoTo ErrorHandlerExit End If

Set up a recordset (rstShip) based on the qrySelectedNorthwindShippingLabels query, which has one record for each product on a selected order:

Set dbs = CurrentDb strQueryShip = "qrySelectedNorthwindShippingLabels" Set rstShip = dbs.OpenRecordset(strQueryShip)

Get the number of records for use in updating the progress meter:

rstShip.MoveLast rstShip.MoveFirst lngNoSets = rstShip.RecordCount

Start the progress meter in the status bar, using the SysCmd object:

strPrompt = "Creating " & lngNoSets _

& " sets of shipping labels" Application.SysCmd acSysCmdInitMeter, strPrompt, _ lngNoSets

Set up a loop for processing the sets of labels for the orders:

For lngSet = 1 To lngNoSets lngOrderlD = rstShip![OrderID] blnShipPartial = rstShip![ShipPartial]

Create a filtered recordset (rstOrder) for this order only, with records corresponding to the products on the order:

strQueryOrder = "qryOrder" Set dbs = CurrentDb strSQL = "SELECT * FROM " & strQueryShip & " WHERE " _

& "[OrderlD] = " & lngOrderlD & ";" Debug.Print "SQL for " & strQueryOrder & ": " & strSQL lngCount = CreateAndTestQuery(strQueryOrder, strSQL) Debug.Print "No. of records found: " & lngCount

Set rstOrder = dbs.OpenRecordset(strQueryOrder)

Set up a loop to process each product on this order, checking whether there is enough inventory to ship the product on this order:

Do While Not rstOrder.EOF

lngProductID = rstOrder![ProductID] strProductName = rstOrder![ProductName] lngNoCases = rstOrder![NoCases] lngCasesInStock = rstOrder![CasesInStock] If lngNoCases > lngCasesInStock Then If blnShipPartial = False Then

For orders with ShipPartial unchecked, can't ship the order because one product has inadequate inventory:

strTitle = "Inadequate inventory" strPrompt = "Only " & lngCasesInStock _

& " cases in inventory; can't fill Order ID " _ & lngOrderlD & " for " & strProductName MsgBox strPrompt, vbExclamation, strTitle GoTo NextOrder Elself blnShipPartial = True Then

For orders with ShipPartial checked, can't ship this product on the order:

strTitle = "Inadequate inventory" strPrompt = "Only " & lngCasesInStock _ & " cases in inventory; can't fill " _ & strProductName & " item on " _ & "Order ID " & lngOrderID MsgBox strPrompt, vbExclamation, strTitle

Figure 12.4 shows a typical "Inadequate inventory" message when there isn't enough inventory to fill a product line item on an order with ShipPartial checked.

FIGURE 12.4

A message indicating that there is inadequate inventory to ship a product item on an order.

i Only 29 cases in inventory; can'tfiFI Pavlova item on Order ID 10605

Check the next product on the order:

GoTo NextProduct End If Else

There is enough inventory to ship this product; create a new labels document for this set of labels from the template:

appWord.Documents.Add(Template:= _

strTemplateNameAndPath, _ documenttype:=wdNewBlankDocument, _ Visible:=True) doc.Activate

Set variables with information to print on all the labels for this order:

strCategory = rstOrder![CategoryName] strOrderDate = CStr(rstOrder![OrderDate]) strShipName = rstOrder![ShipName] strShipAddress = rstOrder![ShipAddress] strShipCityStatePC = rstOrder![ShipCityStatePC] strShipCountry = rstOrder![ShipCountry] strSupplier = rstOrder![Supplier] strShipDate = Format(Date, "dd-mmm-yyyy")

Set up a loop to print a set of labels for this order, one label per case shipped:

For lngCaseNo = 1 To lngNoCases With appWord.Selection

Put data into one label (one cell in the Word document):

.TypeText Text:="FROM:" & vbTab .MoveLeft Unit:=wdCharacter, Count:=1 .MoveLeft Unit:=wdWord, Count:=2, _

Extend:=wdExtend .Font.Bold = True .EndKey Unit:=wdLine .Font.Bold = False

.TypeText Text:="Northwind Traders" .TypeParagraph

Indent the left margin to match the tab setting, so the address will line up with the name.

Instead of looking up Word methods, properties, and other object model components in the Object Browser, you can capture the syntax for a Word action by recording a macro in Word, then copying and pasting the VBA code into your Access VBA procedure. Just insert your Word application variable where needed and trim the arguments you don't need to prepare the code for use in Access.

.ParagraphFormat.Tablndent (1) .TypeText Text:="2839 El Presidio St." .TypeParagraph

.TypeText Text:="Nowhere, WA 92 83 8" .TypeParagraph

Return to the normal left margin before printing "TO:":

.ParagraphFormat.Leftlndent = 8 .TypeParagraph .Font.Bold = True .TypeText Text:="TO:" & vbTab .MoveLeft Unit:=wdCharacter, Count:=1 .MoveLeft Unit:=wdCharacter, Count:=3, _ Extend:=wdExtend

.EndKey Unit:=wdLine .Font.Bold = False .TypeText strShipName .TypeParagraph

Indent the left margin to match the tab setting, so the address will line up with the name:

.ParagraphFormat.Tablndent (1) .TypeText strShipAddress .TypeParagraph

.TypeText strShipCityStatePC .TypeParagraph .TypeText strShipCountry .TypeParagraph

Return to the normal left margin before printing the extra information:

.ParagraphFormat.Leftlndent = 8 .TypeParagraph .Font.Size = 10 .Font.Bold = True .TypeText "Order ID:" & CStr(lngOrderlD) .TypeParagraph .TypeText "Category:"

& strCategory .TypeParagraph .TypeText "Product: " & lngProductID & " & strProductName & .TypeParagraph .TypeText "Supplier:"

& strSupplier .TypeParagraph .TypeText "Ship date:

& strShipDate .TypeParagraph .Font.Size = 12 .Font.Bold = False .TypeParagraph .TypeText vbTab & "Case " _

& lngCaseNo & " of " & lngNoCases .MoveRight Unit:=wdCell End With Next lngCaseNo

Save the Word labels document for this set of labels:

strSaveName = "Shipping Labels for Order ID " & lngOrderlD & " (" & strProductName _ & ") shipped on " & strShipDate & ".doc"

strSaveNameAndPath = strDocsPath & strSaveName Debug.Print "Save name: " & strSaveName

On Error Resume Next

Check for the existence of a file with this name, and delete it if found:

Set fil = fso.GetFile(strSaveNameAndPath) If Not fil Is Nothing Then Kill strSaveNameAndPath End If

On Error GoTo ErrorHandler doc.SaveAs FileName:=strSaveNameAndPath

Update the progress meter:

Application.SysCmd acSysCmdUpdateMeter, lngSet

Update the ReadyToShip field in tblOrders to False:

DoCmd.SetWarnings False strSQL = "UPDATE tblOrders SET " _

& "tblOrders.ReadyToShip = False " _ & "WHERE OrderlD = " & lngOrderlD Debug.Print "SQL string: " & strSQL DoCmd.RunSQL strSQL

Subtract the amount of product shipped from the in stock amount in tblProducts:

lngSubtract = lngCaseslnStock - lngNoCases strSQL = "UPDATE tblProducts SET " _ & "tblProducts.UnitslnStock = " _ & lngSubtract & " WHERE ProductID = " _ & lngProductID Debug.Print "SQL string: " & strSQL DoCmd.RunSQL strSQL

In tblOrderDetails, set QuantityShipped to QuantityOrdered, and DateShipped to todays date:

strSQL = "UPDATE tblOrderDetails SET " _ & "tblOrderDetails.QuantityShipped = " _ & "[QuantityOrdered], " _

& "tblOrderDetails.DateShipped = Date() " _ & "WHERE tblOrderDetails.OrderlD = " _ & lngOrderID _

& " And tblOrderDetails.ProductID = " _ & lngProductID & ";" Debug.Print "SQL string: " & strSQL DoCmd.RunSQL strSQL

strTitle = "Set of labels created" strPrompt = _

"A set of shipping labels created " _ & "for Order ID " & lngOrderlD _ & ", Product ID " & lngProductID _ & " (" & strProductName & ")" MsgBox strPrompt, vblnformation, strTitle

Figure 12.5 shows the success message for the last set of labels, with the progress meter at full in the Access window status bar.

Figure 12.5 shows the success message for the last set of labels, with the progress meter at full in the Access window status bar.

End If

NextProduct:

rstOrder.MoveNext Loop

NextOrder:

rstShip.MoveNext

Recalculate the number of sets of labels to print:

"qrySelectedNorthwindShippingLabels")) Me![lblSetsToPrint].Caption = lngSelected _ & " sets of shipping labels to print" Next lngSet

DoCmd.Close acForm, Me.Name

Finished:

strTitle = "Finished!" strPrompt = _

"One set of shipping labels created for each "

& "order shipped on " _ & Format(Date, "dd-mmm-yyyy") MsgBox strPrompt, vblnformation, strTitle

ErrorHandlerExit:

Figure 12.6 shows the "Finished!" message after all the sets of labels have been created.

FIGURE 12.6

The success message after all labels have been printed.

Finished!

One set of shipping labels created for

ach order shipped on

'if'1 12-Jan-2007

rsn

Clear the progress meter:

Application.SysCmd acSysCmdClearStatus

Exit Sub

ErrorHandler:

If Err = 429 Then

Word is not running; open Word with CreateObject:

Set appWord = CreateObject("Word.Application") Resume Next Else

MsgBox "Error No: " & Err.Number & "; Description: " _

& Err.Description Resume ErrorHandlerExit End If

End Sub

The GetDocumentsPath function that retrieves the Documents path from tbllnfo is listed next; the GetTemplatesPath function is similar:

Public Function GetDocumentsPath() As String On Error GoTo ErrorHandler Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblInfo")

rst.MoveFirst

GetDocumentsPath = rst![DocumentsPath] & "\" rst.Close

ErrorHandlerExit: Exit Function

ErrorHandler:

MsgBox "Error No: " & Err.Number & "; Description: " &

Err.Description Resume ErrorHandlerExit

End Function

Figure 12.7 shows a page of shipping labels.

FIGURE 12.7

A set of Word shipping labels filled with data from Access.

FIGURE 12.7

A set of Word shipping labels filled with data from Access.

Every development project has unique requirements; when using Access to create mailing labels (or any other type of Word documents), you have the freedom to create tables and forms that exactly meet your needs, storing all (and only) the information you need to select and print the data you need on Word documents. The code used to create the shipping labels described in this section includes several techniques that are useful for producing Word documents of various types, such as creating filtered recordsets for working with detail records, calculating "x of y" numbers when working with sets of records, displaying a progress meter in the status bar, and updating tables from code.

0 0

Post a comment