Form/table question

[S4Aero]

I have a table - FandBCostingTable - and a related form - FandBCostingForm.
I enter data into the form that doesn't appear on the table or in any
query's. Probably something stupid (I'm still an Access Newbie)

 

[John W. Vinson]

I have a table - FandBCostingTable - and a related form - FandBCostingForm.
I enter data into the form that doesn't appear on the table or in any
query's. Probably something stupid (I'm still an Access Newbie)

What is the Recordsource property of FandBCostingForm? What are the Control
Sources of the controls on the form? The Recordsource should be the tablename
or an updateable query based on the table, the Control Sources should be
fieldnames in the table.

 

[S4Aero]

The recordsource IS the FandBCostingTable. The control sources are indeed
field names from the table.

For example, I calculate the cost for meal 1 - all subtotals are totaled in
"Meal1Total". Meal1Total is the control source for "AdultMeal1". It all
totals correctly, with the # (eg.$47.00) in Meal1Total appearing on the form
in "AdultMeal1". When I look at the table data, AdultMeal1 shows "$0"

 

[John W. Vinson]

The recordsource IS the FandBCostingTable. The control sources are indeed
field names from the table.

For example, I calculate the cost for meal 1 - all subtotals are totaled in
"Meal1Total". Meal1Total is the control source for "AdultMeal1". It all
totals correctly, with the # (eg.$47.00) in Meal1Total appearing on the form
in "AdultMeal1". When I look at the table data, AdultMeal1 shows "$0"

You can't have it both ways! Either the textbox's control source is a
fieldname or it's a subtotal expression. It can't be both.

And you should NOT be storing the totals *anywhere*. Storing derived data such
as this in your table accomplishes three things: it wastes disk space; it
wastes time (almost any calculation will be MUCH faster than a disk fetch);
and most importantly, it risks data corruption. If one of the underlying
fields is subsequently edited, you will have data in your table WHICH IS
WRONG, and no automatic way to detect that fact.

Just redo the calculation whenever you need it, either as a calculated field
in a Query or just as you're now doing it - in the control source of a Form or
a Report textbox.

 

[S4Aero]

thanks John. I guess I'll have to write a query based on a form to produce
the report I need or at least do the calculation in the query. Which is
better, or is it even possible to write the query based on a form

 

[John W. Vinson]

thanks John. I guess I'll have to write a query based on a form to produce
the report I need or at least do the calculation in the query. Which is
better, or is it even possible to write the query based on a form

Either is possible. You can use a criterion *or* a calculated field in a query
by typing

[Forms]![NameOfYourForm]![NameOfControl]

in the criteria line or in a vacant field cell respectively.

I'd really recommend doing the sum calculation in the QUery though; you can
reuse the query on either a form or a report, if that's appropriate.