Updating Calc Field from Form

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

Guest

I am wanting to update a particular field in a form based on the data entered
in another field and write it to the underlying table.

ie

Field 1 Date: 01/12/2006

Field 2 (calculated) Sample ID: 20061201

and then successfully update the table. I can get the second field to
return the number above but cannot figure out how to link it to the control
source in the relevant table.
 
Damo,

The advice you will almost universally receive to such a question is...
"don't do it". You should remove the Field 2 from the table. And then,
whenever you need the derived/formatted version of the date, you should
simply do it "on the fly" - the simplest way to do so would be to use
the Format property of the control on your form or report.
 
Svetlana,

In a database, it is almost always a bad idea to store data which is
derived/calculated from other existing data. It adds additional
unnecessary complication and difficulty, and increased probability of
error, in the sense that any time data is entered or changed it needs to
be updated in more then one place. This is based on one aspect of the
principle of data normalisation - one place for each data element, and
each data element in its place. Generally, if it can be
calculated/derived, then calculate/derive it when you need it.

There are very specific examples of where this is not the case... this
is where the values that the calculation is based on can change over
time. The classic example of this is an Invoice Amount based on a
Quantity * UnitPrice calculation, which needs to be stored to reflect
the price at the time of purchase, otherwise it will be wrong if the
price subsequently changes. But these circumstances are rare - and even
then some "purists" would argue that you should use a price history
table, and still perform the calculation on the fly based on a lookup to
the price for the purchase date.

In the case of Damo Waterboy's question, in fact this is not even a
calculation, as the desired result can be obtained simply by a
formatting of the existing data. So that would mean he would have two
fields with exactly the same information in them, which is clearly
pointless.
 
Thanks steve. There is one part I did not explain properly (actually left it
out by mistake). We have a number of sample points and the actul sample
nuber we have on our records is actually Customer #- Reverse Date - sample #
to uniquely identify the sample eg 147-20060607-14 - Customer 147, Date
7/6/2006, sample pt 14. Does this help or confuse the matter?
 
Damo,

Those three data elements should be kep in three separate fields. Which
I assume is already the case... No? I can appreciate that your business
rules/conventions are that you want to see this in the particular form
such as 147-20060607-14 but this will only ever be on a form or report.
There is still absolutely no need for this to be separately stored in
the table, and as mentioned before, some good reasons not to. When you
need it on your form or report, you can concatenate it in a calculated
field in the query that the form or report is based on...
SampleNumber: [CustomerNo] & "-" & Format(Reverse Date],"yyyymmdd" &
"-" & [SampleNo]
.... or in the Control Source of an unbound textbox on the form or
report, using similar syntax...
=[CustomerNo] & "-" & Format(Reverse Date],"yyyymmdd" & "-" & [SampleNo]

(Note that I have changed your field names to CustomerNo and SampleNo -
it is not a good idea to use a # as part of the name of a field or control.

If it was mine, I would use an AutoNumber field for the Primary Key in
this table, and also in table design set a Unique Index on the
combination of the 3 organic fields.
 
Back
Top