(Word) Problems with subtotal/total in user form

[SMDeasy]

Hi! I am a newb here and I have tried to search for a solution but cant seem to find one. I created a user form to submit values to a word doc-creating a printable invoice. I have played around with so much code to figure where I've gone wrong so I don't even know where I've started anymore...lol. Here it is - I have a column for Quantity, a column for price and a column for Amount. After entering price and quant., when you press subtotal, amounts are calculated and added to give the subtotal. When pressing total, sales tax is calculated and a total is given. You can fill in a deposit and the deposit will be subtracted from the total. It works fine if the amounts are in the hundreds but if the amounts are in the thousands, the tax and subtotal do not add together properly. Here is my code for the subtotal and total command buttons:

Private Sub cmdSubtotal_Click()
Dim q1, p1, q2, p2, q3, p3, q4, p4, q5, p5, q6, p6, q7, p7, q8, p8, q9, p9, q10, p10, q11, p11, q12, p12, q13, p13, q14, p14, default

default = "0"
q1 = Val(txtQuantity1.Text)
q2 = Val(txtQuantity2.Text)
q3 = Val(txtQuantity3.Text)
q4 = Val(txtQuantity4.Text)
q5 = Val(txtQuantity5.Text)
q6 = Val(txtQuantity6.Text)
q7 = Val(txtQuantity7.Text)
q8 = Val(txtQuantity8.Text)
q9 = Val(txtQuantity9.Text)
q10 = Val(txtQuantity10.Text)
q11 = Val(txtQuantity11.Text)
q12 = Val(txtQuantity12.Text)
q13 = Val(txtQuantity13.Text)
q14 = Val(txtQuantity14.Text)
p1 = Val(txtPrice1.Text)
p2 = Val(txtPrice2.Text)
p3 = Val(txtPrice3.Text)
p4 = Val(txtPrice4.Text)
p5 = Val(txtPrice5.Text)
p6 = Val(txtPrice6.Text)
p7 = Val(txtPrice7.Text)
p8 = Val(txtPrice8.Text)
p9 = Val(txtPrice9.Text)
p10 = Val(txtPrice10.Text)
p11 = Val(txtPrice11.Text)
p12 = Val(txtPrice12.Text)
p13 = Val(txtPrice13.Text)
p14 = Val(txtPrice14.Text)


If txtQuantity1.Text = True Then
lblAmount1.Caption = (q1 * p1)
Else
lblAmount1.Caption = ""
End If

If txtQuantity2.Text = True Then
lblAmount2.Caption = (q2 * p2)
Else
lblAmount2.Caption = ""
End If

If txtQuantity3.Text = True Then
lblAmount3.Caption = (q3 * p3)
Else
lblAmount3.Caption = ""
End If

If txtQuantity4.Text = True Then
lblAmount4.Caption = (q4 * p4)
Else
lblAmount4.Caption = ""
End If

If txtQuantity5.Text = True Then
lblAmount5.Caption = (q5 * p5)
Else
lblAmount5.Caption = ""
End If

If txtQuantity6.Text = True Then
lblAmount6.Caption = (q6 * p6)
Else
lblAmount6.Caption = ""
End If

If txtQuantity7.Text = True Then
lblAmount7.Caption = (q7 * p7)
Else
lblAmount7.Caption = ""
End If

If txtQuantity8.Text = True Then
lblAmount8.Caption = (q8 * p8)
Else
lblAmount8.Caption = ""
End If

If txtQuantity9.Text = True Then
lblAmount9.Caption = (q9 * p9)
Else
lblAmount9.Caption = ""
End If

If txtQuantity10.Text = True Then
lblAmount10.Caption = (q10 * p10)
Else
lblAmount10.Caption = ""
End If

If txtQuantity11.Text = True Then
lblAmount11.Caption = (q11 * p11)
Else
lblAmount11.Caption = ""
End If

If txtQuantity12.Text = True Then
lblAmount12.Caption = (q12 * p12)
Else
lblAmount12.Caption = ""
End If

If txtQuantity13.Text = True Then
lblAmount13.Caption = (q13 * p13)
Else
lblAmount13.Caption = ""
End If

If txtQuantity14.Text = True Then
lblAmount14.Caption = (q14 * p14)
Else
lblAmount14.Caption = ""
End If

lblAmount1.Caption = Format(lblAmount1.Caption, "#,###,###.00")
lblAmount2.Caption = Format(lblAmount2.Caption, "#,###,###.00")
lblAmount3.Caption = Format(lblAmount3.Caption, "#,###,###.00")
lblAmount4.Caption = Format(lblAmount4.Caption, "#,###,###.00")
lblAmount5.Caption = Format(lblAmount5.Caption, "#,###,###.00")
lblAmount6.Caption = Format(lblAmount6.Caption, "#,###,###.00")
lblAmount7.Caption = Format(lblAmount7.Caption, "#,###,###.00")
lblAmount8.Caption = Format(lblAmount8.Caption, "#,###,###.00")
lblAmount9.Caption = Format(lblAmount9.Caption, "#,###,###.00")
lblAmount10.Caption = Format(lblAmount10.Caption, "#,###,###.00")
lblAmount11.Caption = Format(lblAmount11.Caption, "#,###,###.00")
lblAmount12.Caption = Format(lblAmount12.Caption, "#,###,###.00")
lblAmount13.Caption = Format(lblAmount13.Caption, "#,###,###.00")
lblAmount14.Caption = Format(lblAmount14.Caption, "#,###,###.00")


lblSubtotal.Caption = (q1 * p1) + (q2 * p2) + (q3 * p3) + (q4 * p4) + (q5 * p5) + (q6 * p6) + (q7 * p7) + (q8 * p8) + (q9 * p9) + (q10 * p10) + (q11 * p11) + (q12 * p12) + (q13 * p13) + (q14 * p14)
lblSubtotal.Caption = Format(lblSubtotal.Caption, "#,###,###.00")



End Sub

Private Sub cmdTotal_Click()

Dim t1, d1

t1 = Val(txtTax.Text)
d1 = Val(txtDepositAmount.Text)


If lblSubtotal.Caption = True Then
txtTax.Text = (lblSubtotal.Caption * 0.06)
End If

If txtTax.Text = True Then
lblTotal.Text = t1 + (lblSubtotal.Caption - d1)
End If
txtDepositAmount.Text = Format(txtDepositAmount.Text, "#,###,###.00")
txtTax.Text = Format(txtTax.Text, "#,###,###.00")
lblTotal.Text = Format(lblTotal.Text, "#,###,###.00")
End Sub

I really appreciate any and all help!!!
Sheila

 

[PHV]

The problem is here:
lblSubtotal.Caption = Format(lblSubtotal.Caption, "#,###,###.00")
You don't have a numeric value but a text string.

A workaround:
txtTax.Text = Val(Replace(lblSubtotal.Caption, ",", "")) * 0.06

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[macropod]

Hi Sheila,

You don't need to have all this:

If txtQuantity1.Text = True Then
lblAmount1.Caption = (q1 * p1)
Else
lblAmount1.Caption = ""
End If
...
lblAmount14.Caption = Format(lblAmount14.Caption, "#,###,###.00")

code. It could all be replaced with :

lblAmount1.Caption = Trim(Format(q1 * p1, "#,0.00;-#,0.00; "))
...
lblAmount14.Caption = Trim(Format(q14 * p14, "#,0.00;-#,0.00; "))

or perhaps even:

Dim i as Integer
For i = 1 to 14
     "lblAmount" & i.Caption = Trim(Format("q" & i * "p" & i, "#,0.00;-#,0.00; "))
Next

Cheers

[MS MVP - Word]

 

[SMDeasy]

Aaaaaah-thank you both so much!!!! Works like a charm!
*Phew*


Sheila