Calculation

[John Paul Angala Pigao]

Please help me calculate quantity inside two separate forms to go to the Main
Stock Form. First, i have a form name Issue Voucher and inside it is a
subform name IVTransactionDetails. Second, i have form name Received Voucher
and inside it is a subform name PRTransactionDetails. Third, I have a Main
Stock Form and we there item id, description, beginning balance and on Hand.
I want to deduct the qty issued in the Issue Voucher Form and add quantity in
the Received Voucher form. Now what i have done is that in the Main Stock
Form i input this command in the On Current (Event Procedure).

Me.QtyonHand = Me.Begbal + DSum("QtyIssued", "PRTransactionDetails", "ItemID
= " & Me.ItemID & "") - DSum("QtyIssued", "IVTransactionDetails", "ItemID = "
& Me.ItemID & "")

But the problem is that when you add qty in the Purchase Received form and
didn't input any quantity in the Issue Voucher for that particular Item ID
will not display anything just a blank field. But if you input qty in the
Issue Voucher and ReceivedVoucher both at the same time qty will be
calculated in the on Hand field of the Main Stock Form.

How can i fix this problem? Can i calculate the on Hand both ways? I mean if
i input data in the Purchased Received Voucher and didn't in the Issue
Voucher it will still calculate and/or vice versa.

Also one more thing would it be possible to make this qtyonhand updated even
if you run a report? Because what is happening right now i need to open the
Main Stock form and update from there the quantity on hand before you will
see the updated on hand in the report. If you run the report without updating
in the Main STock form the qty on hand will never change.

Please help...Thanks in advance.

 

[Allen Browne]

Anything added to Null results in Null, so use Nz() around each of the
values that could be null:

Me.QtyonHand = Nz(Me.Begbal,0)
+ Nz(DSum("QtyIssued", "PRTransactionDetails", "ItemID = " & Me.ItemID),0)
- Nz(DSum("QtyIssued", "IVTransactionDetails", "ItemID = " & Me.ItemID),0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"John Paul Angala Pigao" <John Paul Angala (e-mail address removed)>
wrote in message
news:[email protected]...

 

[John Paul Angala Pigao]

Hi Allen and thank you for the quick response, i really appreciate it. Now it
is working perfectly.

Now allen i have another question with regards to report would it possible
that the on Hand updates itself eventhough you will not go to the Main Stock
Form? On Hand will just update if you look on the particular item id but if
you directly go to the report after you issue an item the on Hand will remain
the same.

Any additional code for that?

Once again, thanks.

 

[Allen Browne]

My view, John, is that you should not have a field in your table to store
the QtyOnHand. Instead, get the database to calculate the value for you when
you need it, using a calculation such as the one shown here:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

In relational database theory, you must not store a dependent value. There
are times to break the rules, but only if you really need to *and* you fully
understand the problems you are creating for yourself. There are just too
many places where this can go wrong, and the stored on-hand value can become
progressively worse and worse the longer the database is used in some cases.

If you really must store it, you could periodically use an Update query to
try to correct the errors that will creep in. But I can't imagine a scenario
where it would be desirable to modify the value by using a report. That
would imply that if you report on an on-hand quantity, the quantity would
change, but if you did not report it it the quantity would not need to
change -- I don't follow that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"John Paul Angala Pigao" <[email protected]>
wrote in message

 

[John Paul Angala Pigao]

Hi Allen, Appreciate your reply/

I would like to try your suggestion but like me a newbie with regards to
database programs it is very hard to understand some of your point.

Function onHand..this term from where you get it? In the relationship window
i didn't see anything like onHand..also where to input that code in the
database..

Do you have a certain access program for it so i can dowload and study from
there? or any procedure to follow that is more understandale for a newbie
like me.

Thanks in advance.

 

[Allen Browne]

Hi John.

The OnHand() function is shown on the website. It starts with:
Function OnHand(...
and ends
End Function

There is not a sample to download, but the relationships window screenshot
shows the tables you woudl use and how they are connected. Agreed that this
is not really newbie fare.

If you want to study further about how to assign specific stock from a
warehouse to orders, you might buy 'Building Access Applications' by John
Viescas. The book has 3 or 4 sample applications on the CD, so effectively
you get the applications for free when you buy the documentation. One of the
apps is for stock control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"John Paul Angala Pigao" <[email protected]>
wrote in message