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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.