Need main form checkbox to check based on sub-form checkboxes (1 Viewer)

The Rev

Registered User
Joined
Jan 15, 2003
Messages
48
Evening!. I am working on my main form. I have a main form with a checkbox labeled "Sub_Family_Satisfied". The sub-form is a single Form with a checkbox on each record for "Requirement_Satisfied". I need the "Sub_Family_Satisfied" checkbox in the main form to check when all of the filtered records in the sub-form's "Requirement_Satisfied" checkboxes are checked.

for instance, My Sub Family form's record is 3.6 and my sub-form's records are 3.6.1, 3.6.2, 3.6.3, and 3.6.4. I need the 3.6's "Sub_Family_Satisfied" checkbox to check only after all 4 records in the sub-form's "Requirement_Satisfied" checkboxes are checked.

Thanks!!

The Rev
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,749
What will drive this decision? A button? Clicking any one of the subform checks involved in the decision?

Is the main form check bound so that once this is set, that fact doesn't get lost when one navigates away from the main record?

Pardon the question, but isn't this redundant because all one has to do is look at the values of subform checks as a group?
The sub-form is a single Form with a checkbox on each record for "Requirement_Satisfied"
I don't understand this. A 'single' form view displays only one record. You cannot have both single and an "each record" situation at the same time.

This would be far easier if you simply checked the box manually but isn't impossible. I'd probably assign a single function to each of the checks AfterUpdate event on the subform and loop through that group. If any of them meet the criteria, perform the main form update as required. It would make it easier if the subform checks had a Tag property value so that the only form controls involved in such a poll are those particular ones.
 

The Rev

Registered User
Joined
Jan 15, 2003
Messages
48
What will drive this decision? A button? Clicking any one of the subform checks involved in the decision?
The checkbox in the subform. If all of the filtered records get that box manually checked, then the main form's checkbox will get checked.

Is the main form check bound so that once this is set, that fact doesn't get lost when one navigates away from the main record?
Yep. Manually setting it right now.

Pardon the question, but isn't this redundant because all one has to do is look at the values of subform checks as a group?
It's a business requirement. The Lead doesn't want to see the subform checkboxes, but they are all required in order to mark the overall sub-family as compliant. The lead reports only the sub-family compliant stats to the customer.

I don't understand this. A 'single' form view displays only one record. You cannot have both single and an "each record" situation at the same time.
Right. There's my problem. Each individual requirement is its own entity and has several questions that must be answered in order to be considered compliant. But they tie back to the Sub-Family they relate to. For instance, checking door access processes would fall under the sub-family of Physical Security, not Maintenance.

This would be far easier if you simply checked the box manually but isn't impossible.
That's what I am dong now, with some simple logic on the "On Click" to make sure the Compliant and Not Compliant boxes can't both be checked.

I'd probably assign a single function to each of the checks AfterUpdate event on the subform and loop through that group. If any of them meet the criteria, perform the main form update as required. It would make it easier if the subform checks had a Tag property value so that the only form controls involved in such a poll are those particular ones.
Interesting. I may give that a shot today. Thanks for taking the time!


The Rev
 

Uncle Gizmo

Nifty Access Guy
Staff member
Joined
Jul 9, 2003
Messages
10,845
I demonstrate how to loop through a set of check boxes with VBA code on my website here:-

Loop Through a Set of Controls ...

If there's a chance that any of the check-boxes in the sub-form should become unchecked, and you go back to the record at a later date, then you could have misleading information. The check box on (the main form) could be checked wrongly. If that's a possibility, you could consider running the code for checking the subform checkboxes from the on current event on the main form.
 

The Rev

Registered User
Joined
Jan 15, 2003
Messages
48
So, I got it to where if I check the checkbox on the subform, it loops through the recordset. For testing purposed, I have it pop up a message box for the status of each checkbox in the record. It reads properly. What it doesn't do is execute the loop again unless I navigate to a different record in my main form and then go back to the record and click it again. How can I get it to execute every time I click the checkbox? I've tried putting the loop in the On Click event for the subform checkbox, but it doesn't re-execute when I click.
 

MajP

The Dude or uh, His Dudeness, or uh, El Duderino
Joined
May 21, 2018
Messages
2,362
Code:
If I understand your issue, then Gizmo and Micron are confusing you because as far as I can tell there is one yes/no field and not multiple.

You have a main form and the child records are related by some field. I will assume it is levelID. So 3.6.1 is related to 3.6 by a levelID.

In the subform you may have fields like
LevelID
ParentLevelID
Selected


3.6.1 3.6 True
3.6.2 3.6 False
3.6.3 3.6 True



So there is no looping of checkboxes. You need to figure out how many child records and how many are selected.

Code:
Public Function ChildRecords(ParentID as string) as integer
  ChildRecords = dcount("*", "qryOrTableName","ParentLevelID = " & ParentID
end function
Public Function SelectedChildRecords(ParentID as string) as integer
  ChildRecords = dcount("*", "qryOrTableName","ParentLevelID = " & ParentID & " AND Selected = True"
end function
Then In the after update of the selected field
Code:
If ChildRecords(me.parentID) = SelectedChildRecords(me.parentID) then me.parent.someCompletedfield = true
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,016
I was thinking of a DCount on checkboxes not set for the relevant key?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Joined
Jul 9, 2003
Messages
10,845
Code:
If I understand your issue, then Gizmo and Micron are confusing you because as far as I can tell there is one yes/no field and not multiple.
I think you would be more correct in saying I am confused! Nothing unusual there!
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,749
I have probably misunderstood as noted, but can you blame anyone?
the subform checkboxes
all 4 records in the sub-form's "Requirement_Satisfied" checkboxes
I guess there is a subform with one record and you need to cycle through all four records to make something happen. Does anyone else think it's unusual to not put all four records in one subform list?
 

The Rev

Registered User
Joined
Jan 15, 2003
Messages
48
Ok. I got it figured out. There are 3 separate states for the Requirement Sat/Not Sat checkboxes . Here's the code I crafted, which is working as I need. I put it on the After Update on the Subform control:

Code:
Set rs = Forms!frm_Families_and_Subfamilies!SubFrm_Requirements.Form.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF
    cbothereval = rs!Requirement_Other_Than_Satisfied
    cbeval = rs!Requirement_Satisfied
    NotDoneYet = cbeval & " " & cbothereval
        If NotDoneYet = "False False" Then
           Forms!frm_Families_and_Subfamilies.Sub_Family_Satisfied = False
           Forms!frm_Families_and_Subfamilies.Sub_Family_Not_Satisfied = False
           Exit Do
        End If
    If cbothereval = "True" Then
        Forms!frm_Families_and_Subfamilies.Sub_Family_Satisfied = False
        Forms!frm_Families_and_Subfamilies.Sub_Family_Not_Satisfied = True
        Exit Do
    End If
    If cbeval = "False" Then
          Forms!frm_Families_and_Subfamilies.Sub_Family_Satisfied = False
          Forms!frm_Families_and_Subfamilies.Sub_Family_Not_Satisfied = False
          Exit Do
    End If
    rs.MoveNext
    Forms!frm_Families_and_Subfamilies.Sub_Family_Satisfied = True
    Forms!frm_Families_and_Subfamilies.Sub_Family_Not_Satisfied = False
Loop
That way, if any of the records in the subform are NEITHER Sat or Unsat (Hasn't been looked at yet) the main form's Sat and Unsat checkboxes will both set to False. If ANY of the records have the Unsat checkbox checked, the main form's UNSAT checkbox will set, and only if ALL of the records SAT checkboxes are set will the main form Sat checkbox set.

Thanks for the direction and sorry I was having difficulty explaining myself.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Joined
Jul 9, 2003
Messages
10,845
sorry I was having difficulty explaining myself.
I find the process of explaining to someone else often helps me resolve the problem Myself. I fact, when I see a question which isn't quite clear, I usually ask more questions to ferret out the the real question.

There are several reasons for this approach, one being it helps the OP Focus on the question and may nudge them into solving it themselves. Another is it's hard to gauge the OP's skill level and asking more questions can often help determine the nature of the help you need to provide. And for me personally, I'm here to help people learn MS Access. I do like to filter out the people that are obviously just asking a question to receive an answer ready made and done for them.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,586
That looks vaguely like something we did at the Navy Enterprise Data Center for our semi-annual site security audit. If I recall correctly, the headers we used formed "proper" trees in that if we were under header 3.6, the relevant items would be 3.6.1, 3.6.2, 3.6.3, etc but NEVER anything for 3.5.1 or 3.7.2 or any other numbers at any time. Instead, they REPEATED the item so that if 3.5.1 were relevant to header 3.6, then it would be cross-posted under its original number AND another number.

If that is the case for you, then if your headings and sub-headings are strings like "3.6" and "3.6.1" and so on, and your interest is on a form, you might do this:

Code:
ThisHeader = Me.HeaderID
NumItems = DCount( "[*]", "[ComplianceList]", "HeaderID LIKE '" & ThisHeader & ".*" )
AllChecked = DCount( "[Checked]", "[ComplianceList]", "[HeaderID] LIKE '" & ThisHeader & ".*' AND [Checked]=TRUE" )
Me.Checked = ( NumItems = AllChecked )
The sequence ".*" prevents the domain aggregates from counting the record you are using as your current point of reference since it would NOT end with a dot. But the LIKE operator would find all of the relevant records. You can do this in the Form_Current routine for the form.

Note also that this would catch cases where 3.6.1 was unchecked because 3.6.1.1 was unchecked, since the LIKE would catch that, too.
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,534
1. I'm pretty sure you have a design flaw in your schema if you are using ONE field to represent a hierarchy of levels. Fixing this will simplify the rest of your process.
2. Storing a piece of data that can be derived another way violates normal forms and is not recommended. If you update this calculated value in the wrong place, it can easily get out of sync and I believe that will happen with your solution although you only posted part of the code so I can't tell where this code is running. If it isn't running in the AfterUpdate event of the subform, the mainform is NOT being updated at the correct point. And even if your code is running in the correct subform event, if this data can be updated ANY OTHER way, your process will fail because the duplicated value in the main form table will not accurately reflect the multiple values from the subform's table.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom