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

[GK in the UK]

Answered in post #15 I think.
Current Event of main form does not fire when you edit sub form records.
You don't need any more debugging than putting a msgbox in main form Current Event, see if it pops up when you change sub form value.
Your if..then gets called on first load of your Purchase Order and never again.

txtTotalReceived WILL change.

 

[Gasman]

Well I was believing the o/p that the code was going through the elseif path.

 

[theDBguy]

Answered in post #15 I think.
Current Event of main form does not fire when you edit sub form records.
You don't need any more debugging than putting a msgbox in main form Current Event, see if it pops up when you change sub form value.
Your if..then gets called on first load of your Purchase Order and never again.

txtTotalReceived WILL change.

Well I was believing the o/p that the code was going through the elseif path.

And if the OP was stepping through the code, then they would definitely know when the code is firing.

 

[Oreynolds]

Thanks guys, I have stripped the DB down, please find copy attached....!

Fingers crossed someone can see what I am doing wrong, thanks again.

 

[Oreynolds]

Just to be clear I do understand that the code will only trigger on an on current event when the user moves to the record, this is how i need it to suit our procedures, thanks

 

[Oreynolds]

Sorry, dB was too large!! Take two..........

 

[Gasman]

Version is too recent for me, sorry.

 

[Oreynolds]

Version is too recent for me, sorry.

Sorry, didn't consider that, is this version any good?

 

[zeroaccess]

I just had a look at your database.

First, this is a toggle button and not a command button, so that might change how people reading this understand the problem.

Now, what I'm seeing is the button displaying "Set to Received in Full". Pressing it sets all items Quantity Received in the subform to 1. The toggle button then changes to say "Set to none Received". Pressing it again changes all items Quantity Received to 0.

However, if I close and reopen the form, if all items are already 1, the toggle button still displays the original "Set to Received in Full". So I believe your problem is that you want the button to update the text the user sees, however functionally it is working as expected. Is that correct?

Before I look at the code, can you answer if quantity received for an item can ever be anything other than 1 or 0? Can it be 2, 3, etc?

 

[zeroaccess]

You could make it so that the caption of the toggle button is based on your subform footer count, but that doesn't appear how it is currently set up. It appears that the caption is based on whether or not the button is pressed (true or false). Here is the current code for the toggle button:

Private Sub tglReceiveAll_Click()

Dim rs As DAO.Recordset
Dim db As DAO.Database

DoCmd.RunCommand acCmdSaveRecord
    
    If Me.tglReceiveAll = True Then
        Me.tglReceiveAll.Caption = "Set to none Received"

        Set rs = Forms![Purchase Orders]![Purchase Order Subform].Form.Recordset.Clone()

        rs.MoveLast
        rs.MoveFirst
            Do Until rs.EOF
                rs.Edit
                    rs!QuantityReceived = rs!Quantity
                rs.Update
                rs.MoveNext
            Loop
        Set rs = Nothing
        Me.Form.Refresh
    Else
        Me.tglReceiveAll.Caption = "Set to Received in Full"
    
        Set rs = Forms![Purchase Orders]![Purchase Order Subform].Form.Recordset.Clone()
        
        rs.MoveLast
        rs.MoveFirst
            Do Until rs.EOF
                rs.Edit
                    
                    rs!QuantityReceived = 0
                rs.Update
                rs.MoveNext
            Loop
        Set rs = Nothing
        
        Me.Form.Refresh
    End If
End Sub

 

[Gasman]

OK, I quickly set a breakpoint at that If statement and it does go through the correct path.? so you are checking the correct controls?

Once you get this working I'd advise that you set all the rest in the current as you go through the controls in one pass. At present you are going through all the controls for each setting of acontrol.

 

[Oreynolds]

I just had a look at your database.

First, this is a toggle button and not a command button, so that might change how people reading this understand the problem.

Now, what I'm seeing is the button displaying "Set to Received in Full". Pressing it sets all items Quantity Received in the subform to 1. The toggle button then changes to say "Set to none Received". Pressing it again changes all items Quantity Received to 0.

However, if I close and reopen the form, if all items are already 1, the toggle button still displays the original "Set to Received in Full". So I believe your problem is that you want the button to update the text the user sees, however functionally it is working as expected. Is that correct?

Before I look at the code, can you answer if quantity received for an item can ever be anything other than 1 or 0? Can it be 2, 3, etc?

Not exactly. As you have seen the button works exactly as required

I just had a look at your database.

First, this is a toggle button and not a command button, so that might change how people reading this understand the problem.

Now, what I'm seeing is the button displaying "Set to Received in Full". Pressing it sets all items Quantity Received in the subform to 1. The toggle button then changes to say "Set to none Received". Pressing it again changes all items Quantity Received to 0.

However, if I close and reopen the form, if all items are already 1, the toggle button still displays the original "Set to Received in Full". So I believe your problem is that you want the button to update the text the user sees, however functionally it is working as expected. Is that correct?

Before I look at the code, can you answer if quantity received for an item can ever be anything other than 1 or 0? Can it be 2, 3, etc?

Not exactly. As you noted the button works exactly as required and has been in use for a while, however the users have requested that this button becomes inactive if the

I just had a look at your database.

First, this is a toggle button and not a command button, so that might change how people reading this understand the problem.

Now, what I'm seeing is the button displaying "Set to Received in Full". Pressing it sets all items Quantity Received in the subform to 1. The toggle button then changes to say "Set to none Received". Pressing it again changes all items Quantity Received to 0.

However, if I close and reopen the form, if all items are already 1, the toggle button still displays the original "Set to Received in Full". So I believe your problem is that you want the button to update the text the user sees, however functionally it is working as expected. Is that correct?

Before I look at the code, can you answer if quantity received for an item can ever be anything other than 1 or 0? Can it be 2, 3, etc?

Not exactly. The button as you noted works fine and has been used for some time. The button is essentially a shortcut for the users to set all products in the PO to fully received, however in the event that it is part received order they would just edit each sub record accordingly. The users have now requested that is button is disabled on the next form load if it has been part received, i.e. the no of items received is >0, does this explain?

 

[Gasman]

However when I move to record 2 that has no quantity, the code follows the correct path, but the main form txtTotalItems does not get refreshed, yet is zero when going through the code and setting txtTest correctly.

Plus record 1 only has a sum quantity of 2 yet shows 3 on main form, yet is only 2 when going through the code.?

You really need to walk through this code block by block, if not line by line. You are more aware of how it is meant to work than I am.

HTH

 

[zeroaccess]

I just stepped through the code and it appears you have an infinite loop here?

 

[Oreynolds]

OK, I quickly set a breakpoint at that If statement and it does go through the correct path.? so you are checking the correct controls?

Once you get this working I'd advise that you set all the rest in the current as you go through the controls in one pass. At present you are going through all the controls for each setting of acontrol.

View attachment 81746

I just don't understand how you have got that text box to show NO!! Whatever data I have 0 or >0 it still says YES, see following screenshots just taken:

>0

=0

 

[zeroaccess]

Not exactly. The button as you noted works fine and has been used for some time. The button is essentially a shortcut for the users to set all products in the PO to fully received, however in the event that it is part received order they would just edit each sub record accordingly. The users have now requested that is button is disabled on the next form load if it has been part received, i.e. the no of items received is >0, does this explain?

Check it again. If you open an order that has all 1s, the caption is not displaying properly because it is based not on the total quantity, but on whether or not the button has been pressed.

I'll keep thinking about this but for now my gut tells me there is other code conflicting with your enable/disable.

 

[Gasman]

You are checking Received control, not the one you showed earlier?

 

[Oreynolds]

Check it again. If you open an order that has all 1s, the caption is not displaying properly because it is based not on the total quantity, but on whether or not the button has been pressed.

I'll keep thinking about this but for now my gut tells me there is other code conflicting with your enable/disable.

Thanks for your input. To test this I have just deleted all other code from this form apart form this one IF statement and still getting the same results.....!!!

losing more hair by the hour!!

 

[Oreynolds]

You are checking Received control, not the one you showed earlier?

View attachment 81751

Sorry, misleading maybe. Received is just the Caption, field name is as previously lissted:

 

[zeroaccess]

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...