Hi Kevin,
i have an unbound texbox (totinputs)on a form that adds together
several over textbox
ie =text2 + text3+text4+text6
no problems so far
.....that you have discovered yet. For one thing, if any one of the text
boxes is null, the entire result will be null, since null (undefined) plus
any value is still undefined. The usual way to handle this is to use the Nz
function to convert nulls to zero, like this:
= Nz(text2, 0) + Nz(text3, 0) + Nz(text4, 0) + Nz(text6, 0)
But this also brings up another point. It certainly appears as if you have a
multi-valued field design, euphamistically known as an "Access spreadsheet".
An MVF design is not good. An example of an MVF design is to have one field
for each month in a year. The fields store 'similar' data, and are not
normalized. Such a design makes it much harder to query the database, create
accurate reports, group by a field in a report, etc.
Your question:
i want to pass the value of textbox(toinputs) to a bound textbox
(totals)
is certainly indicitive of a normalization problem. Generally, you do not
want to store a result that can be calculated. Here is what database design
expert Michael Hernandez, author of the book "Database Design for Mere
Mortals" has to say on this topic:
<Begin Quote>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>
I suggest that you start at this URL, and read up on database design. Make
sure to read the first two papers available, by Mike Hernandez:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
The quote I posted above comes from page 23 of his Understanding
Normalization paper.
Note: The newest version of Access (Access 2010), currently in beta release
only, is the first version to include trigger-like "data macros", which would
prevent the very issue that I quoted above. So, you *might* have a better
case for storing the results of a calculation in this version of Access.
However, consider what Access MVP John Vinson has to say:
"Storing calculated data generally accomplishes only three things: it wastes
disk space, it wastes time (a disk fetch is much slower than almost any
reasonable calculation), and it risks data validity, since once it's stored
in a table either the Total or one of the fields that goes into the total may
be changed, making the value WRONG."
So, while you can store the results of a calculation, before we show you how
to do so, I must ask if you have a valid reason for doing so. Please read the
DB design papers first, before answering.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
kevcar40 said:
hi
i have an unbound texbox (totinputs)on a form that adds together
several over textbox
ie =text2 + text3+text4+text6
no problems so far
i want to pass the value of textbox(toinputs) to a bound textbox
(totals)
i have tried using on change event and afterupdate
but neither work
the afterupdate event is below
Private Sub totinputs_AfterUpdate()
Me.[totals].Value = Me.[totinputs].value
End Sub
any ideas how to do this
thanks kevin
