Overview

Another LOB app is added to the Access project to allow users to page through suppliers with orders for Northwind in the pipeline showing the products and quantities on order from the supplier. When a button on the page is clicked, updates are made to reflect full receipt of products with positive units on order from the supplier, adding the received unit quantities to units in stock and zeroing units on order. Internal Audit has also asked for some audit trail data about who and when made the last receipt from a supplier.

Outline of Development

In your Northwind DB

  • Create a view vwSuppliersWithOrders that selects SupplierID, CompanyName and ContactName from Suppliers where there are any products for the supplier with UnitsOnOrder > 0. Hint: Select from suppliers with a WHERE clause that has SupplierID in (select SupplierID from Products where UnitsOnOrder > 0)
  • Create a view vwProductsOnOrder that selects ProductID, ProductName, UnitsOnOrder, SupplierID from Products with UnitsOnOrder > 0
  • Add columns LastReceivedDT datetime nulls allowed and LastReceivedBy varchar(50) nulls allowed to the Suppliers table
  • Create a sproc spReceiveOrder that takes a SupplierID as a parameter; if the parameter is missing or null return 1, if there is no such SupplierID return 2, if the supplier has no products with UnitsOnOrder > 0 return 3; otherwise update the Suppliers table LastReceivedDT to the current datetime and LastReceivedBy to the user login executing the sproc -- also update any products for the SupplierID adding UnitsOnOrder to the current UnitsInStock and setting UnitsOnOrder to zero. Be sure to test that the sproc works before moving on.
  • Provide user vm-1\AIST3410 SELECT privilege to the two new views and EXECUTE privilege to the new sproc.

In your development version of NorthwindApps.accdb

  • Create linked tables to your two new views but identify SupplierID as the key for vwSuppliersWithOrders and ProductID as the key for vwProductsOnOrder
  • Add the two new linked tables to relationships joining them by SupplierID
  • Create a form / subform ReceiveOrder / sfrmReceiveOrder that allow the user to page through suppliers and see any products for the supplier with UnitsOnOrder > 0; clean up the form as we did for frmFulFillOrder
  • Add a button to the form with text Receive Order which, when clicked, will execute the spReceiveOrder sproc passing whichever SupplierID the user happens to be viewing when they clicked it; include a requery of form data when the sproc completes successfully (so the suppliers showing on the form actually have positive units on order ... the most recently received supplier should no longer appear)

Re-deploy NorthwindApps.accdb and verify it runs correctly for user AIST3410.