Capturing data from a form and writing to a table.

  • Thread starter Thread starter JDP
  • Start date Start date
J

JDP

I posted this yesterday on the Forms newsgroup, and
apologize in advance if I should not be reposting here.

In a form I have a series of calculated values, resulting
in a final calculation. The source data is from three
linked tables. All calculations are correct, and I get
the result I need.
Question:
How do I write the resulting calculation from the text box
back to one of the source tables? I need this piece of
data to generate a report.
It must be obvious to someone, and I am too close to the
problem.

Any help would be appreciated.
 
It's generally not a good practice to store calculated fields in your
tables. It's best to add calculated fields to the report query to get the
same results as your form.

Max
 
First Max I want to thank you for getting back to me so
quickly.
I agree with you re. the statement, unfortuantely the form
contains well over 30 separate calculations to get to this
data value, and the client does not want me to re-design
the form, only copy the result from the form to a field
added in the table.
I have consulted with the client that this is not a
reliable approach, as the form must be opened in order for
the calculations to run.

So back to the origional question, is there an easy way to
capture the result of a calculated field, and add it into
a record?
 
OK. Add another text box bound to your table field and in the
Form_Current event or After_Update event of the form or other
contol/s add BoundTextboxName.Value =CalculatedTextbox.Value.

That's the easy way. If you can't add a form field, you can add an
SQL command in code.

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [Table Name] SET [Table Name].[Field Name] = "
& Me.txtCalcField.Value & " WHERE [Table Name].[Primary Key ID]=" &
Me.Primary_Key_ID
DoCmd.SetWarnings True

Another way would be to create a DAO recordset and update the record
that way.

Good Luck,
Max
 
Back
Top