Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating an Attendance DB

Status
Not open for further replies.

sopomeres

MIS
Jan 16, 2003
61
US
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.
 
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
 

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") & "#")
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") & "#")
If IsNull(RecDetect) Then
StrSQL = "INSERT INTO Attend ( AttPatient, AttDate, AttType ) " _
& "SELECT " & Me![scrStudent] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE Attend SET Attend.AttType = " & TypeAttend _
& " WHERE (((Attend.AttPatient)=" & Me![scrStudent] & ") AND" _
& "((Attend.AttDate)=#" & Format(TDate, "mm/dd/yy") & "#));"
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")
Me![scrYear] = Format(Date, "yyyy")
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.")
Exit Sub
End If
Me![scrMonth] = Format(Me![scrCDate], "mmmm")
Me![scrYear] = Format(Me![scrCDate], "yyyy")
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")) = 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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top