We help IT Professionals succeed at work.

adding unbound and bound textboxes- cont from last question

davetough
davetough asked
on
Medium Priority
283 Views
Last Modified: 2012-08-13
Hello,
I have attached db- continuation on my last question.
when you open db- a form comes up.
There are two 'Parts Categories' boxes on form. 1 is unbound and 2 is bound.
Having both boxes perform same function to learn.
I want to add #-U [txtU] + #-V[txtV] + #-S[txtS]- and display results in Parts Categories boxes- but is not working.
The unbound textbox adds some of records correctly and other records not adding - (does not work when have NO value in one of records)-
and there will be times- where some levels are null.

The bound box- I have tried coding: me.txtPartsCategories= [me.txtU]+[me.txtV]+[me.txtS]
and not working at all.
thank you
base1.accdb
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Use this instead:

me.txtPartsCategories= NZ([me.txtU],0) + NZ([me.txtV],0) + NZ([me.txtS],0)
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Sorry - Drop the brackets:

Me.txtPartsCategories = Nz(Me.txtU, 0) + Nz(Me.txtV, 0) + Nz(Me.txtS, 0)

You can also do this in the control source propery of txtPartsCategories like this:

 = Nz(txtU, 0) + Nz(txtV, 0) + Nz(txtS, 0)
CERTIFIED EXPERT

Commented:
I have updated your database.  The null values are causing problems.
With the bound value, often designers will use a query to do the calculation.  That way, any field changes force an automatic recalculation and you don't need the VB code.
In 2010, the calculated field offers the same advantage.  I added the Calculated Field in the table, but not the form.
I set the default value to 0 for the field with nulls and I changed the update code to use 0 when no range was selected.  You can hide the 0 with a format mask which I place in the second field for you to review. base1.accdb

Author

Commented:
thank you-mbizup am i correct that
Me.txtPartsCategories = Nz(Me.txtU, 0) + Nz(Me.txtV, 0) + Nz(Me.txtS, 0)-should work in bound texbox-
am getting #name? error- maybe i am doing something wrong-
DoDah- am having hard time opening your db- is it 2010- i have 2007
thank you
am going to look this over tonight and understand it
i think i can see some code in 2010forms code
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
That code goes in the open event not the control source.


My second suggestion will work in the control source.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Here's the sample I tested that on to make it a bit clearer.

Your bound textbox is filled through this code in the Form's Current Event:
Me.txtPartsCategories = Nz(Me.txtU, 0) + Nz(Me.txtV, 0) + Nz(Me.txtS, 0)

Your unbound textbox is filled by a control source set to:
 = Nz(txtU, 0) + Nz(txtV, 0) + Nz(txtS, 0)

(You can't use the Me. Prefix in a control source - it only works in VBA)

The advantage of the second method is that the total updates automatically anytime one of the values in the sum changes.

As an aside, you should avoid using spaces and special characters (such as # or -) in your field names.  That can cause problems.  


base1.accdb

Author

Commented:
Great Help- thanks again- my keyboard went dead -and was unable to write
yes i wonder if those special charachters

Explore More ContentExplore courses, solutions, and other research materials related to this topic.