Why is my vba microsoft access textbox not calculating?

VBA Textbox not calculating

Private Sub Textbox3()
Me.Textbox3 = (IIf(Me.Textbox1 = "", 0, Me.Textbox1) + 0) + (IIf(Me.Textbox2 = "", 0, Me.Textbox1) + 0)
End Sub
Joseph SAsked:
Who is Participating?
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.

Martin LissOlder than dirtCommented:
I would not name your sub Textbox3 since that is apparently also the name of a textbox.
Joseph SAuthor Commented:
What should my sub be?
Dale FyeOwner, Developing Solutions LLCCommented:
Actually, you don't really need any code at all.

In design view, select your textbox3 (I always rename all of my controls to something meaningful).
Display the properties dialog and on the 2nd tab, in the ControlSource box enter something like:

= IIf(Me.Textbox1 & "" = "", 0, Me.Textbox1) + IIf(Me.Textbox2 & "" = "", 0, Me.Textbox2)

Open in new window


This should check for either NULL or an empty string in textbox1 or textbox2.  I assume that you wanted to add the value from Textbox2 in the 2nd half of your equation, whereas your code above references textbox1 as the value to sum if textbox2 is empty.

Dale
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
Joseph SAuthor Commented:
I want to be able to do like in excel, where cell1=1,  cell2=2 , and cell3 =3. Then when you are in cell4=sum(cell1:cell3). With no buttons. I have everything written in vba.

I watched tutorials online, and I have it written same way. I don't understand why it worked from them, but doesn't work for me.
Mark EdwardsChief Technology OfficerCommented:
Hey Joe:  Sounds like a case of monkey see, monkey do.  That's a hard one to troubleshoot since it means even the slightest difference anywhere could cause an issue (i.e. your use of the textbox1 value instead of the textbox2 value in your second addition factor, and the "+0" in each factor - not needed.)

Dale's formula should work as long as all values in the textbox are either Null, an empty string, or a number.  If anything in textbox1 or 2 is anything else, it will throw an error.  i.e. a 2 plus a space " " will cause an error.

Also, Access is NOT a spreadsheet and doesn't act/behave like one.  SUM(Field1:Fieldn) is NOT something you can do in Access (although you probably already know that).

By the way, are your textboxes bound to an underlying table/query or unbound.  If they are unbound, then it will add strings (i.e "1" + "2" = "12").  You'll have to change the formula to include a force change of the value to a number.
Martin LissOlder than dirtCommented:
Please describe in words what you are attempting to do in your Me.Textbox3 = (IIf(Me.Textbox1 = "", 0, Me.Textbox1) + 0) + (IIf(Me.Textbox2 = "", 0, Me.Textbox1) + 0) line.
Joseph SAuthor Commented:
@Martin LIss,

I think this is a better explanation, say I put "1" in textbox1 and "2" in textbox2. Then textbox3 will display "3."
Martin LissOlder than dirtCommented: 
Sub MySub()
Me.Terxtbox3 = Me.Textbox1 + Me.textbox2
End Sub

Open in new window

Mark EdwardsChief Technology OfficerCommented:
when adding things in Access, it's not like Excel.  Watch out for null values!  1 + Null = Null.  In fact, Null + anything equals Null in Access.
Mark EdwardsChief Technology OfficerCommented:
You need for your formula to handle empty (null) textboxes.  Dale's formula does that.
Mark EdwardsChief Technology OfficerCommented:
I've tested your scenario in Access.  Here's what is happening:

If the textbox 1 and textbox2 are unbound, then the formula in textbox3 appends the two values together like text ("1" + "2" = "12")
if you adjust the formula to force a value to a number (i.e. Val([textbox1] & ""), then you get 1 if you enter "1" and you get 0 if the box is empty.  The result is 1+0=1 and 1+2=3.
Martin LissOlder than dirtCommented:
The code that I posted above will work if you Call MySub. The 'Call' isn't actually necessary)]. If you want something dynamic then delete that sub and add this code. The calculation will happen when you leave either Textbox1 or 2.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Me.TextBox3.Value = Val(Me.TextBox1) + Val(Me.TextBox2)

End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Me.TextBox3.Value = Val(Me.TextBox1) + Val(Me.TextBox2)

End Sub

Open in new window

Mark EdwardsChief Technology OfficerCommented:
To handle ALL cases, you can use the formula:
Textbox3 = Val([textbox1] & "") + Val([textbox2] & "")

Just put:
= Val([textbox1] & "") + Val([textbox2] & "")
in Textbox3's ControlSource property.  You can't do that, however, if you want Textbox3 to be bound to a field in the recordsource table/query.  You'll have to do the subroutine using:
Me.Textbox3 = Val(Me.textbox1 & "") + Val(Me.textbox2 & "")
Mark EdwardsChief Technology OfficerCommented:
You can fire off your subroutine by calling it in your Textbox1 and Textbox2 Afterupdate() events.
Mark EdwardsChief Technology OfficerCommented:
By the way, for you folks that don't know.... The Val() function throws an error/fails if you use it on a control and the control is empty (Null).  You can append an empty string to the control inside the Val() function i.e. Val(Me.Textbox1 & "") where Val("") = 0, or use the NZ() function to replace Null with a default value.  In the case of numbers:
NZ(Me.Textbox1,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
Joseph SAuthor Commented:
Thank you all, I just put it in the control source instead of VBA.
Martin LissOlder than dirtCommented:
Please don't forget to close this question by selecting one or more answers as the solution.
Joseph SAuthor Commented:
I am going to test out the responses, I am still working on my form.
Joseph SAuthor Commented:
Thank you everyone for your assistants :)
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
              Experts Exchange Top Expert VBA (current)
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
Microsoft Access

From novice to tech pro — start learning today.