Creating an Attendance DB

[sopomeres]

I'm looking to create an Access DB for posting attendance for students. How can I set up a form to show the following: one 'Date' (top of page), list of all 'Students' from a table, and an attendance 'Result' per student, e.g. absent, present. I'd like to have the the 'Result' field cleared (and saved) whenever the user changes the 'Date' at the top of the page.

How can I achieve this? Should I create 3 tables? DATE, STUDENTS, and RESULT. If so, what type of relationship?

Thank you.

 

[cuok]

Hi

send me your e-mail - i have a perfect one!

my email: cuok@bezeqint.net

i have to went to 4-5 hours so be patient pls !
CUOK

 

[cuok]

i CAN'T BE HERE FOR THE NEXT 24 HOURS SO I'M ATTACHING ALL THE FORM CODE - U DIDN'T SEND ME YOUR E-MAIL I'LL SEND ALL THE PROGRAM (34k) IF I GET YOUR E-MAIL:

I HOPE IT CAN HELP U SOMEWAY.

***************** CODE ******************
Option Compare Database
Option Explicit

Function ThisIs()
Dim TDate As Date, C1 As Integer, StrSQL As String, TypeAttend, RecDetect
C1 = 1: TDate = Me![scr1Date]
Do Until C1 = CInt(Mid(ActiveControl.Name, 3, 2))
TDate = DateAdd("d", 1, TDate)
C1 = C1 + 1
Loop

TypeAttend = DLookup("AttType", "Attend", "[AttPatient] = " & Me![scrStudent] & " AND [AttDate] = #" & Format(TDate, "mm/dd/yy&quot & "#&quot
If IsNull(TypeAttend) Then
TypeAttend = 0
End If
TypeAttend = TypeAttend + 1
If TypeAttend > 3 Then
TypeAttend = 0
End If

RecDetect = DLookup("[scrStudent]", "Attend", "[AttPatient] = " & Me![scrStudent] & " AND [AttDate] = #" & Format(TDate, "mm/dd/yy&quot & "#&quot
If IsNull(RecDetect) Then
StrSQL = "INSERT INTO Attend ( AttPatient, AttDate, AttType ) " _
& "SELECT " & Me![scrStudent] & " AS F1, #" _
& Format(TDate, "mm/dd/yy&quot & "# AS F2, " & TypeAttend & " AS F3;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE Attend SET Attend.AttType = " & TypeAttend _
& " WHERE (((Attend.AttPatient)=" & Me![scrStudent] & &quot AND" _
& "((Attend.AttDate)=#" & Format(TDate, "mm/dd/yy&quot & "#));"
DoCmd.RunSQL StrSQL
End If
Call RefDates
End Function

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyPageDown Then
Me![scrCDate] = DateAdd("m", 1, Me![scrCDate])
End If
If KeyCode = vbKeyPageUp Then
Me![scrCDate] = DateAdd("m", -1, Me![scrCDate])
End If
Call RefDates
End Sub

Private Sub Form_Open(Cancel As Integer)
Me![scrCDate] = DateSerial(Year(Date), Month(Date), 1)
Me![scrMonth] = Format(Date, "mmmm&quot
Me![scrYear] = Format(Date, "yyyy&quot
End Sub
Sub RefDates()
Dim D1 As Variant, D2 As Integer, D3 As Integer, TypeAttend
If IsNull(Me![scrStudent]) Then
MsgBox ("Selection error.@Displaying calendar data can only be done for a specific " _
& "student.@Select a student and continue.&quot
Exit Sub
End If
Me![scrMonth] = Format(Me![scrCDate], "mmmm&quot
Me![scrYear] = Format(Me![scrCDate], "yyyy&quot
D1 = DateSerial(Year(Me![scrCDate]), Month(Me![scrCDate]), 1)
D2 = DatePart("w", D1, vbMonday)
Do Until DatePart("w", D1, vbMonday) = 1
D1 = DateAdd("d", -1, D1)
Loop
Me![scr1Date] = D1
D3 = 1
Do Until D3 > 42
Me("C" & Format(D3, "00&quot) = Day(D1)
If Month(D1) <> Month(Me![scrCDate]) Then
Me(&quot;C&quot; & Format(D3, &quot;00&quot).ForeColor = 8421504
Else
Me(&quot;C&quot; & Format(D3, &quot;00&quot).ForeColor = 0
'If Me(strt).ForeColor = -2147483634 Then
End If
TypeAttend = DLookup(&quot;AttType&quot;, &quot;Attend&quot;, &quot;[AttPatient] = &quot; & Me![scrStudent] & &quot; AND [AttDate] = #&quot; & Format(D1, &quot;mm/dd/yy&quot & &quot;#&quot
If IsNull(TypeAttend) Then
TypeAttend = 0
End If
Select Case TypeAttend
Case 0
Me(&quot;C&quot; & Format(D3, &quot;00&quot).BackColor = 12632256
Case 1
Me(&quot;C&quot; & Format(D3, &quot;00&quot).BackColor = 65280
Case 2
Me(&quot;C&quot; & Format(D3, &quot;00&quot).BackColor = 255
Case Else
Me(&quot;C&quot; & Format(D3, &quot;00&quot).BackColor = 3355443
Me(&quot;C&quot; & Format(D3, &quot;00&quot).ForeColor = 16777215
End Select
D3 = D3 + 1
D1 = DateAdd(&quot;d&quot;, 1, D1)
Loop
Me.Repaint
End Sub
Private Sub scrStudent_AfterUpdate()
Call RefDates
End Sub
Private Sub Command107_Click()
On Error GoTo Err_Command107_Click


DoCmd.Close

Exit_Command107_Click:
Exit Sub

Err_Command107_Click:
MsgBox Err.Description
Resume Exit_Command107_Click

End Sub
*****************END CODE ******************

CUOK