Re: Computation in one form from the other forms

On this same form you should have a field that contains the item number, I'll
assume its called txtItemNumber for this example, then yo would use;

Me.QtyonHand = Me.Begbal - DSum("QtyIssued",
"tbl_Transaction_Details", "ItemNumber = " & Me.txtItemNumber & "")

instead, which refers to the curremt value of the field txtItemNumber
instead of a fixed value, so, as long as it is bound or updated through code
when the record changes, you can use the AfterUpdate event of THAT
(txtItemNumber) control to enter the code above, so the QtyOnHand updates
automatically for each new record selected.

good luck

TonyT..

"jppigao@xxxxxxxxx" wrote:

Hi tony and once again thank you sir for the quick response.

Now i am feeling that we are getting closer to the main goal of this
database. Now this idea you have given me is good and works very well.
But here is a problem i encountered

Now what i have done is to input the expression below:

Me.QtyonHand = Me.Begbal - DSum("QtyIssued",
"tbl_Transaction_Details", "ItemNumber = 190001")

in the form of the Main Stock where i have a field of Begbal
(Beginning Balance) and a field for QtyonHand (Qty on Hand)

I place it in the QtyonHand field properties under "On Click" and i
create an event procedure.

Now it works when i open the Main Stock form choose the item 190001
and quantity is being calculated in a flash but when i choose another
item like 190002 the problem now come, The total qty sold by 190001 is
deducted also to the Beginning balance of 190002 so basically we are
only getting one calculation for all of items in the table.

So is there any way to separate the expression from one itemid to
another or one good thing is to have a general code for one form but
will define which item should only be calculated.

Like..

I choose 190001 it will just calculate what's for him and then i
scroll and look for other item and it calculate the qty on hand for
it's own.

Now FYI i have a table of all the items approx 5000 items listed in my
table.

Tony your first idea is good and i am looking forward to use this
expression because i need to have another table and form for receiving
items so i can add stock whenever t become zero.

Me.NameOfTexboxToShowQty =
DSUM("QtyBookedIn","tblWithQtyBookedIn","ItemNumber = x") -
DSUM("QtySold","tblWithQtySoldIn","ItemNumber = x")

Have a nice day! God bless!

John Paul Angala Pigao



On Jan 29, 4:23 pm, TonyT <To...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
without knowing form field or table field names I can't give an accurate
example, but;

Me.NameOfTexboxToShowQty =
DSUM("QtyBookedIn","tblWithQtyBookedIn","ItemNumber = x") -
DSUM("QtySold","tblWithQtySoldIn","ItemNumber = x")

all on one line, replacing fields and x with appropriate values

If you are doing this on a form bound to the main table with Qty in, you
could have a bound textbox showing original qty in and save time with;

Me.NameOfTexboxToShowQty = Me.txtBoxWithQuatityInStock -
DSUM("QtySold","tblWithQtySoldIn","ItemNumber = x")

have a look at the DSum function in help for more examples and syntax

hth

TonyT..



"jppi...@xxxxxxxxx" wrote:
Hi Tony and thanks for the very quick response. But i'm kinda lost in
this. Actually the items is not fast as a grocery store is moving. But
i think it's cool and i am looking forward to study it.

Anyway i would like the first thing.

"The total number acquired, less the number disposed of. Just use
DSum() on the table of acquisitions to get the number acquired for any
product. Another DSum() expression on the table of uses/invoices gets
the number disposed of."

Is there any sample of how this is being done?

I would like to just calculate the quantity from the Issue Vouchers
subtracted to the Beginning balance.

Thanks and have a great day.

On Jan 29, 11:45 am, TonyT <To...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi

A more normalised approach would be to have another table showing goods In,
and then make the calculation of current stock quantity on the basis of goods
In - goods Out = Current stock. For more info see this excellent example;

http://allenbrowne.com/AppInventory.html

TonyT..

"jppi...@xxxxxxxxx" wrote:
Good day!

Please bear with me with my current situation.

I need some help regarding my Inventory Monitoring Database i would
like to implement in our office. First, Here is what i am trying to
accomplish. We would like to create a database to monitor our
Inventory and make sure that all incoming and outgoing items are
monitored and documented if possible.

So what i have done so far is created a table and form for the main
stock items which i have the item id, description, u/m, etc. I also
created a table and form for the issue voucher where the information
such as user, department and site will be stored and in this issue
voucher form i have a subform which will handle the transactions for
the issue voucher like qty, description and how many qty for the item
issued to the user.

Now is it possible to total the qty of the Beginning balance of the
Main Stock Items minus by the qty issued in the subform of the Issue
Voucher?

i want to see the qty on hand in the main stock items form where the
qty on hand for that item will be safe and cannot be modified somehow,
it will just be like an added field where in the background it is
computing all the transaction for that item id.

I would like to start from here and if there are any confusion please
let me know and i will try my best to explain more clearly my problem.

I am thanking you all for giving a help site like this.

God bless!

John Paul A. Pigao- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


.

Relevant Pages

  • Re: Calculate profits on stock sales on FIFO basis
    ... Date Stock Action Qty Price Gain ... 01-01-09 IBM Buy 50 100 ... I wonder how you would handle splits and stock dividends that change basis. ... I need the gain made by me on that particular 'Stock' sale based on FIFO. ...
    (microsoft.public.excel.newusers)
  • RE: Computation in one form from the other forms
    ... A more normalised approach would be to have another table showing goods In, ... In - goods Out = Current stock. ... created a table and form for the issue voucher where the information ... the issue voucher like qty, description and how many qty for the item ...
    (microsoft.public.access.formscoding)
  • Re: Computation in one form from the other forms
    ... in the form of the Main Stock where i have a field of Begbal ... and a field for QtyonHand ... only getting one calculation for all of items in the table. ... created a table and form for the issue voucher where the information ...
    (microsoft.public.access.formscoding)
  • Re: Computation in one form from the other forms
    ... i will use to deduct quantity in the Main Stock form but the QtyonHand ... and a field for QtyonHand ... only getting one calculation for all of items in the table. ... created a table and form for the issue voucher where the information ...
    (microsoft.public.access.formscoding)
  • Re: Computation in one form from the other forms
    ... i will use to deduct quantity in the Main Stock form but the QtyonHand ... and a field for QtyonHand ... only getting one calculation for all of items in the table. ... created a table and form for the issue voucher where the information ...
    (microsoft.public.access.formscoding)
Loading