Correct Syntax for VBA

Hi Experts,
I am writing a sub to calculate a simple addition and multiplication between data entered by my users but for some reason it doesn't work, and I know why but I don't how to fix it.
my input fields is formated right after the user leave the box to put the number entered with a "$" sign in front and change the number with the proper decimal ,but then when I try to do my calculation I think the "&" sign gets pass in the formula and mess everything . so here is the form and the code.
the formula should take the "flat rate" + "labor amount"+....and =to Quoted Amount

my formula might do a little more than just adding , but if I understand how I can format my text field and do some calculation then I can figure out the rest.
thanks
David
Private Sub txtFlatRate_AfterUpdate()
    txtFlatRate = "$" & FormatNumber(txtFlatRate)
End Sub


Private Sub txtFlatRate_Change()
    txtQuoteAmount.Value = Val(txtFlatRate.Value)
    '+ Val(txtLabor) + Val(txtParts) + Val(txtMachine) + Val(txtExpedite)
    'This calculate the rush fee of 25% max $2000
    txtRushFee = ((Val(txtFlatRate) + Val(txtLabor) + Val(txtParts) + Val(txtMachine) + Val(txtExpedite)) * 25) / 100
    If txtRushFee > 2000 Then
        txtRushFee = 2000
    End If
    txtQuoteRush = "$" & FormatNumber(FormatNumber(txtQuoteAmount) + Val(txtRushFee))
    binChanged = True
End Sub

Open in new window

Capture123.JPG
tavernyAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
dont put any $ inside text box, use it on label

Dollar Value ($) : XXXXXXX

like this where XXXXXXX is the input box... did you try blur event? you can try to run your code on blur event of text box instead of after update
0
 
HooKooDooKuCommented:
At least one issue I see is line 14.
txtQuoteRush = "$" & FormatNumber(FormatNumber(txtQuoteAmount) + Val(txtRushFee))

It should be
txtQuoteRush = "$" & FormatNumber(Val(txtQuoteAmount) + Val(txtRushFee))

0
 
HainKurtSr. System AnalystCommented:
try:

txtQuoteAmount.Value = Val(txtFlatRate.Value)
txtRushFee = ((Val(txtFlatRate) + Val(txtLabor) + Val(txtParts) + Val(txtMachine) + Val(txtExpedite)) * 25) / 100
If txtRushFee > 2000 Then
        txtRushFee = 2000
End If
txtQuoteRush = "$" & FormatNumber(FormatNumber(txtQuoteAmount) + Val(txtRushFee))

-->  

txtQuoteAmount.Value = txtFlatRate.Value
txtRushFee.Value = ((Val(txtFlatRate.value) + Val(txtLabor.value) + Val(txtParts.value) + Val(txtMachine.value) + Val(txtExpedite.value)) * 25) / 100
If Val(txtRushFee.Value) > 2000 Then
        txtRushFee.Value = "2000"
End If
txtQuoteRush.Value = "$" & FormatNumber(FormatNumber(txtQuoteAmount.Value) + Val(txtRushFee.Value))
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tavernyAuthor Commented:
Sorry guys , I think I pasted a code that I modified so much that I commented my code.
I am posting right now the correct one.

so I am putting 100 in the flat rate and 100 on the labor amount and normally I should get 200 in the quoted amount , but I get 0.

her is the code:
txtQuoteAmount.Value = Val(txtFlatRate.Value) + Val(txtLabor) + Val(txtParts) + Val(txtMachine) + Val(txtExpedite)
   
0
 
HainKurtSr. System AnalystCommented:
you should use

txtQuoteAmount.Value = Val(txtFlatRate.Value) + Val(txtLabor.Value) + Val(txtParts.Value) + Val(txtMachine.Value) + Val(txtExpedite.Value)
0
 
tavernyAuthor Commented:
I tried that it doesn't work.
actually even this doesn't work:
txtQuoteAmount.Value = Val(txtFlatRate.Value)
this is what I am doing in case I am missing some peace of information:

I click flatrate box , type 100 and then hit tab, while typing 100 I see the correct value changing in my total quote. but as soon as I hit tab which call the afterupdate sub ( which reformat the data to put a "$" in front of my number in the flat rate) the total quote becomes 0

how do you display currency amount in the box to be formated correctly , am I doing it correctly to do :
"$" + "my value" ??
0
 
tavernyAuthor Commented:
so guys any ideas?
if I remove the following code then it works. :

Private Sub txtFlatRate_AfterUpdate()
    txtFlatRate = "$" & FormatNumber(txtFlatRate)
End Sub
0
 
HainKurtSr. System AnalystCommented:
it should be

txtFlatRate.Value = "$" & FormatNumber(txtFlatRate.Value)

but it creates a infinite loop if you change the text of a text box on after update event...
0
 
tavernyAuthor Commented:
So how do we fix that? I would like to have a field that look like a currency number.
0
 
tavernyAuthor Commented:
I thought about doing that as the last resort and I guess that's what I need to do .
I've never heard of blur event, and I don't think I have it available in VBA , I am using VBA for microsoft Dynamics.
so let's say I remove the $ and leave only the box for numerical input. what would be the best syntax:

1) txtQuoteAmount.Value = Val(txtFlatRate.Value) + Val(txtLabor.Value)
2) txtQuoteAmount = Val(txtFlatRate) + Val(txtLabor)
3) txtQuoteAmount = Val(txtFlatRate.Value) + Val(txtLabor.Value)
4) txtQuoteAmount = Val(txtFlatRate) + Val(txtLabor)
5) txtQuoteAmount.value = FormatNumber(txtFlatRate.Value) + FormatNumber(txtLabor.Value)
6) txtQuoteAmount = FormatNumber(txtFlatRate.Value) + FormatNumber(txtLabor.Value)
7) txtQuoteAmount.value = FormatNumber(txtFlatRate) + FormatNumber(txtLabor)
8) txtQuoteAmount = FormatNumber(txtFlatRate) + FormatNumber(txtLabor)

thanks
0
 
tavernyAuthor Commented:
Sorry for my late response, I actually remove the dollars amount from the box and left the data as numerical only.
Thanks for the support
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.