Calculated field in a subform (1 Viewer)

Mat1994

Registered User.
Local time
Today, 22:00
Joined
Nov 29, 2018
Messages
94
Hi All,

I have a simple question concerning a calculation field a a subform.

In my form (frm_UserInterface), I have 2 tab control, "Runway Information" and "Obsctacles + Procedure Information".
-In the tab Runway Information, I have a field called TORA_FNL. (TORA_FNL is a number.)
-In the tab Obstacles, I have a subform about my obstacles. In the subform I have a field (ObDistRef), that can have the value "BR brake release" or "LO liftoff end of runway". I also have a field "Dist" that depends on the value chosen in "ObDistRef" and the value of TORA_FNL.

The equation is :
IF ObDistRef = BR Brake release THEN Dist=Dist + TORA_FNL
ELSE ObDistRef= LO liftoff end of runway THEN Dist=Dist-TORA_FNL

I would like the VB code to go in the on click event on the "ObDistRef" field. I can't seem to get the code to get the value of TORA_FNL.


So I tried writing this :

Private Sub ObDistRef_Click()
If Me.ObDistRef = "BR brake release" Then
Me.Dist = Me.Dist + Me.TORA_FNL
Else : Me.Dist = Me.Dist - Me.TORA_FNL
End If
End Sub

As the Me.TORA_FNL doesn't work properly, I tried replacing "Me.TORA_FNL" with "[frm_UserInterface].[Form]![Me.TORA_FNL]" but it doesn't work.

So i just can't manage to get the code to get the value of TORA_FNL in the Runeway Information tab. Hoping someone can help me.

Thank you for your help.
Mat
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:00
Joined
May 21, 2018
Messages
8,463
You are calling this code from the subform. So Me is referring to the subform. TORA_FNL lives on the Main form which is the parent of the subform. So you need
Me.Parent.Tora_Fnl
 

Mat1994

Registered User.
Local time
Today, 22:00
Joined
Nov 29, 2018
Messages
94
Many thanks, It works now!
 

Mat1994

Registered User.
Local time
Today, 22:00
Joined
Nov 29, 2018
Messages
94
I have a similar problem with another code I have.

I have a form (frm_UserInterface), in the form there is a tab with a subform (sfr_Obstacles).
- I have one field (ObHtRef) that can have 2 values ("BR brake release" or "LO liftoff end of runway").
-This field has a "on click" event, when if you choose one of the 2 values it will calculate an equation (A=A+B)

The problem is, if I click on the same value ("BR brake release" or "LO liftoff end of runway"), the equation repeats itself.
I'd like to avoid that.

I tried changing my code to the after_Update and the On_change event, but the problem is still there.
Is there anything I can add to my code to avoid the equation from repeating itself when after I chose the same value in ObHtRef?

I hope I was clear enough,
Thank you for your help,
Mat
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:00
Joined
May 21, 2018
Messages
8,463
Sorry it is not very clear. Maybe posting the code would help. Are you saving the calculation in a field? Often this is a red flag, a DB is in some ways like a spread sheet. You let the db do the calculations dynamically in queries. Would it make sense to save A and B and show A+B dynamically when needed.
 

Mat1994

Registered User.
Local time
Today, 22:00
Joined
Nov 29, 2018
Messages
94
Hello and Happy new year!

The code is :

Private Sub ObDistRef_Click()
' will calculate the value of Dist depending on the chose of BR or LO

If Me.ObDistRef = "BR brake release" Then
Me.Dist = Me.Dist + Me.Parent.TORA_FNL

Else: Me.Dist = Me.Dist - Me.Parent.TORA_FNL

End If
End Sub

The idea behind this code is:
-The engineers will chose a "ObDistRef". The field is a combo box with only 2 value possible ("BR brake release" or "LO liftoff end of runway")
-Then the engineer has to input a "Dist". This field is the distance between the reference, so "BR brake release" or "LO liftoff end of runway", and the obstacle.
-The code is used when the engineer decides to change the obstacle reference (he changes "BR brake release" to "LO liftoff end of runway" or vice-versa)


The problem is that if the engineer clicks twice on the same reference ("BR brake release" or "LO liftoff end of runway") the on_click event happens systematically (which is normal).

I would like this on_click event to occurs only when the reference ("BR brake release" or "LO liftoff end of runway") changes.

I hope my problem was explained better, if not I'll attache my DB so you can go and have a look.
Thank you for your help,
Mat
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:00
Joined
May 21, 2018
Messages
8,463
Do you have to store this value? I think I would store InitialDistance and obstacle Reference. Then you display TotalDistance as the calculated field in a query or on a form. Then you can switch back and forth without problem.
 

Mat1994

Registered User.
Local time
Today, 22:00
Joined
Nov 29, 2018
Messages
94
Yes, this value needs to be stored.
Your idea is not bad. That's what I did to calcule other fields I have.
I'll try it again even if I would of prefered having just one Dist (no InitialDistance and FinalDistance)
 

Mat1994

Registered User.
Local time
Today, 22:00
Joined
Nov 29, 2018
Messages
94
I've just thought about it. But if I do a initial and final distance, the problem stays the same.
I would like the the Dist to correct itself when the ObDistRef changes. So this idea won't change the fact that the equations would repeat themselves if the same value of ObDistRef is selected.
Mat
 

Mat1994

Registered User.
Local time
Today, 22:00
Joined
Nov 29, 2018
Messages
94
Hi All,

I've manage to solve myu problem.
MajP was right. I add a new field called Dist_init and a field called Dist_FNL.

For the Dist_init, I created an event (after_Update) that populates the field Dist_FNL.
I wrote the simple equation : Me.Dist_FNL = Me.Dist_init

Then, for the ObDistRef, I modified the code like this :
Private Sub ObDistRef_Click()
' will calculate the value of Dist depending on the chose of BR or LO

If Me.ObDistRef = "BR brake release" Then
Me.Dist_FNL = Me.Dist_init + Me.Parent.TORA_FNL

Else: Me.Dist_FNL = Me.Dist_init - Me.Parent.TORA_FNL

End If
End Sub


The problem was, that having one variable (Dist) created a cycle where the value of the Dist always over-rigthed itself. It created a loop where the equations repeated themselves.
Having two variable breaks the loop, allowing only one variable to be modified after the on click event.

Thank you for your help,
Mat
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:00
Joined
May 21, 2018
Messages
8,463
MajP was right. I add a new field called Dist_init and a field called Dist_FNL.

That is not actually what I was suggesting. I was suggesting you do not store Dist_FNL instead you calculate it when needed using a query or calculated control. The problem with storing calculations is that you need to ensure you are always coding a recalculation.

Example:
You store in the database
PurchasePrice
Quantity
and you never store TotalPrice, instead in your query
Code:
Select PurchasePrice, Quantity, (PurchasePrice * Quantity) as TotalPrice from someTable
or in a calculated control
Code:
=[PurchasePrice] * [Quantity]

Sometimes the calculations can get complicated in a query, and it may be justified to store it like you are doing. However, try to avoid storing them when possible.
 
Last edited:

Mat1994

Registered User.
Local time
Today, 22:00
Joined
Nov 29, 2018
Messages
94
ok, unfortunatly my colleagues weren't satisfied if what I did.
The value need to be stored.
I have a new idea but I need to try it out.

Mat
 

Users who are viewing this thread

Top Bottom