Storing Calculated Value (special senerio)

[robsworld78]

Hi, I know you shouldn't store calculated values but this is a special case I believe.



I'm playing with inventory, we have cases, boxes and pieces. A case could have 4 boxes and each box has 12 pieces.

When I transfer inventory I use cases, so if I need to transfer only half a case I go .5.

Now I've come across a senerio where I may need to take inventory out so I will just enter a negative number -.5 for half a case. Works fine.

Here's the problem. I may only want to take out 6 pieces which is half a box, so I can't enter that in cases very easy, it would be like .0082 or something like that. So what I want to do is have another text box where I enter the number of pieces I want to remove and with math in the cases box it will say that's .0082 cases or whatever but it needs to store that value because that's the field I use for everything.

So how do I do that calculation and store it in the "casestransfered" field?

Right now I have it so I enter the pieces it does the math in an unbound textbox and then I enter what's in the calculated text box in the bound "casestransfered" field but I want it to store automatically from the unbound text box that's doing the math.

[June7]

In code, I use VBA not macros, something like: Me!casestransferred = Me.calcbox
or Me!casestransferred = do the math here

The trick is to figure out what event to put the code in. Use the AfterUpdate event of the Pieces control and do the calcs in the code, like the second example above or the Close event of the form and use the first example.

[robsworld78]

Thanks, that was too easy, why can't everything be this easy.

[robsworld78]

On other little thing I can't find help on.

I have a field that calculates totals on a subform, in order to have that update I have me.refresh in the afterupdate on the field I add the data works great.

But now I want to refresh another subform when the value changes in the calculated field on the other subform. I've tried all the events and it won't work, if I use the onclick event it works when i click the field but I don't want it to do that, I would have thought the afterupdate would have worked but it doesn't, it appears it doesn't know that number is being changed because I'm not chaning it the formula is.

So then I go back to the afterupdate on the field I'm entering my data in which already has me.refresh

If I add another line under it telling it to refresh the other subform it needs me to enter data twice in the field.

Example.

add data in field tab over and it refreshes that subform but not other, I go back to that field change it and it refreshes its subform again and refreshes the other subform with the previous entry if that makes sense, it won't run both refreshes at the same time. How do I make it refresh both or even maybe 3 subforms at once, of course without refreshing the main form or anyother subforms I don't want refreshed.

I want to specificially say refresh this subfrom this subform and yourself, nothing else.

Here's the code I'm using

Private Sub Pennies_AfterUpdate()
Me.Refresh
Me.Parent.PayOrderInvoiceAmountOwingSubform.Form.R efresh
End Sub

I've tried And between them but it doesn't work, how do I string these 2 lines together and add a 3rd if I want.

[robsworld78]

Oh one other related thing. On the field that is doing calculating I want that number to be stored. It's the payment amount I'm capturing.

Someone enters the payment as 2 - $20, 1 - $10 and the total says $30 paid then I want that number to go into the "PaymentAmount" field which I can do now thanks to you! However I can't get that number over automatically using the events. I had to go with onclick and after the field calculates the value I click it and it moves to the "PaymentAmount" but I want it automatically, just like the calculated field works.

Any ideas?

[robsworld78]

I have a hard time pulling from different subforms, it always seems to be done a different way and I haven't figured out why yet. Anyways this trick works great if everything is on the same subform but for the life of me I can't get it to call info from another subform, please help.

This is the code I use

Me.BarsSold = Me.BarsReturned

Works great, when I put numbers in BarsReturned it moves them to BarsSold. But I want it to be like this.

Me.BarsSold = VendorInventoryLevelSubform.BarsGiven - Me.BarsReturned.

I've tried so many senerios for BarsGiven but it won't call that number. I've gone me.parent.VendorInventoryLevelSubform.barsgiven I've tried in many different brackets sometimes with me.parent. etc...