Showing a product in a formatted textbox(contents) 3

[pingpang]

Good day to you!

I have this problem with calculation. I have Quantity(txtQty) and Unit Price(txtUprice) field and output the product in the Amount(txtAmount) field. If works just fine if I remove the code to format the contents of the textbox. But my code below doesn't work, it gives a wrong product in the txtAmount.text

Kindly check my code...

Private Sub txtQty_Change()
txtQty.Text = Format(txtQty.Text, "#,###&quot
txtQty.SelStart = Len(txtQty.Text)

txtAmount.Text = Val(txtQty.Text) * Val(txtUprice.Text)
txtAmount.Text = Format(txtAmount.Text, "#,###.00&quot
End Sub

Private Sub txtUprice_Change()
txtUprice.Text = Format(txtUprice.Text, "#,###.00&quot
txtUprice.SelStart = Len(txtUprice.Text)

txtAmount.Text = Val(txtQty.Text) * Val(txtUprice.Text)
txtAmount.Text = Format(txtAmount.Text, "#,###.00&quot
End Sub

Any help will be greatly appreciated.

thanks in advance

 

[ADoozer]

rather than formatting in the change event...

set txtQty Dataformat property (you get a popup like in excel)
and txtUPrice Dataformat property in the ide

then just use

Private Sub txtQty_Change()

txtAmount.Text = Val(txtQty.Text) * Val(txtUPrice.Text)
txtAmount.Text = Format(txtAmount.Text, "#,###.00&quot

End Sub

Private Sub txtUPrice_Change()

txtAmount.Text = Val(txtQty.Text) * Val(txtUPrice.Text)
txtAmount.Text = Format(txtAmount.Text, "#,###.00&quot

End Sub

hope this helps If somethings hard to do, its not worth doing - Homer Simpson

 

[zemp]

Another option would be to follow ADoozer's suggestion in formatting the first two text boxes in design mode and then use the validate events to for updating the amounts. The validate event fires when you exit the control so the calculation is not done for every character you enter.

Private Sub txtQty_Validate(Cancel As Boolean)
txtAmount.Text = Format(Val(txtQty.Text) * Val(txtUprice.Text), "#,##0.00&quot
End Sub

Private Sub txtUprice_Validate(Cancel As Boolean)
txtAmount.Text = Format(Val(txtQty.Text) * Val(txtUprice.Text), "#,##0.00&quot
End Sub


Thanks and Good Luck!

zemp

 

[pingpang]

thanks!

But my form is not bound. In my form I have ok and cancel button. In the ok button codes for saving what the user inputs in every textbox.

And I believe that you can't use the dataformat property when your control is not bound.

Hope for your response soon.... Thanks again!

 

[zemp]

If you are not using the dataformat property then you just need to take care of the formatting yourself. Try the code below in your validate events.

Private Sub txtQty_Validate(Cancel As Boolean)
txtQty.Text = Format(txtQty.Text, "#,###&quot
txtQty.SelStart = Len(txtQty.Text)

txtAmount.Text = Format(Val(txtQty.Text) * Val(txtUprice.Text), "#,##0.00&quot
End Sub

Private Sub txtUprice_Validate(Cancel As Boolean)
txtUprice.Text = Format(txtUprice.Text, "#,###.00&quot
txtUprice.SelStart = Len(txtUprice.Text)

txtAmount.Text = Format(Val(txtQty.Text) * Val(txtUprice.Text), "#,##0.00&quot
End Sub

Thanks and Good Luck!

zemp

 

[ADoozer]

be careful when formating before doing your calculation, ive found that if you put a quantity of 1000 in txtqty the amount calculated is only for 1 unit(despite the val function showing 1000). im not sure why this is or how to get round it, but thought id bring it up!

good luck If somethings hard to do, its not worth doing - Homer Simpson

 

[ADoozer]

you could give this a try, it seems to work!!

Dim qty As Integer
Dim uprice As Double


Private Sub Form_Load()
qty = 1
uprice = 0
End Sub

Private Sub txtQty_Validate(Cancel As Boolean)

qty = Val(txtQty)

txtQty.Text = Format(txtQty.Text, "#,###&quot
txtQty.SelStart = Len(txtQty.Text)

txtAmount.Text = Format(qty * uprice, "#,##0.00&quot

End Sub

Private Sub txtUprice_Validate(Cancel As Boolean)

uprice = Val(txtUPrice)

txtUPrice.Text = Format(txtUPrice.Text, "#,###.00&quot
txtUPrice.SelStart = Len(txtUPrice.Text)

txtAmount.Text = Format(qty * uprice, "#,##0.00&quot

End Sub

hope this helps! If somethings hard to do, its not worth doing - Homer Simpson

 

[zemp]

Good catch ADoozer, also add the zero I forgot in the following line,

txtUPrice.Text = Format(txtUPrice.Text, "#,###0.00&quot Thanks and Good Luck!

zemp

 

[ADoozer]

zemp: the problem persists, ive tried

"#,### and #,###.00"
"#,##0 and #,##0.00" and
"#,###0 and #,###0.00"

and a few other combos, they all do the same.

it would seem that formating 1000 to 1,000 is doing something that affects the val function?!?! (assumption dont quote me!)

assigning the text to a variable before hand seems to work though!??!?
If somethings hard to do, its not worth doing - Homer Simpson

 

[zemp]

I agree that's why I said it was a 'good catch'. You noticed and corrected that problem. I had just forgot to adjust the format for display in one line.

Sorry for the confustion. Thanks and Good Luck!

zemp

 

[ADoozer]

oops my bad!!

so to clarify...

Dim qty As Integer
Dim uprice As Double


Private Sub Form_Load()
    qty = 1
    uprice = 0
End Sub

Private Sub txtQty_Validate(Cancel As Boolean)
    
    qty = Val(txtQty)
    
    txtQty.Text = Format(txtQty.Text, "#,###")
    txtQty.SelStart = Len(txtQty.Text)

    txtAmount.Text = Format(qty * uprice, "#,##0.00")
    
End Sub

Private Sub txtUprice_Validate(Cancel As Boolean)
    
    uprice = Val(txtUPrice)
    
    txtUPrice.Text = Format(txtUPrice.Text, "#,##0.00")
    txtUPrice.SelStart = Len(txtUPrice.Text)
    
    txtAmount.Text = Format(qty * uprice, "#,##0.00")
    
End Sub

works!! LOL If somethings hard to do, its not worth doing - Homer Simpson

 

[zemp]

Yes, a star for your work. Thanks and Good Luck!

zemp

 

[pingpang]

Thanks a lot! I also notice (what ADoozer noticed) last night that 1,000 will be treated as 1.

Again, Thanks to you guys! I'll try the code tonight.

By the way, why do you use the validate event? why not the change event? And why do we need to assign 1 to qty in the form_load event?

 

[ADoozer]

pingpang: the validate event is fired just b4 the control loses focus wheras the change event is fired everytime the contents change, its perfectly ok to use the change event but i prefer the validate event (you could argue it saves processing time too), as for assigning a qty of 1, its not necessary , you could assign 0 to it but in the real world, nobody buys 0 products

hope this clears up a few things! If somethings hard to do, its not worth doing - Homer Simpson

 

[pingpang]

ok thanks for all your help!

 

[pingpang]

hello!

I've tried your code already. It works but when you press the tab key until it reaches back to the txtqty(textbox) the amount will change. Did you get me? I don't know why, I hope you could check it.

Thanks!

 

[ADoozer]

do you mean that txtAmount is displaying 0.00 when there is no input in txtqty or txtuprice, if so try this:-

replace
txtAmount.Text = Format(qty * uprice, "#,##0.00&quot
with
If Not (txtqty = "" Or txtUPrice = "&quot Then
txtAmount.Text = Format(qty * uprice, "#,##0.00&quot
End If

in both validate events
If somethings hard to do, its not worth doing - Homer Simpson

 

[ADoozer]

ok.... now i understand!! took a few guys to beat my head against the wall b4 i realised but, try this code instead

Dim qty As Integer
Dim uprice As Double

Private Sub Form_Load()
    qty = 0
    uprice = 0
End Sub

Private Sub txtQty_Validate(Cancel As Boolean)
    
    qty = CInt(txtqty)
    
    txtqty.Text = Format(txtqty.Text, "#,###")
    txtqty.SelStart = Len(txtqty.Text)

    txtamount.Text = Format(qty * uprice, "#,##0.00")
    
End Sub

Private Sub txtUprice_Validate(Cancel As Boolean)
    
    uprice = CDbl(txtuprice)
    
    txtuprice.Text = Format(txtuprice.Text, "#,##0.00")
    txtuprice.SelStart = Len(txtuprice.Text)
    
    txtamount.Text = Format(qty * uprice, "#,##0.00")
    
End Sub

good luck If somethings hard to do, its not worth doing - Homer Simpson

 

[pingpang]

Hi there!

I've tried the code already. It works... thanks!

I just want to ask you if you don't mind what's the difference when you use val or cint/clng/cdbl?

thanks again!

 

[ADoozer]

check out MSDN for "val function" and "data type keyword summary" for a full and proper explanation.

good luck

If somethings hard to do, its not worth doing - Homer Simpson