Refer to calculated field on Subform from Main form (1 Viewer)

Oreynolds

Member
Local time
Today, 23:48
Joined
Apr 11, 2020
Messages
110
Dear All,

I think the lockdown is getting to me and given me brain freeze.....

I have a cmd button on my main form "Purchase Orders" called tglRecieveAll.
On the main form is a subform set in datasheet view called "Purchase Order Subform".
On the subform footer is a calculated field called "SumQuantityReceived" which calculates fine and displays on the main form in another field.

When the value of the calculated field "SumQuantityReceived" = 0 I want to enable the cmd button "tglRecieveAll" on my main form. When the value of the field is >0 I want to disable the cmd button.

I have written the following code on my main form 'Current' procedure:

Code:
If Forms("Purchase Orders").[Purchase Order Subform].Form.SumItemsReceived.Value = 0 Then
Me.tglReceiveAll.Enabled = False
ElseIf Forms("Purchase Orders").[Purchase Order Subform].Form.SumItemsReceived.Value > 0 Then
Me.tglReceiveAll.Enabled = True
End If

The code runs with no errors but it isn't disabling the cmb button as I want. Any ideas what I'm doing wrong? I can only assume that given its a calculated field I am referring to the value technically does not get passed?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:48
Joined
Oct 29, 2018
Messages
14,594
Hi. Did you try stepping through the code?
 

Micron

AWF VIP
Local time
Today, 18:48
Joined
Oct 20, 2018
Messages
3,472
Did you use the wrong name in your code, or your post?
SumQuantityReceived
SumItemsReceived

EDIT - or maybe the button:
tglRecieveAll
tglReceiveAll
 
Last edited:

Oreynolds

Member
Local time
Today, 23:48
Joined
Apr 11, 2020
Messages
110
No, all the control names are correct as I get no code errors at all. I deliberately tried changing a name to check it errored and it did proving all the names are correct?
 

HalloweenWeed

Member
Local time
Today, 18:48
Joined
Apr 8, 2020
Messages
125
I see a possible issue here: this code appears to be wrote for the main form. And the code relies on a value in the subform, which is dependant on the current record. I would think the code should be modified to be moved to the "current" event on the subform. Also, IDK if it would also require a refresh or requery of the main form to take effect.
 

Oreynolds

Member
Local time
Today, 23:48
Joined
Apr 11, 2020
Messages
110
Hi. Did you try stepping through the code?
Might sound crazy but I’ve never mastered stepping through code and when I tried an online VBA video in doing it it intimated you cant step through form class subs which I find difficult to believe. I’ve written tonnes of amateur code and just relied on trial and error!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:48
Joined
Oct 29, 2018
Messages
14,594
Might sound crazy but I’ve never mastered stepping through code and when I tried an online VBA video in doing it it intimated you cant step through form class subs which I find difficult to believe. I’ve written tonnes of amateur code and just relied on trial and error!
Have you seen this one?
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:48
Joined
Sep 21, 2011
Messages
7,900
Might sound crazy but I’ve never mastered stepping through code and when I tried an online VBA video in doing it it intimated you cant step through form class subs which I find difficult to believe. I’ve written tonnes of amateur code and just relied on trial and error!
Not the best way to approach it TBH. :)

Put a breakpoint somewhere in the form code (where it is going to go through) and then walk through each line with F8.
Debug.Print <variable> as well as hovering over variables allows you to check values.
 

Micron

AWF VIP
Local time
Today, 18:48
Joined
Oct 20, 2018
Messages
3,472
A calculated value is retrievable in my limited testing - if the calculation is in the control source and not the default value.
You haven't said that the subform calculated control is showing a value or where the calculation expression is located.
Your code also doesn't deal with the possibility of Null values - is that possible?
 

GK in the UK

Registered User.
Local time
Today, 23:48
Joined
Dec 20, 2017
Messages
232
Have I missed something or is your logic the wrong way round ? You're disabling it when the value is 0.

I would write it like this:

Me.tglReceiveAll.Enabled = (Forms("Purchase Orders").[Purchase Order Subform].Form.SumItemsReceived.Value = 0)

If your main form is for the one record that is your Purchase Order, it will get called once when the form is opened, so it won't get evaluated again even if you edit records on the sub form.
 

zeroaccess

Active member
Local time
Today, 17:48
Joined
Jan 30, 2020
Messages
672
I see a possible issue here: this code appears to be wrote for the main form. And the code relies on a value in the subform, which is dependant on the current record. I would think the code should be modified to be moved to the "current" event on the subform. Also, IDK if it would also require a refresh or requery of the main form to take effect.
Yeah: the code needs to be run in the Current event of the subform. I don't believe a requery or refresh is needed for enable/disable of controls.
 

Oreynolds

Member
Local time
Today, 23:48
Joined
Apr 11, 2020
Messages
110
Have you seen this one?
Thanks for this, that’s a great site. Had a read through overnight so hopefully with a bit of practice that should really help things.

That said, all the debugging usually refers to when you encounter errors but in this case may code runs without any. All that is happening is that it continually follows the ElseIf =0 path even when that calculated field is definitely not 0. I display the value of the calculated field that originates in the subform footer in another field that references it in the control source on the main form so I can monitor it’s value and despite it definitely being >0 it still won’t follow the other IF path.

If I run the code referencing to a local field in my main form that’s value is not calculated and comes from the form query then the code works as expected based on its value which still points back to it being a field that is calculated on the form as the problem? Am I missing something? Thanks for your help
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:48
Joined
Sep 21, 2011
Messages
7,900
On the subform footer is a calculated field called "SumQuantityReceived" which calculates fine and displays on the main form in another field.
So why not just test against that control?

That said, all the debugging usually refers to when you encounter errors but in this case may code runs without any. All that is happening is that it continually follows the ElseIf =0 path even when that calculated field is definitely not 0. I display the value of the calculated field that originates in the subform footer in another field that references it in the control source on the main form so I can monitor it’s value and despite it definitely being >0 it still won’t follow the other IF path.

Believe me, that cannot happen. The computer just obeys the code. :)

Have you stepped through the code with F8.?

Have you tried my first question re your quoted text?

And yes, your logic is backwards at the moment.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 06:48
Joined
May 7, 2009
Messages
12,050
you have already displayed it in another control?
On the subform footer is a calculated field called "SumQuantityReceived" which calculates fine and displays on the main form in another field.
then on current event of your form:

...
Me.tglReceiveAll.Enabled = (Val(Me.theTextbox & "") > 0)

''' theTextbox is the textbox on the MainForm.
 

GK in the UK

Registered User.
Local time
Today, 23:48
Joined
Dec 20, 2017
Messages
232
You say your evaluation code for enabling the button is in Current Event of the main form. It's only getting evaluated once. Pop a MsgBox within the lines of your evaluation code and see how often it fires. You also say tglReceiveAll is on the main form.

The field on the main form with data source set to the footer field, gets evaluated 'live', and will update on a change of SumItemsReceived but your code for enabling the button has to be called, and as it is, it isn't getting called when SumQuantityReceived changes.
 

Micron

AWF VIP
Local time
Today, 18:48
Joined
Oct 20, 2018
Messages
3,472
That said, all the debugging usually refers to when you encounter errors but in this case may code runs without any.
I don't agree with that at all. A software bug is anything that causes an unexpected result. It doesn't require that an error is raised.

which still points back to it being a field that is calculated on the form as the problem?
As I already mentioned, I was able to retrieve the value property of a calculated control.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:48
Joined
Oct 29, 2018
Messages
14,594
Thanks for this, that’s a great site. Had a read through overnight so hopefully with a bit of practice that should really help things.

That said, all the debugging usually refers to when you encounter errors but in this case may code runs without any. All that is happening is that it continually follows the ElseIf =0 path even when that calculated field is definitely not 0. I display the value of the calculated field that originates in the subform footer in another field that references it in the control source on the main form so I can monitor it’s value and despite it definitely being >0 it still won’t follow the other IF path.

If I run the code referencing to a local field in my main form that’s value is not calculated and comes from the form query then the code works as expected based on its value which still points back to it being a field that is calculated on the form as the problem? Am I missing something? Thanks for your help
Hi. I sent you that link so you can learn how to "step through" your code. You don't need an error to happen to step through your code.
 

Oreynolds

Member
Local time
Today, 23:48
Joined
Apr 11, 2020
Messages
110
Ok, thanks for everybody's input. To try and simplfy things and identify the source of the issue I now have the following:

Form: Purchase Orders
Subform: Purchase Order Subform
Text box in footer of subform: "SumItemsReceived" with Control Source of: =Sum([QuantityReceived])
Text box on main form: "txtTotalReceived" with Control Source of: =[Purchase Order Subform].Form!SumItemsReceived
Text box on main form: "txtTest"

In the main form Current event I have the following code:

Code:
Private Sub Form_Current()

If Me.txtTotalReceived > 0 Then
Me.tglReceiveAll.Enabled = False
Me.txtTest = "NO"
ElseIf Me.txtTotalReceived = 0 Then
Me.tglReceiveAll.Enabled = True
Me.txtTest = "YES"
End If

End Sub

The text box "txtTotalReceived" displays the correct value of items received and changes as the user inputs what items on the purchase order have been received so from a field point of view everything is working fine.

Yet whatever the value shown in the field, i.e. 0 or >0 the code follows the ElseIf route every time and enables the cmd button and puts a 'YES' in the field "txtTest"

I appreciate all the comments on debugging etc but surely access is just not reading the value of this calculated field and therefore the code is seeing it as a 0 every time?

Sorry if i'm being dumb or missing something obvious here....?

1588435408788.png


1588435433556.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:48
Joined
Sep 21, 2011
Messages
7,900
Well I am stumped. :unsure:
I do not reckon you even need the elseif, you could do it just as well as an else.? However that is not going to make a difference (I would have thought)
All I do know is that Access (or any other program) does not just decide not to read a value when told to.:)

This is going to be what I call a 'silly' error. It will be so obvious when you get to the bottom of it, but until then it is frustrating.

All I can suggest now is to leave enough in there to test and upload a test DB. Someone will get to the bottom of it.
 

Micron

AWF VIP
Local time
Today, 18:48
Joined
Oct 20, 2018
Messages
3,472
If it hasn't been suggested already, why not post a compacted and zipped copy of the db? Pictures aren't all that helpful, and neither is posted code when there's nothing obviously wrong with the syntax. Unless -
it is because your 0 value is a default one, or
the value is not what you've coded for, or
it might be null or "", or
the reference is wrong, or
you should have an Else case rather than just ElseIf, or
...
 

Users who are viewing this thread

Top Bottom