Create a Control in a form that can store a cumulative count

[Dynoduck]

I am modifying a database form to include a Yes/no field that is checked each
time a student is absent. In conjunction with this field I want to add a
query field or form control that can count up the number of absences by
adding 1 to this field each time the absent field id checked. To make this
work I would have to clear the absent field each day or each time the student
record is accessed again. Can anyone suggest a way to do this? I am open to
any suggestion to accomplish the end objective of accumulating the number of
times a student is absent through the simple process of checking a yes/no
field.

 

[Dale Fye]

Well, If I were a student, and wanted a record of my absenses, or wanted to
contest your record of my absences, how would you show that?

I think I would have a StudentAbsence table that contains a StudentID,
AbsenceDate (default value =Date()), Excused (y/n), and Comments fields.

I would use the Click event of the checkbox on the main form to open a
second form (the StudentAbsence form) filtered by the current StudentID, to
a new record, and automatically enter the StudentID from the main form into
the StudentID of the popup form. This student Absense form would have Save
and Cancel buttons that would either save or cancel the current record, a
delete button to allow the user to delete the current record, and maybe a
report button to display the students absence report. When the user closes
this form, I would requery the Absenses textbox (which would be locked) to
update the count of absences.

On the main form, the Absenses textbox would have a control source of
something like:

=DCOUNT("StudentID", "StudentAbsences", "StudentID = " & me.txtStudentID)

You might event add a textbox for Unexcused absences with a similar DCOUNT
function as the control source.

HTH
Dale

 

[Ken Sheridan]

Two questions first. Are the absences on a daily basis? Is the absence
absolutely guaranteed to be recorded on the actual day of absence?

If the answer in both cases is Yes, then as well as the Students table you
need an Absences table with columns StudentID and AbsenceDate, the first a
long integer number (this assumes that the primary key of Students is a
numeric StudentID column), the second of date/time data type. The two
columns should be made the composite primary key of the table.

In form bound to the Students table add your unbound check box, chkAbsent
say. In the form's Current event procedure set its value to False with:

me.chkAbsent = False

In the check box's AfterUpdate event procedure insert a row into the
Absences table with:

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "INSERT INTO Absences (StudentID, AbsenceDate) " & _
"VALUES(" & Me.StudentID & ", #" & Format(VBA.Date,"yyyy-mm-dd") & "#)"

dbs.Execute strSQL

By virtue of the two columns being the primary key of the Absences table it
will only be possible to enter insert one row per student/date no matter how
many times the check box might be checked in a single day. If you simply
incremented the value of a numeric column it would be difficult to prevent
the same absence inadvertently being recorded more than once.

To count the number of absences per student requires a simple query, e.g.

SELECT StudentID, FirstName, LastName,
NZ(COUNT(*),0) AS NumberOfAbsences
FROM Students LEFT JOIN Absences
ON Students.StudentID = Absences.StudentID
GROUP BY StudentID, FirstName, LastName;

As the absences are recorded by date you could of course refine this by
restricting the results to a date range, using parameters to enter the start
and end of the range, but bear in mind that this would, unlike the
unrestricted query above, not return students with zero absences in the
range. This is because the above query uses a LEFT OUTER JOIN, but its not
possible to restrict a query on a column in the table on the right side of
such a join. Doing so in effect turns it into an INNER JOIN.

The above method would fulfil your requirements but it does depend on the
assumptions made in my first paragraph above always holding true. I would
not be too confident about it being possible to guarantee this, so I'd prefer
embedding a subform based on the Absences table in the main students form,
linking them on StudentID, and in the subform, which would be in continuous
form or datasheet view, including a combo box, cboAbsences, bound to the
AbsenceDate column. The combo box could be set to list the dates from 5 days
before to five days after the current date for instance by putting the
following in the subform's Open event procedure:

Dim n As Integer

Me.cboAbsences.RowSourceType = "Value List"

For n = -5 To 5
Me.cboAbsences.AddItem DateAdd("d", n, VBA.Date)
Next n

Make sure the combo box's LimitToList property is set to False (No) so that
a user can type in a date outside the + or -5 days range if necessary.

To record an absence for a student its simply a question of inserting a new
row in the subform by selecting a date from the combo box or typing one in if
outside the range. If a user incorrectly tries to enter one which is already
recorded this would raise a key violation error and they'd be prevented from
doing so.

Ken Sheridan
Stafford, England