Stock Control

[alxj]

I need to create a section which ammends underlying stock levels in my product table when and order is placed in my order form. I am currently using a Main form (client details) and 2 subforms (orders & order details) as my order form. Is this possible or am i asking too much from access.
Thanks for your help
Alex

 

[Fornatian]

I think the way I would do it - ensuring you have a table of your stock items with a corresponding no in stock field is to build an update query with both these fields in the QBE grid,
in the stock item criteria row reference the stock item on the subform
(hint: you are best building the query while the form is open in the background, then you can just right click in the criteria row and choose build, from there find your field name in the 'loaded forms' section, if the form name has a plus sign it means there is a subform).

In the 'update to' field you will need to calculate the new stock items number. I'd do it using a DLookup() funtion to return the old number in stock figure and take it away from the items remaining on the form like so:

DLookUp("StocksNumber","[TestforUpdateQuery]","[StockName]=" & Forms!MyForm!MyStockName)-Forms!MyForm![NoOfStockOrdered]

I've got it to work but it was a bit tricky.
Good luck - if it's a small file e-mail me with it and I'll have a look.

Ian


[This message has been edited by Fornatian (edited 11-26-2000).]

 

[Richie]

Storing calculated and constantly changing values in a table is not recommended or neccesary use an unbound textbox or a query.