Update stock table after purchase save button

[accessonly11]

Hi dears

What is the best method to update stock table. I have Purchase form with purchase data form. I want when we press save button on purchase main form, it will add a new record in tblstock, where tblstock.productid = tblpurchaseData.productid,
and tblstock.purchasedQty=tblpurchaseData.Qty
using vba?

 

[jdraw]

I suggest you review this inventory related information from Allen Browne

 

[accessonly11]

it required sturcture like this,

but can i have other options for my sturcture , my relationship pic is like this

 

[jdraw]

You can certainly have a different structure than what Allen used in his example. His structure highlights the concept/technique Inventory Control Quantity on Hand.
If your relationships have been tested and accurately represent the "business processes" to be supported by your database, then proceed. Just ensure that your model matches your needs.
The key point of Allen's article is that you update Quantity on Hand when needed not when each item is bought/sold.

 

[The_Doc_Man]

If your inventory items are individually identifiable (such as having serial numbers) then a table of stock on hand is what is needed. But most stock systems with indistinguishable items (no serial numbers or other individual identification) use a summation query on a list of transactions that will give you essentially a stock balance as a running sum.

 

[accessonly11]

first i was using a union query which get purchase data and sale data with (-) symbol, it gives me available qty correctly,
but now i want to store in stock table, the purchased qty and sold qty everytime i bought and sold.

 

[accessonly11]

is this possible

in this vba i am trying to update stock table purchase qty field, which may also create stock id field automatically. code is not good, i just want to describe my point

 

[jdraw]

For consideration:

Many would suggest a Transaction table where
Incoming records are + and
Outgoing records are -

Summing transactions by Product since last stock taking will show the change in stock and
StockQty at last stock taking + change in stock = QuantityOnHand

 

[accessonly11]

is it possible to put a 2nd subform of stock table on this form which is linked to purchase data. and after update of product id, add new recored in stock purchase qty field which value is equal to this purchase data subform qty field

 

[accessonly11]

For consideration:

Many would suggest a Transaction table where
Incoming records are + and
Outgoing records are -

Summing transactions by Product since last stock taking will show the change in stock and
StockQty at last stock taking + change in stock = QuantityOnHand

i am considring my stock table as transction table

 

[June7]

Possibly why your DLookup doesn't work is the parentheses surrounding field names.

Normally, a transaction would be purchase or sale of items with a single vendor/customer. How can you consider a stock table as a transaction table if it is storing a cumulative calculated value?

 

[accessonly11]

Possibly why your DLookup doesn't work is the parentheses surrounding field names.

Normally, a transaction would be purchase or sale of items with a single vendor/customer. How can you consider a stock table as a transaction table if it is storing a cumulative calculated value?

Dlookup function working ok, in result its getting my required value from another table. The main i need code in green, which i am strugling to make it correct, as i need to add new record in stock table with my specific data.
Stock table will also be used in future for stock adjustments (stock increase, decrease) and sales returns as well as it will be maintain for stock in by getting values from purchase data table and for stock out by getting values from sale data table. I hope i describe my point in my weak English

 

[jdraw]

You may want to create some additional transaction types- for example
stockAdjustment, return...- depending on what you are measuring.
Depending on your requirements, better to get all the facts during design. Much more difficult to add details once the system is operational.

If English is not your native language, then create a response in your language and use Google translate to get and post the English.

 

[Pat Hartman]

Naming all your PKs "ID" does nothing to clarify your schema. It is not cool or useful. It is unfriendly to the reader who can't make sense out of the schema unless he's looking at the layout and that is really inconvenient if the layout won't fit on a single page.

Inventory sounds simple but there are lots of nuances caused by business rules and also by the number of concurrent users. Sometimes, you might have an environment were items move quickly but the order entry process might take a few minutes to complete. For those situations, you have a separate class of usage called "encumbered". That is used for web order entry. You can pick something but if you don't complete the order in a limited amount of time, it goes back into inventory. Or for my wholesale florist app, they allowed pre-ordering. If someone needed more roses than normal because they were doing a wedding the following week, we would order dozens of white roses but mark them as pre-sold so normal orders couldn't pick them up by accident.

You need to know how tight your inventory is. If it is very tight, you need to mark the inventory as "held" or whatever you want to call it when the item is added to the order. Then when the order is finalized, you would change the status to sold or decrease the quantity if you are working with buckets rather than eaches.

 

[accessonly11]

Naming all your PKs "ID" does nothing to clarify your schema. It is not cool or useful. It is unfriendly to the reader who can't make sense out of the schema unless he's looking at the layout and that is really inconvenient if the layout won't fit on a single page.

Inventory sounds simple but there are lots of nuances caused by business rules and also by the number of concurrent users. Sometimes, you might have an environment were items move quickly but the order entry process might take a few minutes to complete. For those situations, you have a separate class of usage called "encumbered". That is used for web order entry. You can pick something but if you don't complete the order in a limited amount of time, it goes back into inventory. Or for my wholesale florist app, they allowed pre-ordering. If someone needed more roses than normal because they were doing a wedding the following week, we would order dozens of white roses but mark them as pre-sold so normal orders couldn't pick them up by accident.

You need to know how tight your inventory is. If it is very tight, you need to mark the inventory as "held" or whatever you want to call it when the item is added to the order. Then when the order is finalized, you would change the status to sold or decrease the quantity if you are working with buckets rather than eaches.

Inventory is very "tight", as it is for medical store where we have to handle 4, 5k items.

 

[Pat Hartman]

It isn't the number of items, it is the number of concurrent order takers and the typical inventory levels that cause extra precautions. You don't want a customer on an order to think you have something in stock when another order taker finished first and now the item is out of stock.

 

[accessonly11]

It isn't the number of items, it is the number of concurrent order takers and the typical inventory levels that cause extra precautions. You don't want a customer on an order to think you have something in stock when another order taker finished first and now the item is out of stock.

i will create database will one user/order taker

my question is still there, how it is possible to add new row in stock table, with specific field value based on purchase order data table

 

[Pat Hartman]

Yes it is possible. Usually you would use an append query or an update query depending on how you keep your inventory.