Stock Control via ComboBox Column on Subform

[LaughingDev]

Hi all,

Ive been working on a simple Access database system for a small business which buys/sells goods. Im currently trying to develop stock control on the CreateOrder form.

The database is similar to the Northwind example, it has 4 tables thus far: Customers, Products, Orders & OrderDetails. ATM the products table simply has a StockLevel field which is being updated by several queries, one upon completion of an order, one for making stock purchases, and one at cancellation of an order.

The CreateOrder form has a OrderDetails subform from which users are able to select the products using a combobox. The combobox uses lookup to show the ProductName(col1) and StockLevel(col5).

What I would like is to have the form not allow users to select Products for which the StockLevel is 0, a MsgBox on the afterupdate event would be ideal as well. However I am not sure whether this is possible to do and how I would go about doing so.

Any help or advice on this would be much appreciated. Thanks in advance.

 

[PNGBill]

Stock Level doesn't need to be a Table Field. It is better to be a SQL / Query result.

Either way, you have one place to get the SOH result.
The Calculated result assists in ensuring the value is reliable.

You can prevent the SKU from listing < = Zero SOH products or you could allow the product to be selected and then give a warning that there is insufficient SOH and lead off to a Back Order option or cancel the line.
This is better handled by an After Update event on the last form control of the item, say OrdQty.
The operator selects the ProductID and then the OrdQty and when leaving that control, the After Update event runs and alerts the operator and may remove the entry.

 

[KenHigg]

Stock Level doesn't need to be a Table Field. It is better to be a SQL / Query result.....

Why do you think it's better?

 

[PNGBill]

When you Calculate the SOH it should always be current where as a field value relies on an Update action to change the value. If something was to go wrong with such an action the field value may be incorrect.
I know this shouldn't happen but a lot of shouldn't happen things do happen.

To Calculate the SOH result you could create a function that also allowed the calculation to return a stock figure for any given date which would be helpful for MonthEnds and or stocktake.

fncQtyOnHand(sku As Long, Optional EndDate As Date)

Advice has regulary been given that Calculated Results should not be stored in a table field, except for historical audit purposes.
Of course there are exceptions and developers choice.

 

[KenHigg]

Thanks for taking time to share your opinon -

I've alway been curious why this advice is given. If a problem could occur when updating when changing the qty then seems logical something could go wrong when entering a data value to sum. If fact, it seems more likely an error could occur there given you could have thousands of records to consider.

Not to mention all of the processing overhead you would have summing stuff.

SAP doesn't sum data like this, wouldn't you think they'd know what they're doing?

I think the calculating rule was meant to be applied across a record and has been mis-applied.

I guess a person can design however they want though

 

[PNGBill]

You are far more experienced then me and I agree for sure regarding a MonthEnd situation regarding processing time.
Most day to day activity would be one record at a time and you wouldn't notice any difference.

I haven't been involved in stock control for many years but remember the old Reports that used to churn out of printers overnight to be filed in the morning and mostly stored away for 7 years.(Carbon capture and storage )
Our old Prime took 30 mins to produce the report and 3 hours to print

Our current business doesn't produce any paper. We do produce .pdf which is similar but not the big reports that were essential to Auditors who growup in the pre computer world.

To get a Suggested Order should require a few calculations that may over shadow the advantage of the current stock being held in a table field but just my guess.

 

[KenHigg]

Not sure about the more experience thing but i am trying to be more open to the way others build their solutions as long as I don't have to support them

 

[LaughingDev]

Thanks for the advice guys,

So is it really not possible to have the AfterUpdate event on the combobox itself where its behaviour would be to either not allow selection of the product that is out of stock or produce a MsgBox and immediately remove that record/product. If it is could I ask for some noob friendly instructions for implementing this.

Thanks.

 

[KenHigg]

I think you are on the right track except you need to be using the before update event. And if the zero qty exists then do a cancel event to cancel updating the record

 

[gemma-the-husky]

you can of course do both -

it is obviously useful to be able to calculate a stock on hand from underlying transactions. it means you can evaluate the stock at any time, rather than just the current time, which is useful for many purposes. The stock ALWAYS calculates correctly - or at least represents the sum of the stored transactions.

on the other hand, the issue about storing a "stock on hand" value is that it is easy for the balance to become out of synch with the underlying data for any number of reasons, and therefore maybe the soh figure needs to be regarded with caution, possibly.

the other issue with stock remains the distinction between quantity and value. Storing stock quantities is tricky, but still doable. Trying to include a valuation for stock transactions adds a major level of complexity.

 

[KenHigg]

you can of course do both -

it is obviously useful to be able to calculate a stock on hand from underlying transactions. it means you can evaluate the stock at any time, rather than just the current time, which is useful for many purposes.

You can do this regardless -

??

on the other hand, the issue about storing a "stock on hand" value is that it is easy for the balance to become out of synch with the underlying data for any number of reasons, and therefore maybe the soh figure needs to be regarded with caution, possibly.

What 'any number of reasons' are at the top of your list?

the other issue with stock remains the distinction between quantity and value. Storing stock quantities is tricky, but still doable. Trying to include a valuation for stock transactions adds a major level of complexity.

I don't really follow this one...

 

[gemma-the-husky]

ken

maybe not quite any number ...

if you store a calculated stock quantity, then you have to write code to modify the stored quantity on every form that allows you to enter/modify a quantity, and any form that adds/deletes stock items. It is easy to miss some of these.

additionally, there is always the possibility that data is changed directly in a table, which clearly will not be reflected in a calculated table

finally, there is the possibility of a "crash" that might leave the calculated table in an incorrect state.


I will come back to the valuation of stock later. my first stab was a bit imprecise

 

[KenHigg]

ken

maybe not quite any number ...

if you store a calculated stock quantity, then you have to write code to modify the stored quantity on every form that allows you to enter/modify a quantity, and any form that adds/deletes stock items. It is easy to miss some of these.

additionally, there is always the possibility that data is changed directly in a table, which clearly will not be reflected in a calculated table

finally, there is the possibility of a "crash" that might leave the calculated table in an incorrect state.


I will come back to the valuation of stock later. my first stab was a bit imprecise

So having to write code is something some programmers don't want to do, this part of the argument could be intrepted as it being easier on the programmer not to do it. Come to think of it, if you do a summed on the fly model then you have the development overhead of how do you handle archiving data, etc. So you'd be back at even

And as far as a 'crash' leaving something out of sinc, a crash could also happen when a user is putting a data value in a summed on the fly column.

I suppose if the data integrity was high enough, a change log should be developed.

I'm just nit-picking (sp?) Developers should develop what they feel they can best build and support

 

[PNGBill]

Thanks for the advice guys,

So is it really not possible to have the AfterUpdate event on the combobox itself where its behaviour would be to either not allow selection of the product that is out of stock or produce a MsgBox and immediately remove that record/product. If it is could I ask for some noob friendly instructions for implementing this.

Thanks.

When you add a product to an Order presumably you first select the Product.
If your Product Selection Control (Combo Box) had any way to check the SOH qty and deny the selection of the product you may be limiting your sales abilities.
You have not yet entered the OrderQty so how is the system to know if there will be enough SOH ?
If there is Zero SOH then what is the system going to do ? Refuse the product to be selected ? or call a message advising Nil stock ?

I would prefer the product to be displayed and maybe a message if Zero SOH but wait for the OrderQty to be entered and at this point advise and act on the stock availability.

As your system develops you may want to take into account Stock On Order and or allow the selected product to be put on back order. Maybe a Command Button becomes Visible or Enabled to allow that product and qty to be placed on Back Order.

I guess the Order Clerk would be happier to see the product and act accordingly then for a pop up message to say Sorry, select different product.

 

[gemma-the-husky]

[response to some earlier posts by KenHigg]

Ken - hopefully this is a clearer explanation of what I see as the issues

If you sum transactions, then you always retrieve the total of the transactions, and it always consistent. The only issue is the possibility that some transactions are missing.

If you try and maintain a total as well as the individual transactions, then it is possible that because of some error, the total is not updated correctly. Therefore you need another function to rework the total from time to time, as well as having to consider the potential that the calculated balance is not correct, when you use it.

So, there is a trade-off between balancing the need for an "instant" though possibly wrong figure, compared with the time spent in evalauting the correct figure based on the underlying transactions.

This is similar to (many) accounting systems that store a total account receivable balance (say) as well as the underlying transactions. There is then always a reconciliation stage that double checks that the transactions agree with the total.

----
now, to get back to the point about the value of the stock. This is much harder than managing the quantity, since each new stock activity has to bear a value. As stock comes in, it is highly likely that new stock is purchased at a different cost to the existing stock. Therefore when the stock is used/sold, the usage needs to reflect the true cost of the actual stock that was sold. Some may have been acquired at one price and some at other prices. Because of this, trying to allocate a stock cost to the transaction is of an order of magnitude harder than monitoring the quantity alone.

There are numerous alternative stock valuation methods, eg FIFO, LIFO, Average Cost, Standard cost. But all of these are alternative ways of looking at the same problem. The underlying problem is splitting the cost of all stock/inventory into two portions. The value of the stock used up within the business, and the value of the stock remaining at any point in time. All of this means that the value of stock used in a business process can be evaluated by several equally "correct" calculation methods, but all of which are very difficult to manage and evaluate in practice.

In actual fact, the various costing systems are not quite equal, as some are accepted by tax authorities, and others not - but from a computational point of view there are similarites. Whichever method youdecide to use to evaluate the value of stock used in a process requires a very complex process to determine that price/value.

As well as all this, stock systems also have to deal with many business processes, such as parts explosions, picking lists, managing partial stock-outs, managing back-orders, providng variance analysis, and so on - all of which explains why good stock/inventory systems are very complex and very expensive.

 

[KenHigg]

...all of which explains why good stock/inventory systems are very complex and very expensive.

And a good example of a very complex and very expensive stock/inventory system is SAP, and it does not sum transactions to get a bin qty

However - if you want to know what your inventory is company wide, then SAP does sum bins across plants. How about that for a truce

 

[gemma-the-husky]

yep

fair enough