Database Help

[Simon Glencross]

I have created a database which hold customer details, stock control and
sales.

I am having difficulty with the following.

When I sell and Item I need this to be deducted from the total stock for the
item which has been sold, what is the best way to do this?

I have the following tables.

tblinvoice
Invoice ID(pk)
CustomerID(fk)
InvoiceDate
InvoiceNotes



Tblinvoicedetails
InvoiceDetailID (pk)
InvoiceID(fk)
Quantity
PriceEach
ItemCode(fk)

tblproducts
Itemcode (pk)
Description
productgroup
rrprice
salesprice

tblcurrentstock
Itemcode (pk)
Quantity


Any help would be much appreciated.

 

[ruralguy via AccessMonster.com]

MVP Allen Browne covers this pretty well.
http://allenbrowne.com/AppInventory.html

 

[J. Goddard]

Hi -

You can use sql to update the tblCurrentStock table; this would be best
done in the form which deals which enters the invoice details:

Dim SQL as string
SQL = "Update tblcurrentstock set [quantity] = quantity - " &
me![quantity] & " where [ItemCode] = " & me![itemcode]
Currentdb.execute SQL

John

 

[Simon Glencross]

Ok, I have looked at that but I would prefer the quantity to be taken from
the stock as I enter the sale as I would like the database to also tell me
if the item is out of stock.

Any other ideas?

p.s. thank you for your help!!

 

[Steve Schapel]

Simon,

I think that generally it is not a good idea to store current stock. I
would remove the tblcurrentstock table from your database altogether...
then you don't need to worry about updating it. If you are recording
stock in and stock out, the current stock can easily be returned via a
query, whenever you want this information.

There is some good information on this topic at
http://www.allenbrowne.com/AppInventory.html

 

[J. Goddard]

You could check the quantity available in the before update event of the
[quantity] control in the form to enter invoice details.

john

 

[Simon Glencross]

Ok,

I have looked at this and customised it I think correctly, do I put the code
under the click event of a command button? Sorry new to this and getting to
know the ins and outs.

Thanks

 

[Simon Glencross]

Ok,

I have looked at this and customised it I think correctly, do I put the
code under the click event of a command button? Sorry new to this and
getting to know the ins and outs.

Thanks