|The program described on this page is obsolete and has been re-written and available for download here.|
|Integrating the Inventory and Maintenance Log Workbooks|
|With the consolidation of all of the forms and VBA code for the Inventory and Maintenance Log workbooks into a single workbook (InventoryMaint.xls) it was now possible to develop new applications for these workbooks that can process data from both the Inventory and Maintenance Logs. The latest version of the integrated application can be downloaded from the link below.|
|Tracking Parts Usage|
first of the integrated applications was to add parts usage tracking
to the Maintenance Log.
This was done by establishing parts kits in the Inventory applications and attaching those kits to specific maintenance tasks. Then as a task is recorded in the Maintenance Log, the part usage for that task can also be recorded in the inventory.
On the left is a screen capture of the Inventory Kit form displaying the Yanmar Service Parts kit. I established this kit with inventory items that are routinely used to perform maintenance functions on the Yanmar 4JH4-AE engine.
Documentation on how to create and populate an Inventory Kit is provided here.
Complete documentation on the Inventory workbook begins here, and the complete documentation on the Maintenance workbook begins here.
the parts kit established I now had to attached that kit to the
maintenance tasks. On the right I have opened the Create/Edit
Tasks form for the Yanmar engine.
I have selected one of the established tasks, "Change Oil/Filter".
There is a new section on this form highlighted by the re ellipse. This section includes a check box that when set will allow me to attach one of the inventory kits to this task. The kits are listed in the drop-down list to the right of the checkbox. In this list I have selected the Yanmar Service Parts kit.
Once I selected the parts kit for this task, I clicked on the Enter Task button to update the task by attaching this parts kit.
I then repeated these steps for several other engine maintenance tasks (e.g., Change Coolant).
I that I have attached the parts kit to several of the engine tasks
I can start tracking those parts whenever I update the maintenance
In the screen capture on the left I entered data on a recent oil change. Below the Comments text box is a box that now contains the name of the parts kit I attached to this task. Once the basic data for the task (Date and Performed By) have been entered the Update Parts Usage button was enabled.
I clicked on this button the Update Usage of Parts in this Kit form is called up.
This form provides a list of parts in the Yanmar Service Parts kit. There are two columns to the right of the part description column. One identifies how many of each part is available in the kit and the other column identifies how many of each part were used performing the maintenance task.
I select one of the parts in the list (in this case the oil filter
part) the number of filters in the kit are transferred to the Qty of
Item in Kit text box.
To the right of that box is the Qty of Item Used text box, which was intially blank. I have used the Spin control to the right of that box to place a usage quantity of "1" in that box. That is I used one of the oil filters in the inventory in the process of completing the Change Oil/Filter task.
This usage immediately decremented the quantity of filters in the kit from 3 to 2 and the kit listing now shows a Qty Used of 1.
then updated the list to show I used one gallon of 15W40 motor oil
in this task.
At this point the neither the Inventory data base nor the Yanmar Service Parts kit have been updated. If I were to click the Cancel button, these changes to the kit will be discarded.
However, I want to update the Inventory with this usage so I clicked on the Update Kit button.
|After I recorded the Change Oil/Filter task in the log I re-opened the Inventory Kit form and selected the Yanmar Service Parts kit. Now the kit reflects the changes I just made from the Maintenance Log (yellow highlight).|
changes to the Inventory workbook were required to implement
the parts usage tracking. This application uses the standard
The Maintenance workbook was modified. A column was inserted in each Task Sheet where the name of the attached parts kit can be inserted for each task.
The screen capture on the left shows the EngineTaskList worksheet with the Yanmar Service Parts kit attached to 6 of the tasks.
|Generate Shopping List|
version 3.11 a shopping list can be generated for the items in a kit
that are not up to the minimum quantity required.
On the right we can see that the Yanmar Service Parts kit is short 4 items.
(1) Each Oil Filter
(1) Each Coolant Filler Cap
(2) Gallons of Long Life Coolant
Now I need a shopping list for these items so I can restock the kit.
shopping list is a special form of the Kit Report generated by the
I have added a check box under the Kit button in the Report Format section of the form.
this screen capture I have checked the Shopping List format and
selected the Yanmar Service Parts kit. If I had made no Kit
selection the shopping list would be generated for all Kits in the
When I click on the Print button the Shopping list shown below is generated.
|The shopping list contains the 3 items in the kit that are below
the minimum quantity requried. The Long Life Coolant item is 2
units under the minimum, the Oil Filter and the Filler Cap are both
one unit down. I had entered the price of the coolant in the
inventory when this item was first added. That cost as a unit
price and as an extended price are shown in the list. I have
not entered a cost for the Oil Filter nor the Filler Cap. The
list does pick up the manufacturer and part number entries for each
item. Only the part number for the Yanmar Oil Filter was
entered into the inventory.
The Last Source column will contain the name of the store where each item was last purchased. I have not implemented that field in the Inventory Data Base so it is blank for all items.
If the cost of each of the items had been entered into the Inventory the Total Known Cost would provide a good estimate of the cost of this shopping list. As it is the total shown is just that for the coolant.