Perfect!!!!! No need to apologize. You have learned something today.
Now, on to the next step!
We need to create a subform to house the People. I will describe everything here, and you have to follow directions--but you have to TRUST me on this, because I will tell you to do some strange things that don't make sense.
Create a new form called "fsubPeople ". Set its default view to "Continuous Forms". No navigation buttons, No Record Selectors, Vertical Only Scroll Bars, No Control Box, No Close Button, No Min Max Buttons, No Allow Additions.
Set its Record Source as tblPerson. (I'll explain later).
From the Form Design Tools, select Design, in the Tools Section, select Tools and click on Add Existing Fields. Drag and drop the F_Name and L_Name Fields over to the detail section of your form. Get these two text boxes nice and cozy all the way at the top of the detail section. You can delete the labels for these text boxes. Rename the text boxes to txtL_Name and txtF_Name.
Now, listen carefully. I can't explain why this happens (at least it does to me), but we need a way for this sub form to identify the Person ID, but we don't want to display it. Drag the PersonID field onto your form.
Then....
Delete it.
Add a Command button to the form and name it cmdAttend with caption "Attend". On Click event for that Command Button:
In the end your tiny subform should look something like this:
[imgnothumb]https://bytes.com/attachment.php? attachmentid=99 26&stc=1&d=1552 585184[/imgnothumb]
Finally, go back to the record source for this form and delete the Record Source. The only reason we used the table as a record source was to establish certain field names and ensure we got everything correct (and it's just easier than designing unbound controls that have nothing to use as control sources).
Save that form and close it.
Open your Attendance form in design view, and drag the new subform onto your main form. Be sure to rename the sub form fsubPeople (if it didn't default to that name).
But, now, we need a way to update the list of people on the sub form. Since we know that your SQL creates a useable list of people...... We need to set that string to the record source of the new subform you made. Your FindPeople() procedure should now look like this:
Notice that we also deleted the
Your main form should now look something similar to this:
[imgnothumb]https://bytes.com/attachment.php? attachmentid=99 27&stc=1&d=1552 585775[/imgnothumb]
But wait! There's one more thing we have to do. Whenever we change the value of your Date text box, we need to update everything, too. So, we add the following procedure in the text box's On Change Event:
We're just refreshing the value of the text box to that its value can be used, and updating the people.
Save your form and close it.
Open your Form and see if there are any errors. At this point you should be able to select different dates, and if there is any attendance data already in tblAttendance, it should reflect in your list of names. If you click on "Attend" next to any name, that name "should" disappear from the list.
Let me know how you get along with this.
Now, on to the next step!
We need to create a subform to house the People. I will describe everything here, and you have to follow directions--but you have to TRUST me on this, because I will tell you to do some strange things that don't make sense.
Create a new form called "fsubPeople ". Set its default view to "Continuous Forms". No navigation buttons, No Record Selectors, Vertical Only Scroll Bars, No Control Box, No Close Button, No Min Max Buttons, No Allow Additions.
Set its Record Source as tblPerson. (I'll explain later).
From the Form Design Tools, select Design, in the Tools Section, select Tools and click on Add Existing Fields. Drag and drop the F_Name and L_Name Fields over to the detail section of your form. Get these two text boxes nice and cozy all the way at the top of the detail section. You can delete the labels for these text boxes. Rename the text boxes to txtL_Name and txtF_Name.
Now, listen carefully. I can't explain why this happens (at least it does to me), but we need a way for this sub form to identify the Person ID, but we don't want to display it. Drag the PersonID field onto your form.
Then....
Delete it.
Add a Command button to the form and name it cmdAttend with caption "Attend". On Click event for that Command Button:
Code:
Option Compare Database
Option Explicit
Private Sub cmdAttend_Click()
Dim strSQL As String
Dim db As DAO.Database
strSQL = _
"INSERT INTO tblAttendance " & _
"( PersonID, AttendanceDate ) " & _
"VALUES ( " & Me.PersonID & ", #" & _
Me.Parent.Form.txtDate & "# );"
Debug.Print strSQL
Set db = CurrentDb()
Call db.Execute(Query:=strSQL, _
Options:=dbFailOnError)
Set db = Nothing
Me.Requery
End Sub
[imgnothumb]https://bytes.com/attachment.php? attachmentid=99 26&stc=1&d=1552 585184[/imgnothumb]
Finally, go back to the record source for this form and delete the Record Source. The only reason we used the table as a record source was to establish certain field names and ensure we got everything correct (and it's just easier than designing unbound controls that have nothing to use as control sources).
Save that form and close it.
Open your Attendance form in design view, and drag the new subform onto your main form. Be sure to rename the sub form fsubPeople (if it didn't default to that name).
But, now, we need a way to update the list of people on the sub form. Since we know that your SQL creates a useable list of people...... We need to set that string to the record source of the new subform you made. Your FindPeople() procedure should now look like this:
Code:
Private Sub FindPeople()
Dim strSQL As String
strSQL = _
"SELECT tblPerson.PersonID, " & _
"tblPerson.F_Name, " & _
"tblPerson.L_Name " & _
"FROM tblPerson " & _
"LEFT JOIN tblAttendance " & _
"ON tblPerson.PersonID = tblAttendance.PersonID " & _
"WHERE (tblAttendance.PersonID Is Null " & _
"AND tblAttendance.AttendanceDate=#" & Me.txtDate & "#) " & _
"OR tblAttendance.PersonID Is Null " & _
"ORDER BY tblPerson.L_Name, tblPerson.F_Name;"
With Me.fsubPeople
.Form.RecordSource = strSQL
.Requery
End With
End Sub
Debug.Print strSQL (because we know that it works!).Your main form should now look something similar to this:
[imgnothumb]https://bytes.com/attachment.php? attachmentid=99 27&stc=1&d=1552 585775[/imgnothumb]
But wait! There's one more thing we have to do. Whenever we change the value of your Date text box, we need to update everything, too. So, we add the following procedure in the text box's On Change Event:
Code:
Private Sub txtDate_Change()
Me.Refresh
Call FindPeople
End Sub
Save your form and close it.
Open your Form and see if there are any errors. At this point you should be able to select different dates, and if there is any attendance data already in tblAttendance, it should reflect in your list of names. If you click on "Attend" next to any name, that name "should" disappear from the list.
Let me know how you get along with this.
Comment