Calculated field in a subform (1 Viewer)

Mat1994

Registered User.
Local time
Tomorrow, 01:56
Joined
Nov 29, 2018
Messages
94
Hi All,

I've attached a demo of my problem.

The DB form has:
- "ObsDistRef" is a combo box and can have the value ("BR brake release" or "LO liftoff end of runway")
-"Dist_init" is used for a calculation
-"Dist" is the value I want stored. It referes to the distance between "BR brake release" and the Obstacle or the distance between "LO liftoff end of runway" and the obstacle.
-"TORA_FNL" is the distance between "BR brake release" and "LO liftoff end of runway". It is a value used to correct the dist depending on the choose of the "ObsDistRef"

In addition, "ObsDistRef" has a afterUpdate event to corrects the value of dist depending on the chose of "BR brake release" or "LO liftoff end of runway".

What I would like :
- The user chooses a "ObsDistRef"
- Than the user inputs a "Dist"
- What I would like is when the user changes "ObsDistRef", the "Dist" value corrects itselve automatically.

I've managed to code the maths correctly.The Dist value correct itself correctly if i change the "ObsDistRef" ones. But the problem is when I click multiple times on the same "ObsDistRef" the maths repeats itself and I don't want that.

How can I stop my equations repeating themselves if the same value of "ObsDistRef" is click on multiple time?

Thank you for your help,
Mat
 

Attachments

  • DB-forum.accdb
    672 KB · Views: 233

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
Not sure how many times to say it, but that should be a calculated control and not a stored value. If you need to show it calculate it.
 

Mat1994

Registered User.
Local time
Tomorrow, 01:56
Joined
Nov 29, 2018
Messages
94
Sorry I know you told me this before.

But I need the "ObsDistRef" and the "Dist" to be stored and shown to the used when the changes are made.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
So the query for the subform is
Code:
SELECT obstacles.obstacleid, 
       obstacles.runwayid, 
       obstacles.specification, 
       obstacles.obsdistref, 
       obstacles.dist_init, 
       runway.tora_fnl, 
       Iif([obsdistref] = "br brake release", [dist_init] + [tora_fnl], 
       [dist_init] - [tora_fnl]) AS CalcDist 
FROM   runway 
       INNER JOIN obstacles 
               ON runway.runwayid = obstacles.runwayid;
Then just add CaclDist and get rid of Dist. Does not need to be stored in the DB.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
But I need the "ObsDistRef" and the "Dist" to be stored and shown to the used when the changes are made.
Why does Dist need to be stored?
If in a Database A + B = C, you store A and B and rarely C unless there is a hard reason.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
Not trying to be a pain, but just trying to get you to do best practices. There are lots of reasons not to store calculations and you have demonstrated a good example.
 

Mat1994

Registered User.
Local time
Tomorrow, 01:56
Joined
Nov 29, 2018
Messages
94
Dist has to be stored because in case of an aircraft accident. If an aircraft craches in a obstacles, during the investigation we have to verifie the distance value we have stored and used to calculate the aircraft performance. Storing the value is also a quick way for my colleages to check if there is a mistake.
 

Mat1994

Registered User.
Local time
Tomorrow, 01:56
Joined
Nov 29, 2018
Messages
94
Also, to have the dist value displayed in the form also allow my colleagues to check if the dist they want to use after export is accurate.

You're not a pain at all, I'm new at Access and I really appreciate the help you and the other on this forum give me.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
Just get rid of this line in the afterupdate
Code:
Me.Dist_init = Me.Dist
You will store initial and final and it will update correctly when you change ObsDistRef
 

Mat1994

Registered User.
Local time
Tomorrow, 01:56
Joined
Nov 29, 2018
Messages
94
Well that's pretty weird. That's how I wrote the code in the first place and it didn't work than but it works now.
Sorry for that.
Thanks
 

Users who are viewing this thread

Top Bottom