Hi all! 1st time poster here but this site has helped me a lot through the years so thank you to everyone, especially the "regulars"
So on to my issue. I have written code to loop through a continuous subform, check the value of 5 checkboxes in each record and increment a temp variable if they are <> 0. This is being done in the BeforeUpdate event. For any Tmp variable that is > 0, the code should then check a box on the mainform. And, ideally, if the temp var does = 0, and if one of the mainform boxes are checked, it should uncheck it. (This part of the code waas removed to try to get it working).
The reason I am doing this via a loop is because if 1 record has certain boxes unchecked, but other records are checked, the main record should still remain checked. Perhaps there is a better way to do this but this is what I came up with.
The issue I am seeing is that it only seems to be counting values for the record line that was changed. Maybe this "diagram" will help and I'll explain more below it:
So let's say Record 1 had been entered previously. I now enter Record 2 and check the box for "B". What I want, is for the code to loop through all records and if a box on any record is checked it should increment a tmp variable. I have also tried an unbound control on the form in case there are issues with temp variables I'm not aware of.
So, if working correctly, the temp variables TmpA, TmpB and TmpD should = 1. However, it only picks up the most recent change, with TmpD = 1 but TmpA and TmpB each = 0. Well actually the TmpCtrl equals a number = to the number of records in the subform. So if there are 5 records, and "A" is checked in only the most recent, TmpA will = 5 and the rest, regardless if checked or not, will = 0. (Thought this may be a clue as to what the issue is).
The reason I am using tmp variables and a loop like this is because if even 1 box on any record is checked, the corresponding box on the main form should be checked (I know I can get this portion with a simple after update event). The loop is so that I can also uncheck boxes on the main form if a subform record was edited to remove a check box and if not even 1 other record has that same box checked.
i.e. if there are 10 records and 1 has "A" checked then the mainform's "A" should be checked. If the subform's "A" is then unchecked, and none of the other records have an "A" checked, the mainform's "A" should be unchecked.
I've searched forums, tried different ideas and have been battling with this for hours and not getting any closer. Here's is my most recent iteration of the code (relevant bits anyway):
For each of the final If statements, I did have an Else Me.Parent.CtlX = "0", but due to the problem listed above, it was unchecking boxes in the mainform even when other subrecords had the appropriate checkbox.
So to summarize, I'd like to have this code loop through all subform records, if 1 or more checkboxes are ticked, it should tick a corresponding checkbox on the main form OR if a subform checkbox has been unticked, and if no other records have that same control checked, it should uncheck the box on the main form.
Any help is greatly appreciated and I tried to be detailed (maybe too much) but if you need more info please let me know. Thanks in advance.
So on to my issue. I have written code to loop through a continuous subform, check the value of 5 checkboxes in each record and increment a temp variable if they are <> 0. This is being done in the BeforeUpdate event. For any Tmp variable that is > 0, the code should then check a box on the mainform. And, ideally, if the temp var does = 0, and if one of the mainform boxes are checked, it should uncheck it. (This part of the code waas removed to try to get it working).
The reason I am doing this via a loop is because if 1 record has certain boxes unchecked, but other records are checked, the main record should still remain checked. Perhaps there is a better way to do this but this is what I came up with.
The issue I am seeing is that it only seems to be counting values for the record line that was changed. Maybe this "diagram" will help and I'll explain more below it:
Code:
A B C D E
Record 1 x x
----------------------------------------------------
Record 2 x
----------------------------------------------------
So let's say Record 1 had been entered previously. I now enter Record 2 and check the box for "B". What I want, is for the code to loop through all records and if a box on any record is checked it should increment a tmp variable. I have also tried an unbound control on the form in case there are issues with temp variables I'm not aware of.
So, if working correctly, the temp variables TmpA, TmpB and TmpD should = 1. However, it only picks up the most recent change, with TmpD = 1 but TmpA and TmpB each = 0. Well actually the TmpCtrl equals a number = to the number of records in the subform. So if there are 5 records, and "A" is checked in only the most recent, TmpA will = 5 and the rest, regardless if checked or not, will = 0. (Thought this may be a clue as to what the issue is).
The reason I am using tmp variables and a loop like this is because if even 1 box on any record is checked, the corresponding box on the main form should be checked (I know I can get this portion with a simple after update event). The loop is so that I can also uncheck boxes on the main form if a subform record was edited to remove a check box and if not even 1 other record has that same box checked.
i.e. if there are 10 records and 1 has "A" checked then the mainform's "A" should be checked. If the subform's "A" is then unchecked, and none of the other records have an "A" checked, the mainform's "A" should be unchecked.
I've searched forums, tried different ideas and have been battling with this for hours and not getting any closer. Here's is my most recent iteration of the code (relevant bits anyway):
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim TmpA As Integer
Dim TmpB As Integer
Dim TmpC As Integer
Dim TmpD As Integer
Dim TmpE As Integer
TmpA = 0
TmpB = 0
TmpC = 0
TmpD = 0
TmpE = 0
If Me.RecordsetClone.RecordCount <> 0 Then
Set rs = Me.RecordsetClone
With rs
.MoveLast
.MoveFirst
Do While Not .EOF
If CtlA <> "0" Then
TmpA = Tmp A + 1
End If
If CtlB <> "0" Then
TmpB = TmpB + 1
End If
If CtlC<> "0" Then
TmpC = TmpC + 1
End If
If CtlD <> "0" Then
TmpD = TmpD + 1
End If
If CtlE <> "0" Then
Tmp E = TmpE + 1
End If
.MoveNext
Loop
End With
If TmpA <> 0 Then
Me.Parent.CtlA = "-1"
End If
If Me.Parent.CtlB<> 0 Then
Me.Parent.CtlB = "-1"
End If
If Me.Parent.Ctl C <> 0 Then
Me.Parent.CtlC = "-1"
End If
If Me.Parent.CtlD <> 0 Then
Me.Parent.CtlD = "-1"
End If
If Me.Parent.CtlE <> 0 Then
Me.Parent.CtlE = "-1"
End If
Set rs = Nothing
End If
End Sub
For each of the final If statements, I did have an Else Me.Parent.CtlX = "0", but due to the problem listed above, it was unchecking boxes in the mainform even when other subrecords had the appropriate checkbox.
So to summarize, I'd like to have this code loop through all subform records, if 1 or more checkboxes are ticked, it should tick a corresponding checkbox on the main form OR if a subform checkbox has been unticked, and if no other records have that same control checked, it should uncheck the box on the main form.
Any help is greatly appreciated and I tried to be detailed (maybe too much) but if you need more info please let me know. Thanks in advance.