Automatic Stock reduction

[conjoa]

Is there anyway to automatically reduce the quantity of stock. I have set up a table, with a field for the quantity of items in stock. I want to be able to automatically reduce this value at the click of a button, a possible macro???

Q. How is this done?
A. A sales person selects his name from a combo box, then he selects the customers name. Following this he then enters the book(s) ordered by this customer, then the quantity of each needed, and finally the discount. Ideally what I would like is for when the sales person enters the quantity of each book, for the amount in stock (Tbl_books) to be reduced by the amount entered in the qunatity field on the order form. If there are not enough copies of the book available for the order to be completed, then a small message box appears telling the sales person that their are not enough copies left in stock (a possible validation rule). Now this is where it gets tricky. If the sales person enters a incorect value for the quantity of books ordered, i.e. 2 instead of 1, then when he corrects the value, the number of books in stock (Tbl_Books) is to increase by 1.

I have absolutly no idea as to how to approach this, I have attached a printscreen from the relationships page, as well as a printscreen from the order form. Would you like a printscreen from any other table or form?

Relationships:
http://img28.picoodle.com/img/img28/5/11/27/f_Untitled2m_602830c.jpg
Stock Form:
http://img28.picoodle.com/img/img28/5/11/27/f_Untitled1m_0ecc314.jpg

Thanking you in advanced
CJM

 

[conjoa]

Automatic Calculations

Dear Reader

Is their anyway to get access to automatically make calculations at the end of a record, i.e I am creating a stock database, with a order page, where the user selects the products that a customer has ordered, through a combo box, then the user adds the quantity wanted by the customer, and then finally the discount. At the end of the line, I would like a calculation to calculate the line total, and then a box on the side of the form, to show the order total.

The products, quantity and discount are a subform with in the order form. The order form holds the customer's details and the employee, selling the items, details.

The macro, would have to serach for the item price, and then multiply that by the quantity, and then finally the discount, thus calculating the line total. Then it would have to add up all the line totals in the subform and calculate the order total.

CJM

 

[Rich]

Use queries to manipulate data, tables only hold information

 

[CEH]

Well I don't know about "Automatic" ..... But you've basically decribed how to do it...... To get your total items ordered or sold you can put the calculation on your form or in your query... for example....In a blank field in query grid type Totals: =[UnitsOrdered]*[ResalePrice] This is if one of your fields is named "UnitsOrdered" and the other "ResalePrice" or you can put the same in an unbound textbox (called.."Totals" in this example) in your subform..... Control source of "Totals" is..... =[UnitsOrdered]*[ResalePrice]
To get a Sum of your "Totals" use another unbound textbox (say "OrderTotal") on your main form with the control source as your subform "Total" field. Control source of "OrderTotal" like..... =nz(YourSubform.Form!Totals,0)

I think that should point you in the right direction. You can slip in your discounts and whatever you want.... all just simple math... but your adding, multiplying...dividing ... by using field names not figures.

 

[neileg]

Do a search in these forums for stock control or inventory. Generally, storing a stock on hand value is bad practice. It is better to calculate the balance from the transactions in and out.

 

[rsmonkey]

As neil suggested your better off having some way of tracking stock coming both in from a supplie and out in a transaction and then query this to calculate your current stock.