Stock Control

[wdverner]

Hi ALl,
I have an inventory stock DB. There is a table called Components which contains a list of part#, description for each part counted. There then is another 3 tables - Inventory (What is in stock), Used (what has been used), Received (what has come in). Within each table there is part# (linked to Component table) and the quantity and date the count was taken. From this I can then run current stock totals.

However I wish to create a form to input new stock to each table. What I need is a list of all components, and a field where the user can enter current stock. This will then write the part#, and quantity to the relevant table.

how do I create such a form? It will display the part#, description from component table, and give a field for quantity. when the user enters the quantity it will take the relevant part# and quantity and update the other table e.g. Received.

Hope this makes some sense! Thanks for your help guys

 

[willir]

wdverner

Okay, can we simplify the design? The reason I ask is that Components sounds simialr to Inventory except one has an item count. (Also, it is a good idea to avoid special characters, like "#", when naming fields and tables)

tblInventory
PartNo - primary key
Description
OnHand
OnOrder
ReOrderPoint
Received
LastAuditDate

Discussion:
PartNo instead of Part# so you can reference the field as PartNo instead of having to encapsulate it with square brackets such as [Part#]

OnHand - inventory on hand
OnOrder - inventory that has been ordered
ReOrderPoint or stock point where inventory is re-ordered
Received - inventory received but not "shelved"
LastAuditDate - last date inventory counted

You can tie into to this table...
- Order system (order process for securing new inventory)
- Sales system (sell your inventory)
- Maintenance system (use inventory for maintenance)
...etc

As for the form, instead of using several tables, you can now use one table to add your inventory.

Works as follows:
- Inventory received. Received count entered. If you tie in and Order system, you can track Order or Purchase Order number, and date received.
- Received Inventory verified and stocked / shelved. Number from "Received" added to OnHand inventory.

This is a pretty stripped down / bare bones system. Inventory can be pretty complicated. However, it should suffice for your needs.

...Moving on
You can use the Form wizard to create your form.
For the "Received" process, the end user should select the PartNo. Create a combo box using the wizard "to find a record". They can enter the "Received" amount but not be able to edit the OnHand or OnOrder inventory.

Once the inventory is shelved / stocked, then a command button adds the contents of "Received" to "OnHand" and zeroes out the "Received" amount.

Hint: When working with the form in design mode, make sure the Properties window is open. From the menu, select "View" -> "Properties".

On the "Data" tab of the Properties window, you can lock or disable a control field, ie, OnHand to prevent the fied from being edited by the end user.

Richard