Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-20-2018, 11:53 AM   #1
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 67
Thanks: 5
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Calculated field in a subform

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

Mat1994 is offline   Reply With Quote
Old 12-20-2018, 12:17 PM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 955
Thanks: 20
Thanked 242 Times in 237 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Calculated field in a subform

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
MajP is online now   Reply With Quote
Old 12-20-2018, 12:20 PM   #3
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 67
Thanks: 5
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field in a subform

Many thanks, It works now!

Mat1994 is offline   Reply With Quote
Old 12-20-2018, 04:22 PM   #4
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 67
Thanks: 5
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field in a subform

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
Mat1994 is offline   Reply With Quote
Old 12-21-2018, 06:14 AM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 955
Thanks: 20
Thanked 242 Times in 237 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Calculated field in a subform

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.
MajP is online now   Reply With Quote
Old 01-02-2019, 10:12 AM   #6
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 67
Thanks: 5
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field in a subform

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
Mat1994 is offline   Reply With Quote
Old 01-02-2019, 10:39 AM   #7
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 955
Thanks: 20
Thanked 242 Times in 237 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Calculated field in a subform

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.

MajP is online now   Reply With Quote
Old 01-06-2019, 04:33 PM   #8
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 67
Thanks: 5
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field in a subform

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 is offline   Reply With Quote
Old 01-06-2019, 04:45 PM   #9
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 67
Thanks: 5
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field in a subform

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 is offline   Reply With Quote
Old 01-08-2019, 05:47 PM   #10
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 67
Thanks: 5
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field in a subform

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
Mat1994 is offline   Reply With Quote
Old 01-08-2019, 06:00 PM   #11
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 955
Thanks: 20
Thanked 242 Times in 237 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Calculated field in a subform

Quote:
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 by MajP; 01-08-2019 at 06:06 PM.
MajP is online now   Reply With Quote
Old 01-10-2019, 03:14 PM   #12
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 67
Thanks: 5
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field in a subform

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

Mat1994 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Subform linked by calculated field gives field cannot be updated error evwool Forms 0 11-28-2012 01:28 PM
Copying Value From A Calculated Field In A Subform From A Dirty Field On The Subform Adam858 Forms 1 06-15-2012 02:15 PM
Calculated Subform Field MrBowder Forms 4 02-28-2012 05:02 PM
Sum a calculated field in a subform kenb214 General 2 10-21-2010 10:13 AM
[SOLVED] Calculated field on a subform rtewell Forms 1 01-09-2004 09:27 AM




All times are GMT -8. The time now is 08:33 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World