Re: After School Attendance

On Mon, 14 Jul 2008 08:59:02 -0700, joestros
<joestros@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I like the look of that attendance page. That is basically what I have
envisioned. What I do not understand is after markling all of the absences,
how do you "save" the changes and move on to the next day? Does selecting
the date simply create a new record in the attendance table?

As far as non-program days I would think that simply not selecting or
entering those dates would work.

I think we can get away with not worrying about semester since we can simply
run a query for a reporting period, say August-December 31 2008. Our
attendnace data is due twice per year and currently we just run reports based
on the given time period.

As far as year, I think it might be the same as semester. All we really
need to know is if a student at site "x" attended a day, and how many
students attended site "x" each day. Basically we need to store daily
attendance somewhere so we can run queries and reports for a given time
period or date.

I also forgot we are going to want to filter by grade as well. Should I
create a grade table?

What does the "select student" in the header do? Using this form do you
have to select each student at each site each day and add them to the days
roster? If so, this probably won't work since some sites have 100+ students
and it would very time consuming to create a roster each site. Isn't there a
way to pull in all active students per site each day using a query?

"Michael Gramelspacher" wrote:

On Thu, 10 Jul 2008 17:43:01 -0700, joestros
<joestros@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I am confused how to create a form with a selectable date, and also display
a roster from a specific site with an option group attached to each name. I
understand how to create a sub form, but am unsure how to bring all of the
data together.

Thank you again.

This is what I am looking at:
http://www.psci.net/gramelsp/temp/Attendance%20Form.jpg

The calendar control is by Allen Browne and is available on his web page.
http://allenbrowne.com/ser-51.html

I had never used it until just now. Allen's instructions are simple and clear.

This is the code behind the combobox:

Private Sub cbo_activity_name_AfterUpdate()

Dim s As String
Dim c As Variant

If Not IsNull(Me.cbo_activity_name) Then
' add to Where clause
c = (c + " AND ") & "activity_name = '" & _
Replace(Me.cbo_activity_name, "'", "''") & "'"
End If

If Not IsNull(Me.txt_calendar_date) Then
' add to Where clause
c = (c + " AND ") & "calendar_date = #" & Me.txt_calendar_date & "#"
End If

' eliminate initial AND
If Left(c, 5) = " AND " Then
c = Mid(c, 6)
End If

' if c is empty then use zero, i.e., show no records
If IsNull(c) Then c = "0"

s = "SELECT * FROM [Attendance Query] WHERE " & c & ";"

Debug.Print s

Me.StudentAttendance.Form.RecordSource = s
End Sub

This is the code from the date text box. Allen's control puts the date in the
text box and sets the focus to the text box.

Private Sub txt_calendar_date_GotFocus()

If Not IsNull(Me.txt_calendar_date) Then
If IsDate(Me.txt_calendar_date) Then

'same as this
cbo_activity_name_AfterUpdate
End If
End If
End Sub

There are other issues to consider. We need a way to prevent creating rosters
for non-school days. We also have to filter for the current year and semester.


Consider this form: http://www.psci.net/gramelsp/temp/Attendance%20Form.jpg

After you make changes to a records, Access automatically saves the changes. The
form is bound to a query and the record fields are bound to query columns.

Selecting a new date causes the form to show the records for that date. If no
records are shown, then you have not yet created them. Press the command button
to run a query to insert the records for that date.

If you create records for a non-program day, then you goofed. But really, you
need a calender table for this. The insert query will simply not allow records
to be created for a non-program date.

This form is just an example. If semesters are not part of your school, then
forget about semesters.

Probably you do not need a table for grades. You need a validation rule like
Between 1 and 8, or whatever range you need. A student's grade will change each
year. I would have to think about this, but I think I would store a fixed date
such as projected date of graduation and compute the grade level based on that
date. Think of it as a student belonging to a year group based upon graduation
year.

The command button creates a master roster for all schools. Since the main form
is based on schools, the subform only shows the records for the current school.

I uploaded the example here. The only thing not obvious is that double-clicking
in one of the select combo boxes clears it. Think of it as sort of a generic
attendance example.
http://www.psci.net/gramelsp/temp/Student%20Attendance.zip
.