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

[Oreynolds]

My $0.02 is that this would be easier with two command buttons, but I understand wanting to consolidate them. Actually, it could probably still be done with just one command button. Worth a try...

I can get the disabling of the button to work easily as I have proved this using another control on the form with a control source from the form query, its only when I use the calculated field as the control that neither the test text field nor the button disable work

 

[Gasman]

All I am doing is moving from one record to another in the main form?

OK, I can replicate your problem, but no idea as to why yet.? If I go onto design mode then got yo form mode it works (several times) back and forth. However I always have a breakpoint at that code. If I remove the breakpoint then you error occurs.

 

[Oreynolds]

All I am doing is moving from one record to another in the main form?

OK, I can replicate your problem, but no idea as to why yet.? If I go onto design mode then got yo form mode it works (several times) back and forth. However I always have a breakpoint at that code. If I remove the breakpoint then you error occurs.

OK, so getting the hang of the debug tool I added an extra line in to see what value access thinks the control field is and even though it displays as 0 it thinks its NULL??

 

[Gasman]

OK, still no idea why, but Me.txtTotalReceived can be empty?
I changed your code to

    If Me.txtTotalReceived > 0 Then
        Me.tglReceiveAll.Enabled = False
        Me.txtTest = "NO"
        Debug.Print Me.txtTest & " " & Me.txtTotalReceived
    Else
        Me.tglReceiveAll.Enabled = True
        Me.txtTest = "YES"
        Debug.Print Me.txtTest & " " & Me.txtTotalReceived
    End If

I put a breakpoint back on the if and went back and forth 3 times. I removed the breakpoint and continuid and got your error.
The debug.prints showed

NO 2
YES 0
NO 2
YES 
YES 
YES 
YES

HTH, that is me for the night. Good luck, hopefully it will give you a clue.

 

[Micron]

The highlighted line is never processed. Move off of it to be sure that it is null.

 

[Oreynolds]

OK, still no idea why, but Me.txtTotalReceived can be empty?
I changed your code to

    If Me.txtTotalReceived > 0 Then
        Me.tglReceiveAll.Enabled = False
        Me.txtTest = "NO"
        Debug.Print Me.txtTest & " " & Me.txtTotalReceived
    Else
        Me.tglReceiveAll.Enabled = True
        Me.txtTest = "YES"
        Debug.Print Me.txtTest & " " & Me.txtTotalReceived
    End If

I put a breakpoint back on the if and went back and forth 3 times. I removed the breakpoint and continuid and got your error.
The debug.prints showed

NO 2
YES 0
NO 2
YES
YES
YES
YES

HTH, that is me for the night. Good luck, hopefully it will give you a clue.

Thanks, I'll persevere, just a quickie, where exactly are you putting the break point as I I put at the start of the IF the code stop prevents me from moving records on the form?

 

[Gasman]

OK, so getting the hang of the debug tool I added an extra line in to see what value access thinks the control field is and even though it displays as 0 it thinks its NULL??

View attachment 81755

View attachment 81756

No, I think it will be Null until set with value. If you process that line you would get a value from Me.txtTotalReceived

 

[Oreynolds]

No, I think it will be Null until set with value. If you process that line you would get a value from Me.txtTotalReceived

Yes, I'm a bit behinds you guys but just sussed that, thanks

Thanks for all your help btw, much appreciated

 

[zeroaccess]

If I change the > 0 to = 0, the button is disabled. This is evidence that the value isn't what we think it is.

 

[Gasman]

Thanks, I'll persevere, just a quickie, where exactly are you putting the break point as I I put at the start of the IF the code stop prevents me from moving records on the form?

I put it on the If statement. Then I use F8 to step through to see what path the code takes. Then when past that I press F5 to run the rest of the code. That is what you need to do, though I think I have proved the code is doing what you want it to do, just that the data is lettig you down.?

Look at my output is post #44

 

[Oreynolds]

I put it on the If statement. Then I use F8 to step through to see what path the code takes. Then when past that I press F5 to run the rest of the code. That is what you need to do, though I think I have proved the code is doing what you want it to do, just that the data is lettig you down.?

Thanks.

Yes just got the same results as you now but only with the break in. It still strikes me that this problem only occurs when using a form field with calculated control source. If you use a control that derived from the query it works fine??? Just annoying I cant use that option......

 

[GK in the UK]

I haven't looked at the DB and I haven't studied the discussion since I last posted.

But I wanted to point out, I've had quite a few issues relying on sub form footer totals. The issue is that the totals don't update in time whilst Access is continuing to process more code. In more than one case, where I actually have a sub form footer total, which forms the basis of what I can do next, I've had to write a SQL query to return the very same total that, in theory, the footer total already provided. I found that I can rely on the query result but not the sub form footer result.

So I've kind of learned that sub form footer totals are fine for just displaying a value but if the very next thing you do after you've updated a sub form value, depends on the total, be very careful.

 

[zeroaccess]

Thanks.

Yes just got the same results as you now but only with the break in. It still strikes me that this problem only occurs when using a form field with calculated control source. If you use a control that derived from the query it works fine??? Just annoying I cant use that option......

I don't think the calculated field is the problem = I changed your code to refer to the subform field directly and it still didn't work.

 

[Oreynolds]

I agree with GK in the UK and was just about to post my suspicion that the calculated field was actually Null at the point the on current code is triggered and only populated just after. This would explain everything right? That’s why it works under debug but not live.

Is there an event that you can use once the form is fully loaded and all fields calculated?

 

[Gasman]

No, somehow the value is being erased so always fails the >0 test. I have not been able to find out why.?

You need to fix Me.OrderID as well, as I could not compile the DB?

 

[zeroaccess]

I'm trying to write an expression in place of the existing calculated field.

But, your PurchaseOrderID is displaying as Purchase Order ID in some places which is causing a lot of confusion when switching views and writing the expression...

By the way, your Purchase Order Details has no primary key. Strongly consider adding a primary key field to uniquely identify each row in the table.

And your code doesn't compile. Do Debug > Compile and figure out the broken references.

 

[zeroaccess]

Use this expression as the source for txtTotalReceived:

=IIf([PurchaseOrderID]>0,DSum("QuantityReceived","Purchase Order Details","[PurchaseOrderID]=" & [PurchaseOrderID]),"")

 

[Oreynolds]

Problem Solved!

Thanks to everyone for their input, much appreciated. After Gasman and Zeroaccess last few comments I tried the following:

1) Moved the code to the subform on current event - This had same results and did NOT work
2) Added the code to a cmd button on the main form - This DID work correctly

On the basis of the above my conclusion is that the values in the calculated fields are not 'available' or 'calculated' at the time that the current code triggers, therefore it returns an incorrect result and always follows the =0 path. The cmd button test works as by the time you can press it the values are calculated - much like the comments from GK in the UK.

Finally I changed the control source on "txtTotalReceived" to the expression provided by by Zeroaccess and it now works fine so I can only deduce this fires slightly earlier or quicker and therefore the value becomes available at the time of the current code trigger....?

Anyway, such a simple problem took so long and so much effort but thanks to everyone involved now solved so much appreciated. Thanks

I have attached another DB copy with all my tests a above if anyone wants to see it.

 

[Oreynolds]

I'm trying to write an expression in place of the existing calculated field.

But, your PurchaseOrderID is displaying as Purchase Order ID in some places which is causing a lot of confusion when switching views and writing the expression...

By the way, your Purchase Order Details has no primary key. Strongly consider adding a primary key field to uniquely identify each row in the table.

And your code doesn't compile. Do Debug > Compile and figure out the broken references.

PS, thanks for your wider comments on other issues, I was aware of a couple but ignoring them in favour of solving this issue first. I'll take a look and take on board your comments re primary key. Thanks

 

[Gasman]

I *thought* this morning that a DoEvents before that code or placing the code at the bottom of the sub might also solve it, but it did not.
I was hoping this might work as you go through all the controls looking at tags multiple times, but alas it still failed.