Computation in one form from the other forms

[jppigao]

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

 

[TonyT]

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..

 

[jppigao]

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.

 

[TonyT]

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..

 

[jppigao]

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

 

[TonyT]

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]

Hi Tony,

Well it seems like it is not working for some reason, well what i have
done so far is..

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

I copied this to the AfterUpdate Control under properties of the
ItemID i tried to play around with the quantity of the Begbal, Also
tried to add, remove some transaction in the Issue Voucher form where
i will use to deduct quantity in the Main Stock form but the QtyonHand
is just a blank field i think the calculation is not doing it's
stuff.


FYI In the form i have the ItemID which is in numbers not in text so
this maybe it? or something wrong with my code

Hoping for your response again!

Thanks Tony!

 

[TonyT]

Put a code break against that code (Me.QtyOnHand....) by clicking on the grey
part of the screen just to the left of the code, and then try changing the
value in Me.ItemID and see what happen when then code break stops the code
(hover over text to see values). So long as ItemID in the table is a number
not text then it should work fine as is. If it's text then change to;

Me.QtyonHand = Me.Begbal - DSum("QtyIssued",
"tbl_Transaction_Details", "ItemID = '" & Me.ItemID & "'")

single quotes added around " & Me.ItemID & "

good luck again

TonyT..

 

[jppigao]

Hi Tony,

Well still it didn't work out. Now when i put the code...

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

in the On CLick control under properties of the QtyonHand field of the
Main Stock form it is working perfectly but the disadvantage is that
you need to click the QtyonHand field of every ItemID just for the
QtyonHand to update and/or display. I don't why it is not working in
the AfterUpdate Control of the ItemID field. Any idea?

Well it seems that the code is correct because it works in the On
Click control but not in some other controls. It would be nice and
good if the QtyonHand is updating everytime you select an ItemID.

Hope you can provide solution for me.

Thanks!

God bless!


John Paul Angala Pigao