not enough stock msg

[steve111]

hi ,

I have 2 forms

one form is made from a query.
the form is called allocation1
the other form (subform) is called stocklist

when they select the material to allocate they then enter the qty to allocate

the stocklist ( which is linked via the material) shows that material along with the stock qty
is it possible to stop the user allocating more than the qty in stock

either by not letting him enter a greater qty or even a message to remind him there is not enough material in stock for the allocation

steve

 

[Mile-O]

Perhaps evaluate the user input in your control's BeforeUpdate property.

i.e.

Private Sub YourTextBox_BeforeUpdate(Cancel As Integer)
    If Me.YourTextBox > Me.TheQuantity Then
        MsgBox "That's too much stock for the quantity", vbExclamation + vbOkOnly, "Too Much Stock"
        Cancel = True
    End If
End Sub

 

[gemma-the-husky]

haven't we already done this steve?

 

[CJ_London]

either by not letting him enter a greater qty or even a message to remind him there is not enough material in stock for the allocation

You need to be clear on your business rules

if they are 'cannot order more than existing stock' I would suggest that

a) they are not allowed to enter a figure greater that the existing stock and
b) they have an option to cancel the order

alternatively if the rule is 'can order more than is in stock but balance goes on back order' then you can allow the order to go through but with a warning that some of the order will be on back order

 

[jdraw]

I agree with Mile-O re the BeforeUpdate, but I'd like o know more about your tables and relationships.
If you have an operational issue with insufficient stock, or "not knowing what stock you have", it is a sign of something missing in your overall processes. It appears you are missing a StockReOrderPoint/Level.

eg:

The Reorder Point is the threshold at which you should order more products to prevent shortages while also avoiding overstock.

I'm sure you have seen Allen Browne's AppInventory- it may help you re StockOnHand.

Good luck.

 

[steve111]

hi

if possible I would like it that they are not allowed to enter a figure greater that the existing stock. But not sure how to do that

steve

 

[jdraw]

I'd still like to see you database design --tables and relationships.

Along this scenario:

Customer makes and Order including Part/Item X as an OrderItem
Before creating(BeforeUpdate) the OrderItem you would determine (for that Part/Item) the current QtyOnHand. If the QtyOnHand > OrderedAmt, then fill the Order. If the QtyOnHand = OrderedAmt then Fill the Order and Recognize/Alert that Part/Iem X has 0 Balance and must be Ordered. If the QtyOnHand<OrderedAmt, Alert that fact, choice here is PartialFillOrder and Issue a BackOrder for Remainder of OrderedAmt. The Customer could say --Forget it, I need it all, now.

(Much of this could be automated with a RecorderPoint for each Part/Item. When QtyOnHand < ReOrderPoint, issue a request for the ReOrderAmt for Part/Item X)

Good luck.

 

[steve111]

hi ,

the database does no work like that.

orders can be on the system up to 2 years in front , the parts required to fulfil orders will look at all orders up to 3 months in advance and these are the parts to order
each order contains many piece parts to fulfil one assembly part ( a capacitor )


Many parts are required to make those capacitors , maybe 70% of the piece parts are the same for each capacitor.

but for example we will use piece part( x)



so the system looks at requirement for 3 months in advance as well as all old orders

<=Now()+91

say the requirement for piece part (x )is 100 to cover all orders up to 3 months
ReqQty: Sum([OrderQty]*[ProductQty])


purchaseqty: Round([StockQty]+[OnOrderQty]+[PendingQty]-Sum([OrderQty]*[ProductQty]),4)

this is run everyweek

hope this explains at bit more

but what they don't have is an allocation record system , so I am trying to create one so I can see what is purchased and what is allocated to a part


but we do change the stock manully
steve

 

[jdraw]

I'd still like to see your tables and relationships.

You appear to have an additional complexity in your latest post. Parts can be parts or assemblies ( a collection of parts) and, if I read it with a little more openness, it probably has a hierarchy of assemblies.

Finished product can be
-a single part
-a collection of parts making a subassembly
-a collection of parts and subassembly(ies) making a sub-subassembly
- etc, etc to several levels.

How do you manage Product component hierarchies?

You may want to read this article and sample database.

 

[steve111]

I have attached some tables and the query that we run for ordering

the products table has the subsheet showing the parts required

steve

 

[jdraw]

What does it mean when you have StockQty > 0 , but no related ProductID?

What unit of measure is associated with ProductQty?
ProductDetailID ProductID Material ProductQty
1 1 Film mPP 11µm x 37.5mm DEM 2.5 PAFN 0.017

ProductDetailID ProductID Material ProductQty
603 53 Epoxy Hardener HX701C/NC (185kg drum) 0.012

What exactly is the What If table?

How do you know which Parts sre subParts of some other Part/Assembly?

 

[steve111]

hi ,

there should have been 4 quires in the attachment so I have attached another
please ignor the stockqty>0 as this is for another purpose, I was going to use that when selecting allocation , as you cannot allocation when you have less than 0

products no = assembly numbers
product details = the parts in that assembly


with the other quires you can see
all the material required for all products( assemblies ) is query "material detail all*"
the "material detail all" query lshows the parts required for all current orders
the " material details 91 days 30 " shows all the itmes that need to be order as we have a negative in stock etc

you will see the unit of measure per material item for that product

steve

 

[jdraw]

there should have been 4 quires in the attachment so I have attached another
please ignor the stockqty>0 as this is for another purpose, I was going to use that when selecting allocation , as you cannot allocation when you have less than 0

This seems to be the same data base as you posted in #10. Only 1 query and 6 tables.
No relationships in relationship window??

You might get some ideas from this generic model related to stock control/inventory management.

http://databaseanswers.org/data_models/customers_and_inventory_mgt/index.htm

 

[RainLover]

Tables are joined by the Primary Key to the Foreign Key. Order Details and Product Detail do no do that.

Using Lookups in a Table will get you into more trouble than you can handle.

Google "The Evils of Lookups" and then read it and apply what you learnt.

Get rid of spaces in the Names of all objects.

I would advise that you attend to these before doing anything else.

BTW LookUps are OK in Forms, just not Tables.