Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Avoid negative quantity

Posted on 2014-03-05
5
Medium Priority
?
280 Views
Last Modified: 2014-03-06
I'm storing a quantity of current items in [tbl_quantity].[quantity].  (key field [ID], related to [tbl_items].[id] via one-to-one with referential integrity)

When using Form FRM_ORDER, I don't want to allow the order to exceed the quantity.  (Maybe a message "There are only x quantity on hand, adjust your order now."

What's the easiest way to do this, or does someone have a very simple inventory system (office supplies, not sales) they would like to share?
0
Comment
Question by:TIgerV
  • 2
  • 2
5 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 39907243
Try this in Validation Rule for the required field in the form:

Example to check if value entered in f2 is not more than f1:

Validation Rule: <=[f1]
0
 

Author Comment

by:TIgerV
ID: 39907368
No, that returns a very unintelligible error.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 39907431
Can you reproduce the issue with a sample database?
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39907489
Typically your quantity on hand will be stored in a separate table from the one that holds the transactions.  Many applications do not even store a quantity on hand but calculate it on the fly each time it is needed.  Sounds like you are using method 1.  I think this method is dangerous but you can use it if you are careful.  In the query for the form, join to the inventory table.  That gives you immediate access to the quantity on hand.

In the Form's BeforeUpdate event:
If Me.txtAmt > Me.qtyOnHand Then
    Msgbox  "You have requested more items than we have available.  Please correct.",vbokOnly
    Me.txtAmt.SetFocus
    Cancel = True
    Exit Sub
End If

Me.qtyOnHand = Me.qtyOnHand - Me.txtAmt
 

Open in new window


The "If" will ensure that you don't request more than are available and exit the sub if you do.  The last statement will decrement the quantity on hand and Access will save the inventory record along with the transaction record.

Now - the reason this is dangerous is because you are almost certainly working in a multi-user environment.  There may be sufficient qty on hand when you look but someone else might save before you do and take that quantity away.  If someone else is working on the same item as you but managed to save before you did, you will get a very confusing error message giving you three options.  You need to instruct (and trust) your users to choose the discard option and start again with their transaction.
0
 

Author Closing Comment

by:TIgerV
ID: 39910085
Thank you!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question