Form Data Not Transferring Into Table???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a form for order entry and a correlating table.... I used an
expression to calculate values (ie- multiply the qty. by the unit cost to
give line total). It was working and transferring the calculated amounts
into the table, but now a few weeks later, it is not transferring any of the
calculated information into the table. The calculations do occur and show
in the order entry form, however, they are not going into the table.

We do have several users in this database ... did someone alter something
and now I am forgetting where to look to change it back(I am a beginner at
setting up databases)? Additionally, is there a way to lock the database
from design changes all together without a password?

Thanks for your help!
Shalen
 
My guess is that the calculated value was never stored nor should it be.
Calculate the value as you need it.
 
In general, you really shouldn't be storing the calculated value. The
rationale for this is that if one of the independant fields changes then you
have to provide some means for ensuring that the derived value is updated.
Instead you calculate it for display purposes only. If for some reason you
do need to store the value then instead of an expression in the
ControlSource of the control you would need to use VBA to assign the value
of the expression to a bound control (where the controlsource is mapped to a
field in the table).

Regarding multiple users, are they all using the same database (from the
same location?) In a multiuser situation you really need to split the
database so that you have separate front-end and back-end databases. The
front-end database has the forms, queries, reports, code and links to the
back-end tables. The back-end has only the tables. Then give each user their
own copy of the front-end database.

To secure the objects you can either distribute MDEs to your users and/or
implement user level security. Here is a link to Lynn Trapps website which
is a good starting point for User Level Security:
http://www.ltcomputerdesigns.com/Security.htm
 
Thanks, Pat, for your reply... this is a bound control function that
automatically calculates, much like a formula in Excel. It is set into the
form and should transfer the amount into the table. My thought process is
that the form is the overlay for the table (just an easier way to look at the
table), it is not different information. That's why I am so puzzled... why
did the data once populate and transfer from the form to the table and has
stopped?
 
You can prevent users from making any design changes by converting your mdb
file to an mde. See Tools, Database Utilities, Make Mde. Do a little reading
on the mde file format before you do. Basically, it is like an mdb, but with
the ability to create or modify objects.

As to the calculated fields not working, somehow something has changed to
cause this. Open your form, and check to see that the calculated fields
control sources are still in place. Also look at where the calculations are
being done to ensure they are working correctly. If the correct calculation
is show in the text box, then it is okay.

Now, what you really need to do is change your approach. If you do any
study at all or read posts on this site, you will find that storing
calculated values in a table violated database normalization rules. There is
absolutely no justification for storing calculated values. The correct
approach is to only store the values used in the calculation and do the
calculation wherevever a human needs to see the result of the calculation.
 
Thanks for your reply... you are right on ... that is what I did... I set up
a bound control source within the form in the "line total" field (box). The
line total field was working on the form and transferring the data into the
table, but now has stopped and I am not sure why?

I have deleted the old fields in the form, created new bound controls, and
then did a test record, but it is not transferring still... that is why I
thought maybe I needed to do something in the table as well?
--
Thanks for your help!
Shalen


Sandra Daigle said:
In general, you really shouldn't be storing the calculated value. The
rationale for this is that if one of the independant fields changes then you
have to provide some means for ensuring that the derived value is updated.
Instead you calculate it for display purposes only. If for some reason you
do need to store the value then instead of an expression in the
ControlSource of the control you would need to use VBA to assign the value
of the expression to a bound control (where the controlsource is mapped to a
field in the table).

Regarding multiple users, are they all using the same database (from the
same location?) In a multiuser situation you really need to split the
database so that you have separate front-end and back-end databases. The
front-end database has the forms, queries, reports, code and links to the
back-end tables. The back-end has only the tables. Then give each user their
own copy of the front-end database.

To secure the objects you can either distribute MDEs to your users and/or
implement user level security. Here is a link to Lynn Trapps website which
is a good starting point for User Level Security:
http://www.ltcomputerdesigns.com/Security.htm

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi,

I have a form for order entry and a correlating table.... I used an
expression to calculate values (ie- multiply the qty. by the unit
cost to give line total). It was working and transferring the
calculated amounts into the table, but now a few weeks later, it is
not transferring any of the calculated information into the table.
The calculations do occur and show in the order entry form, however,
they are not going into the table.

We do have several users in this database ... did someone alter
something and now I am forgetting where to look to change it back(I
am a beginner at setting up databases)? Additionally, is there a
way to lock the database from design changes all together without a
password?

Thanks for your help!
Shalen
Click to expand...
Click to expand...
 
Make sure that you have AfterUpdate events for each of the independant
fields that contribute to the derived value. If you renamed any of the
controls the event procedure may have become detached from the event
property - double check the event property to make sure it has the "[Event
Procedure]" text in the property.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks for your reply... you are right on ... that is what I did... I
set up a bound control source within the form in the "line total"
field (box). The line total field was working on the form and
transferring the data into the table, but now has stopped and I am
not sure why?

I have deleted the old fields in the form, created new bound
controls, and then did a test record, but it is not transferring
still... that is why I thought maybe I needed to do something in the
table as well?
In general, you really shouldn't be storing the calculated value.
The rationale for this is that if one of the independant fields
changes then you have to provide some means for ensuring that the
derived value is updated. Instead you calculate it for display
purposes only. If for some reason you do need to store the value
then instead of an expression in the ControlSource of the control
you would need to use VBA to assign the value of the expression to a
bound control (where the controlsource is mapped to a field in the
table).

Regarding multiple users, are they all using the same database (from
the same location?) In a multiuser situation you really need to
split the database so that you have separate front-end and back-end
databases. The front-end database has the forms, queries, reports,
code and links to the back-end tables. The back-end has only the
tables. Then give each user their own copy of the front-end database.

To secure the objects you can either distribute MDEs to your users
and/or implement user level security. Here is a link to Lynn Trapps
website which is a good starting point for User Level Security:
http://www.ltcomputerdesigns.com/Security.htm

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi,

I have a form for order entry and a correlating table.... I used an
expression to calculate values (ie- multiply the qty. by the unit
cost to give line total). It was working and transferring the
calculated amounts into the table, but now a few weeks later, it is
not transferring any of the calculated information into the table.
The calculations do occur and show in the order entry form, however,
they are not going into the table.

We do have several users in this database ... did someone alter
something and now I am forgetting where to look to change it back(I
am a beginner at setting up databases)? Additionally, is there a
way to lock the database from design changes all together without a
password?

Thanks for your help!
Shalen
Click to expand...
Click to expand...
Click to expand...
 
Klatuu, Thanks for your help... I feel like we are getting closer here, but
maybe I am not explaining the database's function well. : )

It is used for sales of products... an order comes in for 127 units at 14.99
each, my boss wants to see the line total on a query for his report; the
sales being done monthly, weekly, daily...

I set up in the form a bound control to the the line total field (which uses
an expression like this =[Qty]*[Unit Cost] ) and this was working... it
appeared in the line total box on the form and in the line total field in the
table, but now it is not.

I went back into the form, deleted the old and created new, ran a test, and
it is not working. I am a novice at databases, and I possibly did another
step in the table... setting a control there?? I cannot remember.. I have
done a lot of reading to get this database to work properly and is working
for us in a large way (we were using Excel and it was tedious)!

Also, I read that you can have the queries do the calculations.. would this
be best? Everyone keeps saying you cannot store values in the table from
calculations in the form?? I just don't understand why it worked before and
now does not??

Thank you everyone for helping me figure this out!!!
 
Also, I read that you can have the queries do the calculations..
would this be best?
Click to expand...

YES! Just use the same query for your reports and you don't have to worry
about maintaining a calculated field (Which is a MAJOR pain - take it from
one who has done it!)
Everyone keeps saying you cannot store values in
the table from calculations in the form?? I just don't understand
why it worked before and now does not??
Click to expand...

There must have been some other means of getting data into the table because
it simply does not work that way in Access.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Klatuu, Thanks for your help... I feel like we are getting closer
here, but maybe I am not explaining the database's function well. : )

It is used for sales of products... an order comes in for 127 units
at 14.99 each, my boss wants to see the line total on a query for his
report; the sales being done monthly, weekly, daily...

I set up in the form a bound control to the the line total field
(which uses an expression like this =[Qty]*[Unit Cost] ) and this
was working... it appeared in the line total box on the form and in
the line total field in the table, but now it is not.

I went back into the form, deleted the old and created new, ran a
test, and it is not working. I am a novice at databases, and I
possibly did another step in the table... setting a control there??
I cannot remember.. I have done a lot of reading to get this database
to work properly and is working for us in a large way (we were using
Excel and it was tedious)!

Also, I read that you can have the queries do the calculations..
would this be best? Everyone keeps saying you cannot store values in
the table from calculations in the form?? I just don't understand
why it worked before and now does not??

Thank you everyone for helping me figure this out!!!
You can prevent users from making any design changes by converting
your mdb file to an mde. See Tools, Database Utilities, Make Mde.
Do a little reading on the mde file format before you do.
Basically, it is like an mdb, but with the ability to create or
modify objects.

As to the calculated fields not working, somehow something has
changed to cause this. Open your form, and check to see that the
calculated fields control sources are still in place. Also look at
where the calculations are being done to ensure they are working
correctly. If the correct calculation is show in the text box, then
it is okay.

Now, what you really need to do is change your approach. If you do
any study at all or read posts on this site, you will find that
storing calculated values in a table violated database normalization
rules. There is absolutely no justification for storing calculated
values. The correct approach is to only store the values used in
the calculation and do the calculation wherevever a human needs to
see the result of the calculation.
Click to expand...
Click to expand...
 
Shalen,
This ---- =[Qty]*[Unit Cost] --- is NOT a bound field. It is an
expression. A control is bound to the RecordSource in one and ONLY one
case. That case is if the ControlSource contains the name of a field in the
form's RecordSource. This expression NEVER, EVER under any condition saved
the calculated value to your table.

As long as the record contains the Qty field and the UnitCost, there is no
need to store the total. Calculate it in your queries as you need it.

Shalen said:
Klatuu, Thanks for your help... I feel like we are getting closer here,
but
maybe I am not explaining the database's function well. : )

It is used for sales of products... an order comes in for 127 units at
14.99
each, my boss wants to see the line total on a query for his report; the
sales being done monthly, weekly, daily...

I set up in the form a bound control to the the line total field (which
uses
an expression like this =[Qty]*[Unit Cost] ) and this was working... it
appeared in the line total box on the form and in the line total field in
the
table, but now it is not.

I went back into the form, deleted the old and created new, ran a test,
and
it is not working. I am a novice at databases, and I possibly did another
step in the table... setting a control there?? I cannot remember.. I have
done a lot of reading to get this database to work properly and is working
for us in a large way (we were using Excel and it was tedious)!

Also, I read that you can have the queries do the calculations.. would
this
be best? Everyone keeps saying you cannot store values in the table from
calculations in the form?? I just don't understand why it worked before
and
now does not??

Thank you everyone for helping me figure this out!!!
--



Klatuu said:
You can prevent users from making any design changes by converting your
mdb
file to an mde. See Tools, Database Utilities, Make Mde. Do a little
reading
on the mde file format before you do. Basically, it is like an mdb, but
with
the ability to create or modify objects.

As to the calculated fields not working, somehow something has changed to
cause this. Open your form, and check to see that the calculated fields
control sources are still in place. Also look at where the calculations
are
being done to ensure they are working correctly. If the correct
calculation
is show in the text box, then it is okay.

Now, what you really need to do is change your approach. If you do any
study at all or read posts on this site, you will find that storing
calculated values in a table violated database normalization rules.
There is
absolutely no justification for storing calculated values. The correct
approach is to only store the values used in the calculation and do the
calculation wherevever a human needs to see the result of the
calculation.
Click to expand...
Click to expand...
 
It is not that you cannot save a calculated field to a table, it is that you
should not. Without the database at hand, it would be impossible for any of
us to know why it did work but now is not working.

There are a couple of ways a calculation can be saved. Once is to bind a
control to the table field where the calculation will be saved and perform
the calculation at some point before the record is updated. The form's
Before update, for example. The other would be to use code to manually
update the field in the record from an unbound contol.

As you are seeing from the responses here, it is never a wise practice. I
have repeatedly seen poster argue that they have to do so because of some
special circumstance. In reality, the circumstance is they are not
practicing good database design.

I know you are new at this, so don't feel like you are getting beat up. We
are only trying to guide you in the right direction. It is always best to
perform the calculation only when the value to be calculated has to be
presented to a human, whether it be on a form or in a report.

One of the practices I employee to ensure the calculation is always
consistent, to avoid having to rewrite the same code, and to prevent having
to find and modify the same calculation in multiple places when the business
rules change, it to write a function that does the calculation and store it
in a standard module. That way, you can call the same functioin from forms,
reports, and queries.

Shalen said:
Klatuu, Thanks for your help... I feel like we are getting closer here, but
maybe I am not explaining the database's function well. : )

It is used for sales of products... an order comes in for 127 units at 14.99
each, my boss wants to see the line total on a query for his report; the
sales being done monthly, weekly, daily...

I set up in the form a bound control to the the line total field (which uses
an expression like this =[Qty]*[Unit Cost] ) and this was working... it
appeared in the line total box on the form and in the line total field in the
table, but now it is not.

I went back into the form, deleted the old and created new, ran a test, and
it is not working. I am a novice at databases, and I possibly did another
step in the table... setting a control there?? I cannot remember.. I have
done a lot of reading to get this database to work properly and is working
for us in a large way (we were using Excel and it was tedious)!

Also, I read that you can have the queries do the calculations.. would this
be best? Everyone keeps saying you cannot store values in the table from
calculations in the form?? I just don't understand why it worked before and
now does not??

Thank you everyone for helping me figure this out!!!
--



Klatuu said:
You can prevent users from making any design changes by converting your mdb
file to an mde. See Tools, Database Utilities, Make Mde. Do a little reading
on the mde file format before you do. Basically, it is like an mdb, but with
the ability to create or modify objects.

As to the calculated fields not working, somehow something has changed to
cause this. Open your form, and check to see that the calculated fields
control sources are still in place. Also look at where the calculations are
being done to ensure they are working correctly. If the correct calculation
is show in the text box, then it is okay.

Now, what you really need to do is change your approach. If you do any
study at all or read posts on this site, you will find that storing
calculated values in a table violated database normalization rules. There is
absolutely no justification for storing calculated values. The correct
approach is to only store the values used in the calculation and do the
calculation wherevever a human needs to see the result of the calculation.
Click to expand...
Click to expand...
 
Back
Top