DAO Recordset Nested Loops

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • NeoPa
    Recognized Expert Moderator MVP
    • Oct 2006
    • 32633

    #16
    Never a problem my friend :)
    You're absolutely right about the Step part. Each turn through the loop processes two parameters. This leads back to the Stop bit too.
    Stop is like hitting a breakpoint.
    As the only reason for this occurring is a programming error (the code is called incorrectly rather than a problem occurring during normal usage) this is perfect error handling for me. As I design the code I will always be the one who finds this error. This seems to be the most appropriate way to give me the information I need to fix the problem. It's fine to replace it with code you're happier with though. The bread and butter of the procedure is the rest of it anyway.

    Comment

    • BradHodge
      Recognized Expert New Member
      • Apr 2007
      • 166

      #17
      Thanks guys for sticking with me on this one. I messed around with a copy of the basic database today at lunch. I did normalize the tblFamily and moved the Teacher/Sub indication to it's own table. It did not change the results any, but I didn't have much time to fool with it.

      I'll work with the real database when I get home this evening and let you all know how it's going.

      And yes Ade, that is a Nom de Plume that I occasionally use. I just tend to get confused on who I am from day to day :)

      Brad.

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32633

        #18
        I prefer Brad's avatar though ;)

        PS. Shame really, as Quiver can ask a question clearly :)
        He could have been harvested as an expert in time I would guess :D

        Comment

        • ADezii
          Recognized Expert Expert
          • Apr 2006
          • 8834

          #19
          Originally posted by BradHodge
          Thanks guys for sticking with me on this one. I messed around with a copy of the basic database today at lunch. I did normalize the tblFamily and moved the Teacher/Sub indication to it's own table. It did not change the results any, but I didn't have much time to fool with it.

          I'll work with the real database when I get home this evening and let you all know how it's going.

          And yes Ade, that is a Nom de Plume that I occasionally use. I just tend to get confused on who I am from day to day :)

          Brad.
          Nom de Plume
          Is that French, BradHodge? You Experts/Admins are so refined! (LOL).

          Comment

          • ADezii
            Recognized Expert Expert
            • Apr 2006
            • 8834

            #20
            Originally posted by NeoPa
            Never a problem my friend :)
            You're absolutely right about the Step part. Each turn through the loop processes two parameters. This leads back to the Stop bit too.
            Stop is like hitting a breakpoint.
            As the only reason for this occurring is a programming error (the code is called incorrectly rather than a problem occurring during normal usage) this is perfect error handling for me. As I design the code I will always be the one who finds this error. This seems to be the most appropriate way to give me the information I need to fix the problem. It's fine to replace it with code you're happier with though. The bread and butter of the procedure is the rest of it anyway.
            Thanks NeoPa for the follow up explanation. It was just intellectual curiosity, nothing else.

            Comment

            • NeoPa
              Recognized Expert Moderator MVP
              • Oct 2006
              • 32633

              #21
              Originally posted by ADezii
              Is that French, BradHodge? You Experts/Admins are so refined! (LOL).
              Nom de Plume is just French for Pen Name. They just used it first is all ;)
              Oh, and by the way, you're an Expert too if you hadn't noticed :D

              Comment

              • ADezii
                Recognized Expert Expert
                • Apr 2006
                • 8834

                #22
                Originally posted by NeoPa
                Nom de Plume is just French for Pen Name. They just used it first is all ;)
                Oh, and by the way, you're an Expert too if you hadn't noticed :D
                Yea, but that's just my Nom de Plume.

                Comment

                • BradHodge
                  Recognized Expert New Member
                  • Apr 2007
                  • 166

                  #23
                  Okay... I have the problem narrowed down.

                  Here is the meat of my code...
                  Code:
                  For Each varItm1 In ctl_1.ItemsSelected 
                      For Each varItm2 In ctl_2.ItemsSelected
                          strFam = DLookup("FamilyID", "tblKids", "ChildID =" &
                                        ctl_1.ItemData(varItm1))
                          strTeach = Nz(ctl_2.ItemData(varItm2), 0)
                          strRate = DLookup("RateID", "tblRates", "[RateDescrip]= '" &  
                                        DLookup("Teach_Sub", "tblTeachers", "[FamilyID]= " & strFam)
                                                     & "'")
                  
                                  If strFam = strTeach Then
                                        DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID,
                                                                   FamilyID,RateID) VALUES (#" & Me.txtDate
                                                                   & "#, " & ctl_1.ItemData(varItm1) & ", " &
                                                                   strFam & ", " & strRate & ");"
                                 Else:
                                 strRate2 = DLookup("RateID", "tblRates", "[RateDescrip]= '" & 
                                 DLookup("Group", "tblKids", "[ChildID]= " & ctl_1.ItemData(varItm1))
                                              & "'")
                                              DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID,
                                                                         FamilyID, RateID) VALUES (#" &  
                                                                         Me.txtDate & "#, " &
                                                                         ctl_1.ItemData(varItm1) & ", " & strFam &
                                                                         ", " & strRate2 & ");"
                                 End If
                      Next varItm2
                  Next varItm1
                  I think the problem lies in the If / Then Statement. When it finds a match between the 2 List Boxes, it appends (correctly). But then it goes through another Loop and finds a place that they don't match, and it goes to the Else: append. This would explain this scenario
                  2) If you select 2 or more children and 2 or more parent/teachers, the append
                  will double
                  - The child with a parent/teacher present will append at $0.00 and then
                  again at the regular rate.
                  I think it needs a qualifier in the If / Then statement that has it look at tblCharges and make sure that there is not already a record with that ChildID on that Date. It would seem like it needs this both in the If statement and the Else statement. No clue how to do this... Any thoughts???

                  With regards to
                  3) If you select 1 or more children without any parent/teachers selected, no append occurs.
                  I think that this is occurring because it doesn't know what to do with no value in ListTeach. Since multi-select list boxes have Null values by default I can't just us an If IsNull (Me.ListTeach) statement.

                  Does anyone know how to check to see if no selections have been made from a Multi-Select List Box?

                  Sincerely,
                  The Name of the Pen

                  Comment

                  • BradHodge
                    Recognized Expert New Member
                    • Apr 2007
                    • 166

                    #24
                    #
                    DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID,#
                    FamilyID, RateID) VALUES (#" & #
                    Me.txtDate & "#, " &
                    #
                    ctl_1.ItemData( varItm1) & ", " & strFam &
                    #
                    ", " & strRate2 & ");" #
                    End If
                    CURSED FORMATTING!!! :)

                    B
                    r

                    a d .

                    Comment

                    • ADezii
                      Recognized Expert Expert
                      • Apr 2006
                      • 8834

                      #25
                      Originally posted by BradHodge
                      Okay... I have the problem narrowed down.

                      Here is the meat of my code...
                      Code:
                      For Each varItm1 In ctl_1.ItemsSelected 
                          For Each varItm2 In ctl_2.ItemsSelected
                              strFam = DLookup("FamilyID", "tblKids", "ChildID =" &
                                            ctl_1.ItemData(varItm1))
                              strTeach = Nz(ctl_2.ItemData(varItm2), 0)
                              strRate = DLookup("RateID", "tblRates", "[RateDescrip]= '" &  
                                            DLookup("Teach_Sub", "tblTeachers", "[FamilyID]= " & strFam)
                                                         & "'")
                      
                                      If strFam = strTeach Then
                                            DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID,
                                                                       FamilyID,RateID) VALUES (#" & Me.txtDate
                                                                       & "#, " & ctl_1.ItemData(varItm1) & ", " &
                                                                       strFam & ", " & strRate & ");"
                                     Else:
                                     strRate2 = DLookup("RateID", "tblRates", "[RateDescrip]= '" & 
                                     DLookup("Group", "tblKids", "[ChildID]= " & ctl_1.ItemData(varItm1))
                                                  & "'")
                                                  DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID,
                                                                             FamilyID, RateID) VALUES (#" &  
                                                                             Me.txtDate & "#, " &
                                                                             ctl_1.ItemData(varItm1) & ", " & strFam &
                                                                             ", " & strRate2 & ");"
                                     End If
                          Next varItm2
                      Next varItm1
                      I think the problem lies in the If / Then Statement. When it finds a match between the 2 List Boxes, it appends (correctly). But then it goes through another Loop and finds a place that they don't match, and it goes to the Else: append. This would explain this scenario

                      I think it needs a qualifier in the If / Then statement that has it look at tblCharges and make sure that there is not already a record with that ChildID on that Date. It would seem like it needs this both in the If statement and the Else statement. No clue how to do this... Any thoughts???

                      With regards to
                      I think that this is occurring because it doesn't know what to do with no value in ListTeach. Since multi-select list boxes have Null values by default I can't just us an If IsNull (Me.ListTeach) statement.

                      Does anyone know how to check to see if no selections have been made from a Multi-Select List Box?

                      Sincerely,
                      The Name of the Pen
                      Assuming your List Box is named lstTest:
                      [CODE=vb]
                      If Me![lstTest].ItemsSelected. Count = 0 Then
                      MsgBox "No Items were selected"
                      Else
                      MsgBox Me![lstTest].ItemsSelected. Count & " Item(s) was/were selected"
                      End If[/CODE]

                      Comment

                      • BradHodge
                        Recognized Expert New Member
                        • Apr 2007
                        • 166

                        #26
                        Originally posted by ADezii
                        Assuming your List Box is named lstTest:
                        [CODE=vb]
                        If Me![lstTest].ItemsSelected. Count = 0 Then
                        MsgBox "No Items were selected"
                        Else
                        MsgBox Me![lstTest].ItemsSelected. Count & " Item(s) was/were selected"
                        End If[/CODE]
                        Thanks ADezii, That helps. I would assume that I would just run an If/Then statement like this to assess ListTeach, and then run a separate Append statement if =0, followed by exit sub? Then, if >0, proceed with the other Appends?

                        If so, this should take care of no append occurring when ListTeach is blank.

                        Now to figure out the other where Appends are doubling.

                        Thanks again,
                        Brad.

                        Comment

                        • ADezii
                          Recognized Expert Expert
                          • Apr 2006
                          • 8834

                          #27
                          Originally posted by BradHodge
                          Thanks ADezii, That helps. I would assume that I would just run an If/Then statement like this to assess ListTeach, and then run a separate Append statement if =0, followed by exit sub? Then, if >0, proceed with the other Appends?

                          If so, this should take care of no append occurring when ListTeach is blank.

                          Now to figure out the other where Appends are doubling.

                          Thanks again,
                          Brad.
                          BradHodge, in my opinion, you should never be testing whether or not 2 List Boxes have at least 1 Item selected while you are in a For Each..Next, Do While..Loop, If...End If, etc. scenario. The time for testing these conditions are at the very beginning of the Procedure 'before' entering any of these constructs, as in:
                          [CODE=vb]
                          Dim ctl_1 As ListBox, ctl_2 As ListBox

                          Set ctl_1 = Me!ListKids
                          Set ctl_2 = Me!ListTeach

                          If ctl_1.ItemsSele cted.Count = 0 Then
                          'Emphasize no Item selected using color Background, be
                          'sure to Reset to White at some point
                          ctl_1.SetFocus: ctl_1.BackColor = QBColor(14)
                          Exit Sub
                          ElseIf ctl_2.ItemsSele cted.Count = 0 Then
                          ctl_2.SetFocus: ctl_2.BackColor = QBColor(14)
                          Exit Sub
                          Else
                          'Both List Boxes have at least 1 Item Selected
                          'Normal processing would proceed here
                          End If[/CODE]

                          Comment

                          • BradHodge
                            Recognized Expert New Member
                            • Apr 2007
                            • 166

                            #28
                            Originally posted by ADezii
                            BradHodge, in my opinion, you should never be testing whether or not 2 List Boxes have at least 1 Item selected while you are in a For Each..Next, Do While..Loop, If...End If, etc. scenario. The time for testing these conditions are at the very beginning of the Procedure 'before' entering any of these constructs, as in:
                            [CODE=vb]
                            Dim ctl_1 As ListBox, ctl_2 As ListBox

                            Set ctl_1 = Me!ListKids
                            Set ctl_2 = Me!ListTeach

                            If ctl_1.ItemsSele cted.Count = 0 Then
                            'Emphasize no Item selected using color Background, be
                            'sure to Reset to White at some point
                            ctl_1.SetFocus: ctl_1.BackColor = QBColor(14)
                            Exit Sub
                            ElseIf ctl_2.ItemsSele cted.Count = 0 Then
                            ctl_2.SetFocus: ctl_2.BackColor = QBColor(14)
                            Exit Sub
                            Else
                            'Both List Boxes have at least 1 Item Selected
                            'Normal processing would proceed here
                            End If[/CODE]
                            That's what I was thinking. I'll work on it and let you know. Thanks so much!
                            Brad.

                            Comment

                            • BradHodge
                              Recognized Expert New Member
                              • Apr 2007
                              • 166

                              #29
                              Originally posted by BradHodge
                              That's what I was thinking. I'll work on it and let you know. Thanks so much!
                              Brad.
                              Well... After many revisions, Here is the simplified version. It is essentially based on 3 separate loops.

                              Loop 1: Assess ListTeach for selected items. If no selected items, then charge the children present based on the Child's Rate.

                              Loop 2: Compare ListKids.ItemDa ta to ListTeach.ItemD ata. If it matches, charge the child based on the Teacher's Rate.

                              Loop 3: Compare ListKids.ItemDa ta to tblCharges. If the child has already been charged that day, go to the next ListKids.ItemDa ta. Compare ListKids.ItemDa ta to ListTeach.ItemD ata. If no match, charge the child based on the Child's Rate.

                              It is working well. I am betting though that I could have simplified my code by using an Array; but I'll have to save that for another day.

                              Thanks all for your help!

                              Brad.

                              Comment

                              Working...