ms access trigger

I'am developing an ms access database for oders and inventory control. I'am trying to find a way to update the inventory table every time someone place an order.
Can someone please help me?

Hali,

As I understood you need to keep track of the inventory ie. the stock supply. Is this correct?
If so, a solution can be the following:

Generally, it needs two tables, one for the Orders and the other for the goods received to stock.
- When someone place an order, then you append a new record to the Orders table.
- When something enters to the stock then you append a new record to the inventory table.

1. Write a query working on the orders table and sums the quantity ordered, and groups the result rows by goods. Something like this:

SELECT
GoodID,
Sum(Qty) as SumOfOrder
FROM
tblOrder
GROUP BY
GoodID

2. Write a query working on the Inventory table that sums the quantity received to stock, and groups the result rows by goods. Something like this:

SELECT
GoodID,
Sum(Qty) as SumOfEnter
FROM
tblInventory
GROUP BY
GoodID

3. Then write a query that links these queries on GoodID, then it will always show you the current level of each good on stock. Something like this:

SELECT
EnterQuery.GoodID,
EnterQuery.SumOfEnter as Qty_Ordered,
iif(isnull(OrderQuery.SumOfOrder),0,OrderQuery.Sum OfOrder) as Qty_Entered,
EnterQuery.SumOfEnter - iif(isnull(OrderQuery.SumOfOrder),0,OrderQuery.Sum OfOrder) as Balance
FROM
EnterQuery LEFT JOIN OrderQuery
ON
EnterQuery.GoodID = OrderQuery.GoodID

The reason of including iif function is that in the left-join query above the SumOfOrder field may be Null and this can cause a run-time error of 'Invalid use of Null'. You should use the lef-join, because if you have a good on stock, it might be never ordered (newly launched product, for example), and if you don't use the left-join, then you won't see the goods that have never been ordered, but you do have on stock.

I haven't run a syntax check on the queries abve so you may get syntax error, but I don't think that you will.

I hope this helps you.

BRegs,

TBÁrpi

Thank you for your tme!!!
You helped me alot in solving my problem!


Best Regards
grms78

grms78
Is it possible to post sample database you have created for Inventory because I am working on same but could not understand this coding
Sandip

Finally i didn't use the code.
I'm thinking of using macros with update queries but i am not sure yet if it works.
If it works i will send you the database.

grms78

Hi grms 78
Thanks
I willwait but keep me inform or let me know what other problems in this you are facing
Sandip

Hi Sandip

I'm not sure yet but if we use update queries inside macros on afterupdate event of the fields in orders form maybe will work.
For example the query must much the Orders productID with the Inventory productID with the cryteria [Forms]![Orders]![productID]=[Forms]![Inventory]![productID].
Then another cryteria in the same query will minimize the inventory of the specific products. [Forms]![Inventory]![inventory]-[Forms]![Orders]![quantity]

The above rows will be written in the cryteria line in the design view of the update query.

I will keep working on it!

Sorry about my english i know that they are not so good!
grms78

Hi grms 78
Thanks
but as i said i am not very good in coding part so could not understood much but i will also work on it, for your information when I was working on sample databases of microsoft office i found one sample database with inventory control (its exe file and if you click onit it creates database (all tables and forms and reports for Inventory controll).See if its useful to you
Sandip