Loop Through Records, Check Values of Check Boxes and Implement Code (1 Viewer)

[DaLoaf]

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:

                      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):

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.

 

[Mark_]

It sounds like you are doing too much work.
I would NOT be doing this in the subform. Each instance in a continuous form can ONLY see that record.

Rather, I would use something like DSum in your man form to check.

This means that your parent would have 5 blocks, each looking about like this;

Me.CheckBoxA = False
IF DSum("[CheckboxA]","ChildTable","[ChildID] = " & ParentID) Then Me.CheckBoxA = True

 

[The_Doc_Man]

Before I take a deep dive into the shallow end of this pool, I've got a couple of questions.

First, are the checkbox controls on the main form bound and in some way related to the checkbox controls on the sub form?

Second, The description of what is supposed to happen for unchecked cases seemed a bit vague. Particularly if I UNcheck the control on the main form, what is supposed to happen?

I have an idea on how one might do this, but I need a better understanding of when things are or are not checked.

 

[gemma-the-husky]

in general, having to check fields against other fields, and especially other rows, to determine appropriate values seems to me to imply that the data is not properly normalised. the idea is that fields in a record should only be dependent on the PK of that record.


maybe when you enter values in row 2, you need to take into account the current setting in row 1, but you should find a way to structure your data and your form behaviour so that you don't have to go back and reconsider all the data related to the prime record.

 

[MajP]

Wow, that is truly overly complicated.

The subform is based on some table. I assume that table has a foreign key relating to the main form.

TblSubForm

  A ' as boolean
  B
  C
  D
  someFK ' relating to main table

So your data looks like this

A	B	C	D	Some_FK
True	False	False	False	1
False	True	False	False	1
False	False	True	False	1
False	False	False	True	1
False	True	False	False	2
False	False	True	False	2
False	True	False	False	2
False	False	True	False	2

Do a group by query

SELECT Min(tbldata.a) AS MinOfA, 
       Min(tbldata.b) AS MinOfB, 
       Min(tbldata.c) AS MinOfC, 
       Min(tbldata.d) AS MinOfD, 
       tbldata.some_fk 
FROM   tbldata 
GROUP  BY tbldata.some_fk;

MinOfA	MinOfB	MinOfC	MinOfD	Some_FK
-1	-1	-1	-1	1
0	-1	-1	0	2

So at anytime the main form can update its checkboxes simply by doing a dlookup of the above query

me.CheckA = nz(dlookup("MinOfA","MyQuery","Some_FK = " & me.PK),0)

 

[DaLoaf]

Thanks for all the responses.

Yeah I'm self taught on VBA so sometimes I miss the easier solutions. MajP, what, you don't like my Rube Goldberg machine of coding? ;-)

Although I've been hesitant to use Dsum and Dlookup (for performance reasons), those do seem to be a much easier solution.

As far as the structure being an issue, it's not, but perhaps it would be easier to understand if I told you all that controls A-E are US States (which I renamed for this post).

So basically all I'm looking for is that if I check a state, let's say New York, in any sub-record, I just want the main record to reflect NY as well. And if NY is unchecked in any subrecord, as long as it's not checked in another subrecord, it would uncheck on the main form.

I think the Dsum/Dlookup route is going to be the best approach for this and I appreciate everyone's help.

 

[Mark_]

So basically all I'm looking for is that if I check a state, let's say New York, in any sub-record, I just want the main record to reflect NY as well.

Is there a good reason to save a calculated value?

If you are dealing with tens of thousands of child records per parent and tens of thousands of parent records, this may allow for a performance increase. If you are dealing with tens of child records, there really is no reason to store a calculated value as you won't notice the performance difference.

 

[MajP]

Although I've been hesitant to use Dsum and Dlookup (for performance reasons)

That is funny, so you built a your Rude Golberg machine to save .15 seconds and in trutch your looping code would be real slow even against 50 dlookups. If you are stuck with this design (more later) then writing code would be easier (not necessarily more efficient), but you will still start with the query to do the heavy lifting.

qrySelections

MinOfAlaska	MinOfAlabama	MinOfArkansas	MinOfNextSate	Some_FK
-1	-1	-1	-1	1
0	-1	-1	0	2

I am assuming the main table has fields of state names which are boolean and the subform has the same.

dim strSql as string
dim rs as dao.recordset
Dim fld as dao.field
dim stateName as string
dim PK as long
strSql = "Select * from qrySelected where Some_FK = " Me.Pk
set rs = Currentdb.openrecordset (strSql)
'It will return a single record
for each fld in rs.fields
  if left(fld.name,5) = "minOf"  "assum if the name is Alabama in the main table the query is minOfAlabama
    stateName = right(fld.name,len(fld.name)-5)   'MinOfAlabama returns Alabama
    strSql = "Update tblMain set " & stateName & " = " & fld.value " WHERE PK = " & me.PK
    currentDB.execute StrSql
  end if
next fld

I will disagree with you that this is a normalized DB. 50 fields representing states is not normalized and not efficient. If you want to redesign provide more details and we can help

Using a single update query would be faster than the code (likely fractions of seconds), but with 50 or more fields to update would be painful to write. The code is simple (short) if you can take advantage of consistent names. If not, then it still would not be so hard to write.

 

[DaLoaf]

Mark,

I'm not 100% clear on what you're asking but both the Main Record and Sub-records store State values. There's nothing that really needs to be calculated about them, they are simply checkboxes bound to a single state.

The main record State boxes tell the user, at a glance, which states are involved, without them having to look through dozens of subrecords. What I'm trying to do with the automatic box checking is just to (1) ensure a state doesn't get missed from being checked on the main form and (2) ensure that if a state is removed from all subrecords, that it is also removed from the main record.

As I mentioned in my OP, if it was just #1, I could simply do an after_update event on the subforms state check boxes. However, it is the unchecking that is tripping me up as I can't simply say If ChkNY = "0", because ChkNY in every record has to = "0" before it should be unchecked from the main box.

I think your other post suggesting Dsum or the other post suggesting Dlookup will do the trick but if you have an alternate solution I'm open to hearing it.

 

[DaLoaf]

I will disagree with you that this is a normalized DB. 50 fields representing states is not normalized and not efficient. If you want to redesign provide more details and we can help

I would have approached this differently but there are only 5 states. BUT each record could affect between 1 and 5 states. So it's not necessarily 1 state per record. (oh how easy it would be if it were)

Besides a multiselect combobox (which I won't use), or a regular combobox listing every possible combination (which the employer doesn't want), is there another solution to storing a variable number of states per record that I'm missing? The only thing I can think of is another connected table just to store state values but I admit I am far from being an expert.

I'm always open to new things as long as they fit within what my employer wants.

 

[Mark_]

If your goal is to show the user at a glance what states are used, then use an unbound control that is set based on a DSUM() instead of saving the value in a record.

If you save it in a record, what happens when you report on said record but some how a child record was updated without the main one being updated?

The only times I've ever needed to save a value that is calculated off of child records is when performance requirements dictate it. I know others have save them when regulatory requirements dictated it. In your case, if you can avoid possibly having a wrong value by simply updating an unbound field and NOT saving the value in a parent, you avoid the chance someone will do something silly (like updating the tables directly) and your parents reflect the wrong value.

 

[MajP]

Besides a multiselect combobox (which I won't use), or a regular combobox listing every possible combination (which the employer doesn't want), is there another solution to storing a variable number of states per record that I'm missing?

If you can describe the design and not a proposed solution we can give a better answer. What is the main table and what are the records.

Most likely I have a main table, and I want child records which are the currently selected states. I add and subtract from this table. However, I am not sure since I do not understand the other child table that you propose and how that relates. Based on the child records you described, I am not sure why you need these values in the main table. The query I produced shows the selected states based on the child records. Need some more explanation, but from what you show now I do not see the need for states in the main table.

 

[DaLoaf]

If your goal is to show the user at a glance what states are used, then use an unbound control

The problem is this data is shown on multiple forms that vary by user access-level as well as reports and I do not want to have to have it calculate over and over. Additionally, for some high-level reports, only the data from the main table is used. With the values being captured in the main table, the child tables do not need to be bothered with at all.

If you save it in a record, what happens when you report on said record but some how a child record was updated without the main one being updated?

Forgive my ignorance here but why would that happen? I'm looking for code that works every time, and leaves no chance of error. To be honest if my choice was code that didn't always update the main record, I would just go back to checking those boxes by hand.

The only times I've ever needed to save a value that is calculated

I wouldn't really call this a "calculated" field or value. It's just basically "if any of these boxes over here are checked, then this box here is checked also". However we may be getting into semantics.

you avoid the chance someone will do something silly (like updating the tables directly) and your parents reflect the wrong value.

Luckily this isn't a problem as I am the only with with access to the tables. We also do server backups for the back_end file multiple times per day so if someone really went our of their way and got into a part of the system they shouldn't be in and accessed the back_end file to get to the tables, and changed the data, we would restore a backup and that person would be having some issues. haha

 

[isladogs]

Last post was moderated. Posting this to trigger email notifications

 

[DaLoaf]

MajP,

Perhaps my response to Mark sheds more light on why I'm storing those values in the main table?

I can't really get into the details of the database, but it is not a 1:1 ratio between subrecords and states.

The main record has the overall information for a single "Topic". The reason I store the state data there as well is mentioned in my last reply to Mark.

The subrecords have more specified information related to the "Topic" and each subrecord could affect between 1 to 5 states. There could be any number of subrecords per main record.

 

[Mark_]

OK, data normalization 101...
If you have one of something, and only one, it goes into a table.
If you have none or more (but not exactly one) then put it in a child.

For normalization you would have the "States" as child records to your current "Child". This avoids having to rebuild your tables, forms, and reports when you discover you need to add a new "State".

The reason you normally don't have a FORM maintain calculated values stored in a parent (and what states are being used is calculated based on information in your child records since you do NOT have a user entering it) is because you cannot always ensure that FORM will be used to update your parent.

Real world example that pops up far to often is when you import data. These records get appended to your tables, but unless you go into EACH parent to have them updated by your form, you may miss one. The alternate is to replicate the same code and hope it is done right in every instance you use it.

For this specific application this may seem to be overkill. It is how ever a standard practice as it does what you requested;

I'm looking for code that works every time, and leaves no chance of error.

It WILL give the correct answer every time, no chance for error, based off of the data present at that time.

 

[MajP]

MajP,

Perhaps my response to Mark sheds more light on why I'm storing those values in the main table? 

I can't really get into the details of the database, but it is not a 1:1 ratio between subrecords and states. 

The main record has the overall information for a single "Topic". The reason I store the state data there as well is mentioned in my last reply to Mark. 

The subrecords have more specified information related to the "Topic" and each subrecord could affect between 1 to 5 states. There could be any number of subrecords per main record

There are times when I store values in the main table based off calculations on the child records. Normally because the query is too resource expensive to run in a query dynamically every time you want to see the results. I try to avoid this because it requires you to really ensure there is no way to get out of synch and ensure you run an update when you need current data, but sometimes it is the most efficient way.

So I can only guess at your structure and use. From the simple example you described and all your responses, I still think there is no need to store the data in the main table. I think this can be done in a query of the child records. As you initially described the problem the qrySelected could be on the main form and joined to the main table. Then based on the selections in the subform it would show the overall selected or not. It would be dynamic, fast, and always correct. I just do not have enough info to say why that is not a proper solution. If not all the original code would be simple and near instantaneous.

 

[MajP]

A B C D E
Record 1 x x
----------------------------------------------------
Record 2 x
----------------------------------------------------
Record N...

I guess my point is in any query on the main form I can show for the parent record that A, C, D are selected with zero code and just a query. Without storing that info in the parent. This can be done all the time for any report, form, or query whenever I want to see it. I just do not have enough info to be convinced otherwise.

 

[DaLoaf]

Thank you both for your responses.

Mark: At this point since I am sure that we will never add other states, I'm just going to leave it. I do understand what you are saying, and will take that into consideration in the future, but to change it at this point would mean doing exactly what you said "having to rebuild tables, forms, and reports".

MajP: Data is stored in the main table because some reports and 1 or 2 forms are based solely on the main table data, so I'd have to run queries and pull the child table data into it when I don't currently have to.

The scope of some of this is beyond my skill level. I was honestly just looking for a "simple" way to make sure that when I enter sub-records that I don't forget to check the corresponding state box on the main record. All without having to majorly restructure the DB. At this point however it seems like it'll be easier for me to just continue clicking those boxes manually.

Thanks again for everyone's input.

 

[MajP]

At this point however it seems like it'll be easier for me to just continue clicking those boxes manually.

I do not know about. I think I showed some pretty easy solutions that require no loops

If these are my states in the main table with a PK and a field for 5 states

ID	Alabama	Alaska	Arkansas	California
1				
2				
3

and child records with selection are

ID_FK	Alabama	Alaska	Arkansas	California
1	Yes	No	Yes	No
1	No	Yes	No	No
1	No	No	No	Yes
2	No	Yes	No	No
2	Yes	No	No	No
3	No	Yes	No	No
3	Yes	Yes	No	No

qryStateSelected returns

ID_FK	MinOfAlabama	MinOfAlaska	MinOfArkansas	MinOfCalifornia
1	Yes	Yes	Yes	Yes
2	Yes	Yes	No	No
3	Yes	Yes	No	No

You need very little code to then update the parent table based on qryStateSelected because those values are your answers. Can be done with 5 dlookups or a single query. Probably need about 5 lines of code.