Solved

Avoid negative quantity

Posted on 2014-03-05
5
269 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 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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Direct Mail software 4 42
MS Access XML API HTTP POST Call Object Send Not Working Correctly 15 34
Sub Reports 8 21
Export Query data to excel file 14 28
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now