Field with calculation and control source (1 Viewer)

jharding08

Member
Local time
Yesterday, 21:36
Joined
Feb 16, 2021
Messages
52
I have a field in a subform that I need to have as a calculated field but also stored in the database.

I have a table with Start Date, End Date and Duration

In the sub form(s) I would need the duration to be a calculation of WorkDays(startdate, enddate) and the next subform (With same fields) should default Start date of subform 2 to the end date of subform 1.

I would need all this calculated when the field has changed or on new. Should I put the calculation in the After_update of the involved fields? and just have the Control Source link to the table field?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:36
Joined
Feb 28, 2001
Messages
22,780
OK, let's break this down.

I need to have as a calculated field but also stored in the database.
just have the Control Source link to the table field?

If you want to save this calculated value, then the answer is a resounding "USE BOUND FIELDS." Which means that the calculation must done using VBA in some event as opposed to defining a formula for the computed value's .ControlSource.

However there is the question of WHY you need to store 3 values when two of them are bound by a computation to produce the third one? I won't say it is never done, but it is rare to do something like this. Usually, you just put that value as a computed field in a query and use the query as a (non-updateable) .RecordSource for whatever report needs the value.

You can EASILY just compute the value in any one of several ways. The easiest might be to use VBA in the BEFORE_UPDATE event of the sub-form. However, there is also the possibility that if you wanted to be able to edit one or both of the dates and then update the computation, you could perform the computation in the LOST_FOCUS event of each of the dates. There are other events as well, but this one is my personal favorite.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:36
Joined
Feb 19, 2002
Messages
36,284
In a relational database, no record has any relationship to any other record. Tables are unordered sets. Think of a bag of marbles. Given that, you can certainly do whatever you want but you must also impose some pretty rigid rules starting with - the end date of an existing record CANNOT ever be changed if there is a record that "follows" it logically.
 

jharding08

Member
Local time
Yesterday, 21:36
Joined
Feb 16, 2021
Messages
52
I agree that Duration, as a calculation, should just be an output and not stored in the DB. Thank you for the clarification
 

Users who are viewing this thread

Top Bottom