Solved

Avoid negative quantity

Posted on 2014-03-05
5
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 30

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 30

Expert Comment

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

Accepted Solution

by:
PatHartman earned 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

733 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