Solved

Avoid negative quantity

Posted on 2014-03-05
5
273 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 35

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
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.

808 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