How to write the content of a formfield into a table

[=?ISO-8859-1?Q?Peter_H=F6ltschi?=]

Hallo
I have one main form with two subforms, which are in relation 1:n to
each other ( Kustomeradress>1:n>Projectdetails >1:n>Projectwork). My
first subform has in a field the following function to collect data from
its subfield:
=DSum("[Time]";"Projectwork";" [PPNr]=Projectdetails![PPNr]")

PPNr is the Projectnumber. I would like to write down the DSum-Result
into the table which is also called Projectdetails. What is the easiest
way to do this?

Best regards
Peter

 

[Arvin Meyer]

Typically, writing a calculated value back to the database is a violation of
Normalization rules, since you can always re-calculate the same result from
existing data. There are some exceptions:

1. Where the data requires history and you are not storing the individual
elements of the calculation.
2. Where the calculations are intense and require long computational times.

If that is not the case, don't store the calculation. If it is the case,
there are several ways:

1. Create an Insert (Append) statement and append the data to the table(s)
2. Add a bound textbox control which is normally hidden, and "push" data
into it using the AfterUpdate event of each of the controls which comprise
the calculation.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[=?ISO-8859-1?Q?Peter_H=F6ltschi?=]

Typically, writing a calculated value back to the database is a violation of
Normalization rules, since you can always re-calculate the same result from
existing data. There are some exceptions:

1. Where the data requires history and you are not storing the individual
elements of the calculation.
2. Where the calculations are intense and require long computational times.

If that is not the case, don't store the calculation. If it is the case,
there are several ways:

1. Create an Insert (Append) statement and append the data to the table(s)
2. Add a bound textbox control which is normally hidden, and "push" data
into it using the AfterUpdate event of each of the controls which comprise
the calculation.

Dear Mr. Meyer
Due to your explanation I will not write calculated value to the table.
But my problem is, that I want to have this calculated value displayed
in a report. Now how can I do this exactly?

Thanks Peter

 

[Douglas J. Steele]

Dear Mr. Meyer
Due to your explanation I will not write calculated value to the table.
But my problem is, that I want to have this calculated value displayed
in a report. Now how can I do this exactly?

Create a query that includes the calculation, and base the report on the
query, not on the table.

 

[Arvin Meyer]

You have 3 choices:

1. You may duplicate your expression in a textbox on the report.

2. You can use a query as the recordsource of the report and create a query
column using the expression.

3. If the form is always open when the report is run, and the report is a
mirror of the data on the form, you can just read the value from the open
form:

=[Forms]![MyForm]![MyTextBox]

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access