If and then VBA with multiple subform lines

[Danny2024]

Hi

I have a main form that contains a subform in it which will be used to assign actions

I am trying to get a status box on the main form to update when all the actions in the subform have been classed as complete in the relevant column

It works fine when I just have 1 line, however if I have 2 lines (1 as closed and 1 as open) it will change the status when it should not as the 1st line is closed



I do not want the status to change until all actions have been classed as complete

VBA Below

Private Sub Command155_Click()
If [Tbl_MRB_Actions subform].Form![Action_Status] = "Closed" Then Me.Text152 = "RELEASE PARTS FROM QUALITY CLINIC"
End Sub

Any ideas would be great

Thanks in advance

[Minty]

You would probably need to need to count the open statuses, then do the update.
However this is fraught with difficulties, what if someone adds a status after the existing one is marked as closed? What is someone changes it from closed to open as they made a mistake?

Rather than try and store this by trying to capture every possible update, simply calculate it.

On the form header something like this in an unbound control should do what you want;

=IFF(Dcount("Action_Status","YourActionTable" ,"ActionStatus = 'Open' AND YourPKID = " & [YourPKField]) = 0, "RELEASE PARTS FROM QUALITY CLINIC", "Actions Not all Closed")

Maybe just use the DCount part initially until to get the result you want.

[Danny2024]

Thanks Minty

I never thought of that

Will give it a try and update how I get on

[orange]

@Danny2024,

Can you describe the underlying business supported by your forms in general terms, simple English, and
show us the tables and relationships (jpg) you currentlty have?