We help IT Professionals succeed at work.

Why is my vba microsoft access textbox not calculating?

Joseph S
Joseph S asked
on
75 Views
Last Modified: 2018-12-02
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
Comment
Watch Question

Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I would not name your sub Textbox3 since that is apparently also the name of a textbox.

Author

Commented:
What should my sub be?
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

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

Author

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 Officer
CERTIFIED EXPERT

Commented:
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 LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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 LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Chief Technology Officer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thank you all, I just put it in the control source instead of VBA.
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
I am going to test out the responses, I am still working on my form.

Author

Commented:
Thank you everyone for your assistants :)
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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)