Stock in hand

[Simon Rose]

I am writing an application to manage stock brought in and
sold.
I have set up a table with fields for sales and
purchases.A table of products and a table for transaction
dates etc.
I have designed a form for entering sales ( Stock out)
which works well, however I want a field on the form to
show current stock in hand .

Any ideas ?

I am new to access so any help would be gratefully
recieved.

Thanks in advance

 

[jb]

Hi Simon,

Generally what I do is in the Products Table, I add a field for QtyOnHand
and every time I sell stock, I decrease this field by the number of items
sold and when I bring stock in, I increase this field by the number of items
purchased.

If you do that, then when you put up your Sales screen, when the user
selects a product, you can have the QtyOnHand in the dropdown list (I'm
assuming you set up a dropdown control on your form for products) so that
you can see how many are OnHand of that product. It's just another field
that you show from the products table.

Finally, assuming you have on your form a sub-form with a classic look like
(Qty, Product#, Description, Price, Extension), if you want the field to
also show with these items, then you need to base the subform on a Query and
not just the Transaction table. Make a query that links the Transaction
table to the Products table via the Product Number field. Once you
establish this link, then all the fields in the Transaction table as well as
the Products table become available to you, and then you can simply insert
the Products.QtyOnHand field into your screen and every time a product is
displayed, the QtyOnHand will be displayed along with it. You'll probably
want to lock this field so changes can't be made to it on the screen, but
rather from your routines for adding and subtracting quantities.

hth.

John

 

[Simon Rose]

Thanks John , a great help. Am I right in saying that I
use a SetValue macro to update the QtyOnHand field or is
there an easier way

Regards

Simon