DAO Recordset Nested Loops

[Quiver]

I'm probably going off in the wrong direction, so feel free to offer other suggestions here.

Overview: I need to look at a common field in two tables and find where they are equal. When equal, I need to UPDATE another table with a value, and when not equal, I need to UPDATE that table with a different value.

Details: The first table is tblKidCharges and the second table is tblTeach. Both tables contain the field FamilyID. When both a child and his parent (the Teacher) are present, their family should receive a $0.00 charge that day. However, if the child is present and the parent is not present, the family will receive a $15.00 charge.

Work Thus Far: I figure this will be done with 2 recordsets. I have experimented with cycling through the recordsets to find where they are equal, but I can't get them to then UPDATE the table (tblCharges).

Any thoughts would be greatly appreciated!

[ADezii]

I'm probably going off in the wrong direction, so feel free to offer other suggestions here.

Overview: I need to look at a common field in two tables and find where they are equal. When equal, I need to UPDATE another table with a value, and when not equal, I need to UPDATE that table with a different value.

Details: The first table is tblKidCharges and the second table is tblTeach. Both tables contain the field FamilyID. When both a child and his parent (the Teacher) are present, their family should receive a $0.00 charge that day. However, if the child is present and the parent is not present, the family will receive a $15.00 charge.

Work Thus Far: I figure this will be done with 2 recordsets. I have experimented with cycling through the recordsets to find where they are equal, but I can't get them to then UPDATE the table (tblCharges).

Any thoughts would be greatly appreciated!

1. Kindly post the Field Names and their Data Tyeps in tblKidCharges, tblTeach, and tblCharges.
2. List any Relationships among the Tables and the Keys involved, along with their Data Types.
3. Define the conditions under which a Child could be present in tblKidCharges and a Parent would also be present in tblTeach.
4. What condition would indicate that a Child was present in tblKidCharges and his/her Parent wasn't in tblTeach?
5. How would you know the specific Record to Update in tblCharges? [FamilyID]?

[Quiver]

1. Kindly post the Field Names and their Data Tyeps in tblKidCharges, tblTeach, and tblCharges.
2. List any Relationships among the Tables and the Keys involved, along with their Data Types.
3. Define the conditions under which a Child could be present in tblKidCharges and a Parent would also be present in tblTeach.
4. What condition would indicate that a Child was present in tblKidCharges and his/her Parent wasn't in tblTeach?
5. How would you know the specific Record to Update in tblCharges? [FamilyID]?

1)
tblKidCharges
KidBatchID (AutoNumber)
BatchID (Number)
ChildID (Number)
Dte (Date/Time)
FamilyID (Number)
LName (Text)
Group (Text)

tblTeach
TeachBatchID (AutoNumber)
BatchID (Number)
FamilyID (Number)
Dte (Date/Time)
FamilyLName (Text)

tblCharges
BatchID (Number)
Dte (Date/Time)
ChildID (Number)
DateStamp (Date/Time)
RateID (Number)
FamilyID (Number)

2)
There are no relationships currently between these tables, but there is a one-to-many between tblFamily and tblKids that link by the FamilyID.

3) & 4)
The data ends up in tblKidCharges and tblTeach by way of 2 multi-select listboxes on frmMAIN. When "charges" are being entered, the user will choose all of the kids present, and then all of the teachers present. A teacher will rarely be present if her children are not, but kids may be present without their teacher/parent being present. When a teacher is present, all of her kids that are also present that day are not charged for the day.

5)
I am envisioning that the charges will ultimately be posted to tblCharges along with the rate that was charged. I don't want the user to have to choose a rate. I want the single click of a button on frmMAIN to take all of the kids and teachers present that day (from the list boxes) and put them in their respective tables. Then begin the process of looking for kids who have their parent/teacher also present, so that the $0.00 rate can be chosen, and subsequently appending that data to tblCharges. I would then like to take the remaining kids, and charge them the rate that is detailed in tblRates (I'm pretty sure I have this step covered).


I hope that clears things up. Let me know if you need more info.

Thanks for your help!

[ADezii]

1)
tblKidCharges
KidBatchID (AutoNumber)
BatchID (Number)
ChildID (Number)
Dte (Date/Time)
FamilyID (Number)
LName (Text)
Group (Text)

tblTeach
TeachBatchID (AutoNumber)
BatchID (Number)
FamilyID (Number)
Dte (Date/Time)
FamilyLName (Text)

tblCharges
BatchID (Number)
Dte (Date/Time)
ChildID (Number)
DateStamp (Date/Time)
RateID (Number)
FamilyID (Number)

2)
There are no relationships currently between these tables, but there is a one-to-many between tblFamily and tblKids that link by the FamilyID.

3) & 4)
The data ends up in tblKidCharges and tblTeach by way of 2 multi-select listboxes on frmMAIN. When "charges" are being entered, the user will choose all of the kids present, and then all of the teachers present. A teacher will rarely be present if her children are not, but kids may be present without their teacher/parent being present. When a teacher is present, all of her kids that are also present that day are not charged for the day.

5)
I am envisioning that the charges will ultimately be posted to tblCharges along with the rate that was charged. I don't want the user to have to choose a rate. I want the single click of a button on frmMAIN to take all of the kids and teachers present that day (from the list boxes) and put them in their respective tables. Then begin the process of looking for kids who have their parent/teacher also present, so that the $0.00 rate can be chosen, and subsequently appending that data to tblCharges. I would then like to take the remaining kids, and charge them the rate that is detailed in tblRates (I'm pretty sure I have this step covered).


I hope that clears things up. Let me know if you need more info.

Thanks for your help!

Quiver, I must admit that I am having a difficult time following your logic, and I do believe that it is a little unorthodox, but let's take it 1 step at a time.

1. tblKidsCharges will be populated via multiple selections made from a Combo Box on frmMain. This Combo Box must then display all the Fields in tblKidCharges except Date. If this is true:
   1. What is the Name of the Combo Box?
   2. What is the Row Source for the Combo Box?
   3. How many Columns are there in the Combo Box (displayed and non-displayed)?
   4. What are the Column Widths (basically same question as previous)?
   5. Does the Combo Box have a Control Source? If so, what is it?
   6. Does the Combo Box have a Bound Field? If so, what is it?
2. Forget tblTeach and tblCharges for now. If the User makes 4 Kid selections from the Combo Box, you would then want a Button Click() to write 4 Records to tblKidCharges, correct?
3. Fill in the missing blanks, and we'll take it from there.

[BradHodge]

Quiver, I must admit that I am having a difficult time following your logic, and I do believe that it is a little unorthodox, but let's take it 1 step at a time.

1. tblKidsCharges will be populated via multiple selections made from a Combo Box on frmMain. This Combo Box must then display all the Fields in tblKidCharges except Date. If this is true:
   1. What is the Name of the Combo Box?
   2. What is the Row Source for the Combo Box?
   3. How many Columns are there in the Combo Box (displayed and non-displayed)?
   4. What are the Column Widths (basically same question as previous)?
   5. Does the Combo Box have a Control Source? If so, what is it?
   6. Does the Combo Box have a Bound Field? If so, what is it?
2. Forget tblTeach and tblCharges for now. If the User makes 4 Kid selections from the Combo Box, you would then want a Button Click() to write 4 Records to tblKidCharges, correct?
3. Fill in the missing blanks, and we'll take it from there.

If I could count the number of times my wife has said she was having trouble following my logic :)


1a) The selections on frmMAIN are actually made from List Boxes. The one for the kids is ListKids and is made up of 3 columns (ChildID, LName and FName) with Child ID being bound and invisible to the user.

The one for the teachers is ListTeach and is made up of 4 columns (FamilyID, FamilyLName, MomFName and TeacherSub(this is a parameter column that just narrows the lists to those parents that are either parents or subs)). FamilyID is the bound column and also invisible.

1b) The Row Source for ListKids is tblKids.

1c) See 1a

1d) The exact column widths are ListKids: 0";0.625";0.6771" and ListTeach: 0"; 0.625";0.6771";0"

1e) Neither List Box has a Control Source

1f) See 1a

2) This is the code that writes the records to tblKidCharges

Dim strSQL as String
Dim strSQL2 as String
Dim strSQL3 as String
Dim ctl as Control
DoCmd.SetWarnings False
    Set ctl = Forms![frmMain]![ListKids]
    strSQL = "INSERT INTO tblKidCharges (BatchID, ChildID, Dte) VALUES("
    strSQL = strSQL & Me.BatchID & ", "
    strSQL2 = Me.txtDate & "#"
    For Each varItem In ctl.ItemsSelected
        strSQL3 = strSQL & ctl.ItemData(varItem) & ", #" & strSQL2 & ");"
        CurrentDb.Execute strSQL3
    Next varItem
    DoCmd.RunSQL "UPDATE tblKidCharges LEFT JOIN tblKids ON tblKidCharges.childID = tblKids.ChildID " _
                    & "SET tblKidCharges.FamilyID = tblKids.FamilyId, tblKidCharges.LName = " _
                    & "tblKids.LName, tblKidCharges.[Group] = tblKids.Group;"
    DoCmd.SetWarnings True

3) In case it helps, the related tables are as follows:

tblFamily
FamilyID (Number)
FamilyLName (Text)
DadFName (Text)
MomFName (Text)
TeacherSub (Text) --Can have value of Null, Teacher or Sub

tblRates
RateID (Number)
RateDescrip (Text)
Rate (Currency)


Thanks for your time on this. I will be interested in hearing your thoughts even if it's to scrap this design and head off in a different direction :)

[BradHodge]

If I could count the number of times my wife has said she was having trouble following my logic :)


1a) The selections on frmMAIN are actually made from List Boxes. The one for the kids is ListKids and is made up of 3 columns (ChildID, LName and FName) with Child ID being bound and invisible to the user.

The one for the teachers is ListTeach and is made up of 4 columns (FamilyID, FamilyLName, MomFName and TeacherSub(this is a parameter column that just narrows the lists to those parents that are either parents or subs)). FamilyID is the bound column and also invisible.

1b) The Row Source for ListKids is tblKids.

1c) See 1a

1d) The exact column widths are ListKids: 0";0.625";0.6771" and ListTeach: 0"; 0.625";0.6771";0"

1e) Neither List Box has a Control Source

1f) See 1a

2) This is the code that writes the records to tblKidCharges

Dim strSQL as String
Dim strSQL2 as String
Dim strSQL3 as String
Dim ctl as Control
DoCmd.SetWarnings False
    Set ctl = Forms![frmMain]![ListKids]
    strSQL = "INSERT INTO tblKidCharges (BatchID, ChildID, Dte) VALUES("
    strSQL = strSQL & Me.BatchID & ", "
    strSQL2 = Me.txtDate & "#"
    For Each varItem In ctl.ItemsSelected
        strSQL3 = strSQL & ctl.ItemData(varItem) & ", #" & strSQL2 & ");"
        CurrentDb.Execute strSQL3
    Next varItem
    DoCmd.RunSQL "UPDATE tblKidCharges LEFT JOIN tblKids ON tblKidCharges.childID = tblKids.ChildID " _
                    & "SET tblKidCharges.FamilyID = tblKids.FamilyId, tblKidCharges.LName = " _
                    & "tblKids.LName, tblKidCharges.[Group] = tblKids.Group;"
    DoCmd.SetWarnings True

3) In case it helps, the related tables are as follows:

tblFamily
FamilyID (Number)
FamilyLName (Text)
DadFName (Text)
MomFName (Text)
TeacherSub (Text) --Can have value of Null, Teacher or Sub

tblRates
RateID (Number)
RateDescrip (Text)
Rate (Currency)


Thanks for your time on this. I will be interested in hearing your thoughts even if it's to scrap this design and head off in a different direction :)

One more piece of info...

My initial thoughts were to begin the append to tblCharges from frmMAIN (instead of doing the interim append to tblKidCharges and tblTeach). It seems like this would be the more straight forward approach, but I couldn't figure out how to loop through the List Boxes to evaluate the selections, and determine which kids had their parent/teachers present as well.

Incase that's of any benefit.

Thanks again.

[ADezii]

If I could count the number of times my wife has said she was having trouble following my logic :)


1a) The selections on frmMAIN are actually made from List Boxes. The one for the kids is ListKids and is made up of 3 columns (ChildID, LName and FName) with Child ID being bound and invisible to the user.

The one for the teachers is ListTeach and is made up of 4 columns (FamilyID, FamilyLName, MomFName and TeacherSub(this is a parameter column that just narrows the lists to those parents that are either parents or subs)). FamilyID is the bound column and also invisible.

1b) The Row Source for ListKids is tblKids.

1c) See 1a

1d) The exact column widths are ListKids: 0";0.625";0.6771" and ListTeach: 0"; 0.625";0.6771";0"

1e) Neither List Box has a Control Source

1f) See 1a

2) This is the code that writes the records to tblKidCharges

Dim strSQL as String
Dim strSQL2 as String
Dim strSQL3 as String
Dim ctl as Control
DoCmd.SetWarnings False
    Set ctl = Forms![frmMain]![ListKids]
    strSQL = "INSERT INTO tblKidCharges (BatchID, ChildID, Dte) VALUES("
    strSQL = strSQL & Me.BatchID & ", "
    strSQL2 = Me.txtDate & "#"
    For Each varItem In ctl.ItemsSelected
        strSQL3 = strSQL & ctl.ItemData(varItem) & ", #" & strSQL2 & ");"
        CurrentDb.Execute strSQL3
    Next varItem
    DoCmd.RunSQL "UPDATE tblKidCharges LEFT JOIN tblKids ON tblKidCharges.childID = tblKids.ChildID " _
                    & "SET tblKidCharges.FamilyID = tblKids.FamilyId, tblKidCharges.LName = " _
                    & "tblKids.LName, tblKidCharges.[Group] = tblKids.Group;"
    DoCmd.SetWarnings True

3) In case it helps, the related tables are as follows:

tblFamily
FamilyID (Number)
FamilyLName (Text)
DadFName (Text)
MomFName (Text)
TeacherSub (Text) --Can have value of Null, Teacher or Sub

tblRates
RateID (Number)
RateDescrip (Text)
Rate (Currency)


Thanks for your time on this. I will be interested in hearing your thoughts even if it's to scrap this design and head off in a different direction :)

I'm headed off to work and have only a couple of minutes to look at this, but the following code should, (hasn't been tested), loop throught all the Selected Items in ListKids and check each Item against ALL Selected Items in ListTeach. If an equality exists between any Item, based on the Bound Column only, you can then take some action. The logic may have to be changed somewhat, but is this basically what you are referring to?

Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm As Variant, varItm_2 As Variant, intI As Integer
 
Set frm = Forms!YourFormName
 
Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach
 
'Check every Selected Item in ListKids against every Selected Item in ListTeach
For Each varItm In ctl.ItemsSelected
  For Each varItm_2 In ctl_2.ItemsSelected
    If ctl.ItemData(varItm) = ctl_2.ItemData(varItm_2) Then
      'indicates equality based on the Bound Columns of both List Boxes
    End If
  Next varItm_2
Next varItm

[BradHodge]

I'm headed off to work and have only a couple of minutes to look at this, but the following code should, (hasn't been tested), loop throught all the Selected Items in ListKids and check each Item against ALL Selected Items in ListTeach. If an equality exists between any Item, based on the Bound Column only, you can then take some action. The logic may have to be changed somewhat, but is this basically what you are referring to?

Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm As Variant, varItm_2 As Variant, intI As Integer
 
Set frm = Forms!YourFormName
 
Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach
 
'Check every Selected Item in ListKids against every Selected Item in ListTeach
For Each varItm In ctl.ItemsSelected
  For Each varItm_2 In ctl_2.ItemsSelected
    If ctl.ItemData(varItm) = ctl_2.ItemData(varItm_2) Then
      'indicates equality based on the Bound Columns of both List Boxes
    End If
  Next varItm_2
Next varItm

That is in the direction that I was headed. It's nice to see the proper way to nest the two (that always throws me).

Since the bound column in ListKids is ChildID and the bound in ListTeach is FamilyID, I can't use it without modifications. I was however thinking of using a DLookUp for the 1st part of the expression to get the applicable FamilyID. Here is what I have so far...

Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm1 As Variant, varItm2 As Variant, intI As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim strFam As String
Dim strTeach As String
Dim strKid As String
Dim strRate, strRate2 As String
 
Set frm = Forms!frmMain
Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach
 
DoCmd.SetWarnings False
 
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("Teacher_Sub", "tblFamily", "[FamilyID]= " & strFam) & "'")
 
            If strFam = strTeach Then
                DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID, FamilyID, RateID) VALUES (#" & _
                Me.txtDate & "#, " & ctl_1.ItemData(varItm1) & ", " & strFam & ", " & strRate & ");"
                strFam = Nz(ctl_2.ItemData(varItm2), 0)
            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
 
DoCmd.SetWarnings True

Here are the current scenarios...
1) If you select 1 or more children and 1 parent/teacher, they will append
correctly.

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.
- The child without a parent/teacher present will append twice at the correct
rate).

3) If you select 1 or more children without any parent/teachers selected, no
append occurs.

Any thoughts?

As always... Thanks!

My apologies for the strange indenting... Couldn't get it look as pretty as I wanted

[ADezii]

That is in the direction that I was headed. It's nice to see the proper way to nest the two (that always throws me).

Since the bound column in ListKids is ChildID and the bound in ListTeach is FamilyID, I can't use it without modifications. I was however thinking of using a DLookUp for the 1st part of the expression to get the applicable FamilyID. Here is what I have so far...

Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm1 As Variant, varItm2 As Variant, intI As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim strFam As String
Dim strTeach As String
Dim strKid As String
Dim strRate, strRate2 As String
 
Set frm = Forms!frmMain
Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach
 
DoCmd.SetWarnings False
 
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("Teacher_Sub", "tblFamily", "[FamilyID]= " & strFam) & "'")
 
            If strFam = strTeach Then
                DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID, FamilyID, RateID) VALUES (#" & _
                Me.txtDate & "#, " & ctl_1.ItemData(varItm1) & ", " & strFam & ", " & strRate & ");"
                strFam = Nz(ctl_2.ItemData(varItm2), 0)
            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
 
DoCmd.SetWarnings True

Here are the current scenarios...
1) If you select 1 or more children and 1 parent/teacher, they will append
correctly.

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.
- The child without a parent/teacher present will append twice at the correct
rate).

3) If you select 1 or more children without any parent/teachers selected, no
append occurs.

Any thoughts?

As always... Thanks!

My apologies for the strange indenting... Couldn't get it look as pretty as I wanted

I'll take everything into work tomorrow, and hopefully, I will give it a good look and see. I will get back to you as soon as the time allows.

[ADezii]

That is in the direction that I was headed. It's nice to see the proper way to nest the two (that always throws me).

Since the bound column in ListKids is ChildID and the bound in ListTeach is FamilyID, I can't use it without modifications. I was however thinking of using a DLookUp for the 1st part of the expression to get the applicable FamilyID. Here is what I have so far...

Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm1 As Variant, varItm2 As Variant, intI As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim strFam As String
Dim strTeach As String
Dim strKid As String
Dim strRate, strRate2 As String
 
Set frm = Forms!frmMain
Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach
 
DoCmd.SetWarnings False
 
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("Teacher_Sub", "tblFamily", "[FamilyID]= " & strFam) & "'")
 
            If strFam = strTeach Then
                DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID, FamilyID, RateID) VALUES (#" & _
                Me.txtDate & "#, " & ctl_1.ItemData(varItm1) & ", " & strFam & ", " & strRate & ");"
                strFam = Nz(ctl_2.ItemData(varItm2), 0)
            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
 
DoCmd.SetWarnings True

Here are the current scenarios...
1) If you select 1 or more children and 1 parent/teacher, they will append
correctly.

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.
- The child without a parent/teacher present will append twice at the correct
rate).

3) If you select 1 or more children without any parent/teachers selected, no
append occurs.

Any thoughts?

As always... Thanks!

My apologies for the strange indenting... Couldn't get it look as pretty as I wanted

After taking a closer look at everything, I feel as though your iinitial impression in Post #5 may be correct:

I will be interested in hearing your thoughts even if it's to scrap this design and head off in a different direction

You are working with 4 Independent Tables, 2 Related Tables, and attempting to integrate them all into an Application. There seems to be too many unrelated processes and intermediate steps. Personally, i feel as though a Redesign is in order but I'll call on some of the other Experts/Moderators for their viewpoints on the subject.

[nico5038]

I agree with a redesign.
1) Start with defining the needed output like the report "Charges"
2) Use the report fields to reorganize into normalized tables.
3) Design the needed processes and use a form per process like:
Add Child /Update Child/Delete Child
Add Teacher /Update Teacher/Delete Teacher
Record attendance

As far as I see you would need a tblTeacher and a tblChild and for those children with a "teaching parent" a relation table tblTeacherChild. Finally a tblAttendance can be used to record the data for the report.
For the attendance you need to record only the children and add an indication or the parent is a teacher.

When you have multiple groups you might add a "class" table.

Nic;o)

[BradHodge]

I agree with a redesign.
1) Start with defining the needed output like the report "Charges"
2) Use the report fields to reorganize into normalized tables.
3) Design the needed processes and use a form per process like:
Add Child /Update Child/Delete Child
Add Teacher /Update Teacher/Delete Teacher
Record attendance

As far as I see you would need a tblTeacher and a tblChild and for those children with a "teaching parent" a relation table tblTeacherChild. Finally a tblAttendance can be used to record the data for the report.
For the attendance you need to record only the children and add an indication or the parent is a teacher.

When you have multiple groups you might add a "class" table.

Nic;o)

Thanks ADezii and Nic;o) for your input. It sounds as if I was at least partially on the right track. My current tables are...

tblKids
tblFamily (which includes an indicator if there is a teacher or sub in the family)
tblTeachers (which runs my List Box)
tblRates (with 2 different class rates, a teacher rate and a sub rate)
tblCharges

A little more info on how everything currently works.
1) The user chooses all kids present and all teachers/subs present from 2 different list boxes on one form.
2) On the click of a button, the app compares ListKids to ListTeachers and appends the appropriate Kids to tblCharges with the ChildID, FamilyID (both from tblKids), the Date (from frmMAIN), and the RateID from the series of DLookups to tblRates.
3) The append is happening, with the exceptions mentioned in the post above

Here are the current scenarios...
1) If you select 1 or more children and 1 parent/teacher, they will append
correctly.
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.
- The child without a parent/teacher present will append twice at the correct
rate).
3) If you select 1 or more children without any parent/teachers selected, no
append occurs.

In addition, Nic;o), I do have separate forms for adding families, children and teachers, and all of them are working well.

I would assume that my tables are fairly normalized (although I'm sure that "fairly normalized" is an oxymoron ), and that the fields that I am wanting to input into tblCharges are good fields (mostly ID fields that can I can reference when generating reports).

Am I way off base here?

Thanks!

Brad.

[ADezii]

I agree with a redesign.
1) Start with defining the needed output like the report "Charges"
2) Use the report fields to reorganize into normalized tables.
3) Design the needed processes and use a form per process like:
Add Child /Update Child/Delete Child
Add Teacher /Update Teacher/Delete Teacher
Record attendance

As far as I see you would need a tblTeacher and a tblChild and for those children with a "teaching parent" a relation table tblTeacherChild. Finally a tblAttendance can be used to record the data for the report.
For the attendance you need to record only the children and add an indication or the parent is a teacher.

When you have multiple groups you might add a "class" table.

Nic;o)

Thanks for the Input, Nico, it is appreciated.

[NeoPa]

Brad,

Firstly (on a matter of simple curiosity) I assume that Quiver (OP) is simply a Nom de Plume?

As to the structure of your database. I would agree with Nico's suggestion and just clarify a little something to do with table structures. It's often tempting to organise things such that a relationship you know to exist is reflected in the structure. In this case Teacher & Parent as a single 'Relationship' as many children have that situation in the classroom. These are however, two distinct relationships, and need to be handled separately in a database for it to be able to help you as much as it can (the database that is). Databases aren't as bright as humans so where you or I could handle this extra level of complexity in our brains, for the most part, a database needs extra (more complicated) programming to make sense of this if the two are tied together in a way that is natural for human thought. This is where Normalisation can help. It keeps it all much simpler.

As a second (technical) comment, I find that when I need to doctor any SQL in my applications I use the Replace() function. This way I can use a template set of SQL and replace those items within it which are changeable. As an illustration :

Private Sub ???
  Dim strSQLTemplate As String
  Dim strSQL as String
  Dim varItem as Variant
 
  DoCmd.SetWarnings False
  strSQLTemplate = "INSERT INTO tblKidCharges " & _
                   "(BatchID, ChildID, Dte) " & _
                   "VALUES(%B, %C, #%D#)"
  strSQLTemplate = Replace(strSQLTemplate, "%B", Me.BatchID)
  strSQLTemplate = Replace(strSQLTemplate, "%C", Me.ChildID)
  For Each varItem In Me.ListKids.ItemsSelected
    strSQL = Replace(strSQLTemplate, "%D", varItem)
    CurrentDb.Execute strSQL3
  Next ctl
  ...
  DoCmd.SetWarnings True
End Sub

As a bonus (in case you're interested) I've written a short function to handle multiple replaces in one go. Feel free to copy and use. I find this invaluable when I have a whole bunch of fields to insert into a template. I also use it for configuring messages to the operator.

'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
'Using VbBinaryCompare means that case is not ignored.
Public Function MultiReplace(ByRef strMain As String, _
                             ByVal varParam As Variant, _
                             ByVal varReplace As Variant, _
                             ParamArray avarArgs())
    Dim intIdx As Integer
 
    If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
    MultiReplace = Replace(Expression:=strMain, _
                           Find:=Nz(varParam, ""), _
                           Replace:=Nz(varReplace, ""), _
                           Compare:=vbBinaryCompare)
    For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarArgs(intIdx), ""), _
                               Replace:=Nz(avarArgs(intIdx + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intIdx
End Function

[ADezii]

...
As a bonus (in case you're interested) I've written a short function to handle multiple replaces in one go. Feel free to copy and use. I find this invaluable when I have a whole bunch of fields to insert into a template. I also use it for configuring messages to the operator.

'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
'Using VbBinaryCompare means that case is not ignored.
Public Function MultiReplace(ByRef strMain As String, _
                             ByVal varParam As Variant, _
                             ByVal varReplace As Variant, _
                             ParamArray avarArgs())
    Dim intIdx As Integer
 
    If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
    MultiReplace = Replace(Expression:=strMain, _
                           Find:=Nz(varParam, ""), _
                           Replace:=Nz(varReplace, ""), _
                           Compare:=vbBinaryCompare)
    For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarArgs(intIdx), ""), _
                               Replace:=Nz(avarArgs(intIdx + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intIdx
End Function

Hello NeoPa, I'm a little confused on the Stop and Step Statements (Lines 9 and 14). I'm assuming the Step is needed because the Find and Replace Arguments occupy consecutive positions in avarArgs, but the Stop has me confused, probably because it is rare that you see it in action (LOL). If you have a minute, please expand on this.

BTW, thanks for your assistance in this Thread.

[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.

[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.

[NeoPa]

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

[ADezii]

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

[ADezii]

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.

[NeoPa]

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

[ADezii]

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.

[BradHodge]

Okay... I have the problem narrowed down.

Here is the meat of my 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

[BradHodge]

#
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 .

[ADezii]

Okay... I have the problem narrowed down.

Here is the meat of my 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:

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

[BradHodge]

Assuming your List Box is named lstTest:

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

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.

[ADezii]

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:

Dim ctl_1 As ListBox, ctl_2 As ListBox
 
Set ctl_1 = Me!ListKids
Set ctl_2 = Me!ListTeach
 
If ctl_1.ItemsSelected.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.ItemsSelected.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

[BradHodge]

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:

Dim ctl_1 As ListBox, ctl_2 As ListBox
 
Set ctl_1 = Me!ListKids
Set ctl_2 = Me!ListTeach
 
If ctl_1.ItemsSelected.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.ItemsSelected.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

That's what I was thinking. I'll work on it and let you know. Thanks so much!
Brad.

[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.ItemData to ListTeach.ItemData. If it matches, charge the child based on the Teacher's Rate.

Loop 3: Compare ListKids.ItemData to tblCharges. If the child has already been charged that day, go to the next ListKids.ItemData. Compare ListKids.ItemData to ListTeach.ItemData. 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.