Access.2007.VBA.Bibl..

Set up a recordset (rstShip) based on the qrySelectedNorthwindShippingLabelsquery,
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

lngOrderID = rstShip![OrderID]

blnShipPartial = rstShip![ShipPartial]

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

strQueryOrder = “qryOrder”

Set dbs = CurrentDb

strSQL = “SELECT * FROM “ & strQueryShip & “ WHERE “ _

& “[OrderID] = “ & lngOrderID & “;”

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

Going Beyond the Basics 12