Data Comparison

[kashif_javed]

Hi,

I need some help regarding data comparison/validation. I have two tables
Stock_Master and Stock_Detail containing the details of item received.
Then there are two tables of Invoice_Master and Invoice_Detail containing
the details of item sold.

What i want is when the user enters the item quantity in Invoice_Detail, it
first check the sum of item quantity that exist in Stock_Detail for that
particular item. If the quantity entered is less than the sum of quantity in
stock_detail, only then it allow to save record.
Please anyone guide me how I can put this validation check.

Thanks in advance.

 

[Al Campagna]

kashif_javed,
Several ways to do that... basically...
Make the ItemQty a required field.
I'll assume your inventory system has Credit and Debit entries for
each ItemNo, so that at any time the...
DSum of Item [Credits] - the DSum of Item [Debits] = [QtyOn Hand].

Use the BeforeUpdate event to compare the entered ItemQty against the
DSum of Credits - DSum of Debits in your inventory table.
If the ItemQty is less than OnHand, use the BeforeUpdate...
Cancel = True
ItemQty.Undo
to remove the just entered quantity.
--------------
Or.. use a combo box with all ItemNos in Column 1 and QtyOnHand in
Column 2. That allows the user to see the available OnHand before
ItemQty is manually entered, and allows you to check ItemQty against
the combobox's QtyOnHand column. (instead of doing DSums against the table)
-
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."