Mini-Calculator on a form

[delaikhi]

Hi, I found this little thing on the internet, it work nicely when stands alone, problem is when I put it on my form, next to a field (Order_Amount), it does not copy value from calculator to the field. Can some one tell me what should I do?

[ranman256]

It works perfectly. I dont see a problem. I clickd the button beside the text box, the calc opened, i calcd, it pasted.
perfect.

[delaikhi]

No, if you paste it on another form, it will not work as expected. That's why I am wondering

[ssanfu]

Did you edit the click event of the button to change the name of the text box?? It worked for me.

I created a new form (Form1).
I added a new text box (Text0).
I copied the button from frmShowCalc and pasted it on Form1
I edited the click event of the button - changed it from =SelectCalculation([ShowCalcs]) to =SelectCalculation([Text0])
Saved Form1

Works great!

[delaikhi]

Steve, do you edit anything on the module? Thanks

[ssanfu]

do you edit anything on the module

Nope. That is why the function has a parameter.
Open Form1. In design view , look at the events tab for the button.

[delaikhi]

Steve, I think maybe the problem is because I have designed the field a little complicated: by format, it's currency; by value it is a result of calculation, something like: ProductPrice*OrderQuantity; I need the calculator only in some cases. Do you think this is the problem?

[ssanfu]

First - terminology. Tables have fields. Forms have controls.

I have designed the field a little complicated: by format, it's currency

The format property has no effect on pasting in a value.

by value it is a result of calculation

If a form has a control named "Order_Amount" (a text box), and the Control source property is set to "= ProductPrice*OrderQuantity", then the calculator will not work on this control.

I need the calculator only in some cases.

More info???

[delaikhi]

If a form has a control named "Order_Amount" (a text box), and the Control source property is set to "= ProductPrice*OrderQuantity", then the calculator will not work on this control.

Yeah, in fact, I put this code on the afterupdate property of another text box, it looks like this:
Private Sub Order_Quantity_AfterUpdate()
Me.Order_Amount = Round(Me.Order_Quantity * Me.Product_Price)
End Sub
Then, call it again when Order_Amount gets focus:
Private Sub Order_Amount_GotFocus()
Call Order_Quantity_AfterUpdate
End Sub
Maybe this is why the calculator doesn't work?

[ssanfu]

Private Sub Order_Amount_GotFocus()
Call Order_Quantity_AfterUpdate
End Sub

Every time the control gets the focus, the order amount is recalculated. Doesn't matter what you enter or how you enter data (manually or with calculator), the value you enter is overwritten.

IMO, the "Order_Amount" should only be calculated when you enter a quantity. Should not be using the "Got Focus" event (I would delete this procedure).

Are you saving the "Order_Amount" in a field of a table? Normally, calculated values are not saved. Having said that, I have had to save calculated values because of speed issues (calculations of amounts over thousands of records), or audit tracking.

[delaikhi]

Actually, as you said I have to save the calculated values for auditing purposes. Normally, it would be OK if I do "Got Focus" event. But when it comes to calculations, like special rates, I have to do calculation by hand, this is where the calculator coming handy. Not sure what to do if "got Focus" deleted? Where should I store the procedures for calculation results? At OrderQuantity's "After Update"? Thanks

[ssanfu]

Not sure what to do if "got Focus" deleted?

Don't have to do anything!

This is a bound form, right? and the control is bound to a field, right?
Then Access automatically saves the record (or changes) if the form is "dirty" (has unsaved changes) and :
- you move to a different record
- you close the form
- you re-query the form

If you change the quantity, the after update event fires for the "Order_Quantity" control which calculates the order amount and puts the result in the "Order_Amount" control. IMO, having the procedure "Order_Amount_GotFocus()" is worthless.

You didn't state how the price gets entered in the "Product_Price" control. I would have a "Product_Price_AfterUpdate" event that calculates the "Order_Amount", just like the "Order_Quantity" control. Price and quantity are the only two things that can affect the order amount.

I do add the NZ() function to handle NULLs:
Me.Order_Amount = Round(NZ(Me.Order_Quantity,0) * NZ(Me.Product_Price,0),2)

I added the number of decimals to the round function. If you don't want cents, change the 2 to a zero.

[delaikhi]

This is a bound form, right? and the control is bound to a field, right?

Yeah

You didn't state how the price gets entered in the "Product_Price" control.I would have a "Product_Price_AfterUpdate" event that calculates the "Order_Amount", just like the "Order_Quantity" control. Price and quantity are the only two things that can affect the order amount.

The Product_Price comes from a separate table tblProductDetails, so that's kinda fixed. But the Order_Amount comes from 3 types of things: "Regular" and "Expedite), in the first case, it would be calculated like: [Order_Quantity]*[Product_Price]; in the latter case: [Order_Quantity]*[Product_Price]+20

[ssanfu]

"Regular" and "Expedite" are only 2.

I'm guessing this is shipping? Is "Regular" / "Expedite" stored with the order?

You could use:

Me.Order_Amount = Round(NZ(Me.Order_Quantity,0) * NZ(Me.Product_Price,0),2) + IIF([fieldname] = "Regular",0,20)


Can you post the dB? Just the tables, queries and forms involved with a couple of test records.