Cost Overrides on Purchase Orders (cont)

In the workbook for the Cost Overrides the customer needs an easy way to compare the cost of an Item that has had the cost overridden with the Replacement Cost from the current price sheet.  To accomplish this add a new MS Query over the Price Sheet Detail file (PRPMPSD).  Additionally we want to provide the Vendor Name from the Vendor Master file (APPMVEN) and fields from the Vendor Price Sheet Master file (PRPMPSM).

  

We'll return the following fields to our worksheet: Vendor Number, Price Sheet Number, Manufacturer Number, Replacement Cost, Price Sheet Master Type and the Price Sheet Control Number.

The criteria for the query will be PRCD21 = 'C' (we only want to return Price Sheets that are Current) and IVNO07 = [itemnum] (using the brackets allows a value to be passed from the workbook back to MS Query).

The P/S data returned by the query looks this on my worksheet after formatting.

Passing the item number to the query is done from the Main worksheet by selecting a cell in the Item Number column and clicking the "Display Selected Item" button. 

Clicking the button runs code that copies the active cell, selects the Price Sheet Detail worksheet and pastes the Item Number into Cell R5 (row 5 is hidden in the previous screen print).

Now in the Parameters dialog box select the "Get the value from the following cell:" radio button.  Enter the cell reference in the box (the best way to do this is click in the box and then click cell C5).  Click the "Refresh automatically when cell value changes" check box.  Click OK.

Now whenever a new item is selected from the main worksheet and the button is clicked  the query will run and display data for the selected Item Number.

Here's the code used by the "Display Selected Item" button.

This code is used by the "Exit" button to return the user to the Main worksheet.

 

In the next post we'll cover additional enhancements that were requested for this workbook

As always, if you have any questions and/or comments either leave it here as a comment or email me at randomexcel@att.net.