Avatar of Joseph S
Joseph S
 asked on

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

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

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

ASKER
What should my sub be?
Dale Fye

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Joseph S

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

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 Liss

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Joseph S

ASKER
@Martin LIss,

I think this is a better explanation, say I put "1" in textbox1 and "2" in textbox2. Then textbox3 will display "3."
SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Joseph S

ASKER
Thank you all, I just put it in the control source instead of VBA.
SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Joseph S

ASKER
I am going to test out the responses, I am still working on my form.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Joseph S

ASKER
Thank you everyone for your assistants :)
Martin Liss

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)