Update Inventory [In Stock] Amount (Access 2003/SP

[79schultz]

How can I update the 'In Stock' amount in a 'Products' table when I am entering orders. For example, 'Products' could have 50 apples 'in stock'. I enter an order of 10 apples on an 'Orders' form and the 'in stock' would automatically be updated to 40. Is this done through an update query?

[HansV]

You can either run an update query or use DAO or ADO code to open a recordset on the Products table, locate the relevant record and edit it.

You must be very careful how you do this. For example, you don't want to update the Products table immediately when the user enters a number in the Orders form - the user might have made a mistake, and have to change the number. You should also provide a mechanism for changing the size of the order after it has been confirmed - the stock should then only be changed by the difference between the old and new order size.

If I were to design an inventory database, I'd use unbound forms to have complete control over the way data are stored.

[79schultz]

OK, that sounds like standing on the seashore and seeing a goal as a speck in the ocean.

I'm not sure what you mean by an unbound form. My order entry is made on a subform. You can't have a subform be an unbound form, can you?

[HansV]

An unbound form is a form whose Record Source is blank. This means that you will need code to load existing data into the form, and also to save the entered data into a table (or tables).

A subform can be unbound, but it cannot be a continuous or datasheet form.

[79schultz]

I'm 'trying' to design a database for a charity group that works with used and new clothing. It needs to be something very simple, the users aren't highly computer literate. "I don't seem to be either". That's one thing they want is an updated Inventory status. I understand the need to be cautious about changing the order. Should I run an update query based on order numbers at the end of the day? ADO and DAO are not my specialty, although if I had help I'd be willing to give it a try.

[HansV]

An update query is probably the easiest solution.

[Support4John]

If your still in the design stage you may want to Check out:

<post#=362044>post 362044</post#>
<post#=526954>post 526954</post#>

HTH, John

[79schultz]

Thanks for that tip. I'll see if I can wade through that.

[79schultz]

Ok. I am trying to wade through this code and I think I will like it, but I'm not sure I understand it all. Is this supposed to create a table, a form, or where do I see the calculated values?

[MarkLiquorman]

You might want to reconsider trying to store the current inventory level. It is frought with potential problems; that is, there is always uncertainity as to whether you have properly updated the level in every situation that might occur. Usually a better solution is to derive the current inventory level by using queries of inventory ins and outs.

[Support4John]

Find attached InvCtrl.zip, contains

Calculate Stock by Tom Pugh code and
Inventory Control: Quantity on Hand by Allen Brown code

----------------------------------------------------------------------
Tom Pugh Code: Google "Tom Pugh" Inventory bluvision

Open frmPart, Open Inventory, Received and Invoice and study the inventory detail records

Enter Part Number 123 or 234 and click Calculate Stock

Study the code behind Calculate Stock

-----------------------------------------------------------------------
Allen Brown Code:

Open Module mod_InventoryControl-Quantity_On_Hand, run and study code

HTH, John

[79schultz]

I just got back to work after the weekend. I'll check into that. Thanks a lot.