Not seeing caculations made in forms in tables

[Guest]

I have a simple calculation on a form in a field called Profit/Loss which
reads =[total income]-[total expenses] it calculates fine and I see it on the
form. When I switch to the table each cell in the column for Profit/Loss is
blank why?

 

[Lynn Trapp]

Basically, because the profit/loss control on your form is not bound to the
table AND it does not need to be. If you can calculate the value of
profit/loss for the form then you can calculate it anywhere else you need
it.

 

[J]

Each text box on a form is bound to a single field OR it's unbound OR
it's a temporary calculation. You can easily tell the difference in
design view:
- For a textbox bound to a single field, it will show ONLY that single
field's name in design view.
- For an unbound textbox, it will actually say "Unbound" when in
design view.
- For a textbox performing a calculation, it will be preceded with the
equals sign.

The last two types do not save to your table and are 'lost' when you
close the form. Alternatively, editting the first type (a regular
bound text box) actually edits the table.



If you HAVE to save that field, you'll have to turn your "Profit/Loss"
textbox into a text box bound to "[Profit/Loss]", and use visual basic
to run the calculation in code whenever [total income] or [total
expenses] are updated. Don't forget to set the text box's "Locked"
value to true.

HOWEVER, any experienced access user will tell you not to save a
calculated field like that permanently if you don't have to.
You are far better off NOT having a field for "Profit/Loss" in your
table at all. If you need to see that field calculated in a table-
style view, you need to write a query. A query is a temporary table
view (with calculations) that draws from a table... you get to pick
what fields it takes from any table.

Best of luck,
~J

 

[Guest]

Lynn:
Thanks for the answer , but I cannot figure out how to perform the
calculations so that it appears in the cells under the profit /loss column on
tables, how do I do that? Or if I need them bound how do I do that?
Thanks
Graeme

Basically, because the profit/loss control on your form is not bound to the
table AND it does not need to be. If you can calculate the value of
profit/loss for the form then you can calculate it anywhere else you need
it.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com

I have a simple calculation on a form in a field called Profit/Loss which
reads =[total income]-[total expenses] it calculates fine and I see it on
the
form. When I switch to the table each cell in the column for Profit/Loss
is
blank why?

 

[Lynn Trapp]

Graeme,
My point is that you do NOT want to store the calculation in the table.
Simply recalculate the value any time you need it. It is much more
efficient than storing it.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com

Lynn:
Thanks for the answer , but I cannot figure out how to perform the
calculations so that it appears in the cells under the profit /loss column
on
tables, how do I do that? Or if I need them bound how do I do that?
Thanks
Graeme

Basically, because the profit/loss control on your form is not bound to
the
table AND it does not need to be. If you can calculate the value of
profit/loss for the form then you can calculate it anywhere else you need
it.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com

I have a simple calculation on a form in a field called Profit/Loss
which
reads =[total income]-[total expenses] it calculates fine and I see it
on
the
form. When I switch to the table each cell in the column for
Profit/Loss
is
blank why?

 

[Guest]

J:
Ok this is beginning to make some sense. I don't want to bind the Text Box's
to the table. I do not see anything in the design view of the form that shows
Bound or unbound. I have tried to create a query but the form is not
accessible from Queries and of coarse the table does not have any of the
calculations. So how do I create a query that shows the calculations?
--
GMcKenna

Each text box on a form is bound to a single field OR it's unbound OR
it's a temporary calculation. You can easily tell the difference in
design view:
- For a textbox bound to a single field, it will show ONLY that single
field's name in design view.
- For an unbound textbox, it will actually say "Unbound" when in
design view.
- For a textbox performing a calculation, it will be preceded with the
equals sign.

The last two types do not save to your table and are 'lost' when you
close the form. Alternatively, editting the first type (a regular
bound text box) actually edits the table.



If you HAVE to save that field, you'll have to turn your "Profit/Loss"
textbox into a text box bound to "[Profit/Loss]", and use visual basic
to run the calculation in code whenever [total income] or [total
expenses] are updated. Don't forget to set the text box's "Locked"
value to true.

HOWEVER, any experienced access user will tell you not to save a
calculated field like that permanently if you don't have to.
You are far better off NOT having a field for "Profit/Loss" in your
table at all. If you need to see that field calculated in a table-
style view, you need to write a query. A query is a temporary table
view (with calculations) that draws from a table... you get to pick
what fields it takes from any table.

Best of luck,
~J

I have a simple calculation on a form in a field called Profit/Loss which
reads =[total income]-[total expenses] it calculates fine and I see it on the
form. When I switch to the table each cell in the column for Profit/Loss is
blank why?

 

[Rick Brandt]

J:
Ok this is beginning to make some sense. I don't want to bind the
Text Box's to the table. I do not see anything in the design view of
the form that shows Bound or unbound. I have tried to create a query
but the form is not accessible from Queries and of coarse the table
does not have any of the calculations. So how do I create a query
that shows the calculations?

In design view of a query based on your table add a new query column and
enter...

DesiredFieldName: Your expression

 

[Pat Hartman \(MVP\)]

To bind your form to a query rather than a table, go to the Data tab and
replace the table name in the RecordSource property with the name of your
new query. As long as you selected all the columns you need for the form,
the form will continue to work. All you'll need to do is to replace the
controlSource for the calculated control by the name of the new calculated
field. You can select it from the dropdown.

J:
Ok this is beginning to make some sense. I don't want to bind the Text
Box's
to the table. I do not see anything in the design view of the form that
shows
Bound or unbound. I have tried to create a query but the form is not
accessible from Queries and of coarse the table does not have any of the
calculations. So how do I create a query that shows the calculations?
--
GMcKenna

Each text box on a form is bound to a single field OR it's unbound OR
it's a temporary calculation. You can easily tell the difference in
design view:
- For a textbox bound to a single field, it will show ONLY that single
field's name in design view.
- For an unbound textbox, it will actually say "Unbound" when in
design view.
- For a textbox performing a calculation, it will be preceded with the
equals sign.

The last two types do not save to your table and are 'lost' when you
close the form. Alternatively, editting the first type (a regular
bound text box) actually edits the table.



If you HAVE to save that field, you'll have to turn your "Profit/Loss"
textbox into a text box bound to "[Profit/Loss]", and use visual basic
to run the calculation in code whenever [total income] or [total
expenses] are updated. Don't forget to set the text box's "Locked"
value to true.

HOWEVER, any experienced access user will tell you not to save a
calculated field like that permanently if you don't have to.
You are far better off NOT having a field for "Profit/Loss" in your
table at all. If you need to see that field calculated in a table-
style view, you need to write a query. A query is a temporary table
view (with calculations) that draws from a table... you get to pick
what fields it takes from any table.

Best of luck,
~J

I have a simple calculation on a form in a field called Profit/Loss
which
reads =[total income]-[total expenses] it calculates fine and I see it
on the
form. When I switch to the table each cell in the column for
Profit/Loss is
blank why?

 

[Larry Linson]

In the Property Sheet for the Form (click the upper-leftmost little
rectangle, then right click and choose Properties from the menu that pops
up, if the Property Sheet isn't visible), the Form will have something in
RecordSource (a Table or a Query) if it is a "bound form". A Control, in its
data tab in the Property Sheet, will have the name of a Field in the Form's
RecordSource if it is bound. If the Control has a calculation in its
RecordSource, it's a Calculated Control.

As a Control can't have both, it takes extra work to save the calculated
value, and you only want to do that if you can't recalculate it when needed
(as a an "extended price/cost" where the unit price used the the calculation
might change -- you don't change what you've already charged on previous
sales when your supplier raises or lowers the unit price in the future).

I don't understand what you mean "the form is not accessible from Queries".
It is as accessible from the Queries tab as it would be from the Tables
tab -- there's an icon on a toolbar that lets you create an "instant Form"
from either (once in a while, I will use such an instant Form as a starting
point, but usually I go to the Forms tab of the database window, and click
New to begin the process of creating a Form).

Larry Linson
Microsoft Access MVP

J:
Ok this is beginning to make some sense. I don't want to bind the Text
Box's
to the table. I do not see anything in the design view of the form that
shows
Bound or unbound. I have tried to create a query but the form is not
accessible from Queries and of coarse the table does not have any of the
calculations. So how do I create a query that shows the calculations?
--
GMcKenna

Each text box on a form is bound to a single field OR it's unbound OR
it's a temporary calculation. You can easily tell the difference in
design view:
- For a textbox bound to a single field, it will show ONLY that single
field's name in design view.
- For an unbound textbox, it will actually say "Unbound" when in
design view.
- For a textbox performing a calculation, it will be preceded with the
equals sign.

The last two types do not save to your table and are 'lost' when you
close the form. Alternatively, editting the first type (a regular
bound text box) actually edits the table.



If you HAVE to save that field, you'll have to turn your "Profit/Loss"
textbox into a text box bound to "[Profit/Loss]", and use visual basic
to run the calculation in code whenever [total income] or [total
expenses] are updated. Don't forget to set the text box's "Locked"
value to true.

HOWEVER, any experienced access user will tell you not to save a
calculated field like that permanently if you don't have to.
You are far better off NOT having a field for "Profit/Loss" in your
table at all. If you need to see that field calculated in a table-
style view, you need to write a query. A query is a temporary table
view (with calculations) that draws from a table... you get to pick
what fields it takes from any table.

Best of luck,
~J

I have a simple calculation on a form in a field called Profit/Loss
which
reads =[total income]-[total expenses] it calculates fine and I see it
on the
form. When I switch to the table each cell in the column for
Profit/Loss is
blank why?