課程簽到管理系統(tǒng)-VBA代碼
教師管理
Option Compare Database
Option Explicit
?
Private Sub Command清空_Click()
教師姓名.Value = ""
教師編號.Value = ""
性別.Value = ""
學(xué)院.Value = ""
職位.Value = ""
職務(wù).Value = ""
聯(lián)系方式.Value = ""
備注.Value = ""
End Sub
?
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
End Sub
?
Private Sub Command添加_Click()
On Error GoTo 添加失敗錯誤
?
?
If 教師姓名 = "" Or IsNull(教師姓名) = True Then
MsgBox "教師姓名值為空！"
Exit Sub
End If
If 教師編號 = "" Or IsNull(教師編號) = True Then
MsgBox "教師編號值為空！"
Exit Sub
End If
?
?
If Nz(DCount("教師姓名", "教師表", "教師姓名='" & Me.教師姓名 & "'"), 0) > 0 Then
MsgBox "該教師姓名已存在！請輸入其他教師姓名"
Exit Sub
End If
'================================================================
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("教師表", dbOpenTable)
With add_rs
.AddNew
!教師姓名.Value = 教師姓名.Value
!教師編號.Value = 教師編號.Value
!性別.Value = 性別.Value
!學(xué)院.Value = 學(xué)院.Value
!職位.Value = 職位.Value
!職務(wù).Value = 職務(wù).Value
!聯(lián)系方式.Value = 聯(lián)系方式.Value
!備注.Value = 備注.Value
.Update
.Close
End With
Set add_rs = Nothing
?'================================================================
?
MsgBox "添加成功！"
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
添加失敗錯誤:
MsgBox "添加失??！"
MsgBox Err.Description
End Sub
?
教師數(shù)據(jù)表
Option Compare Database
Option Explicit
?
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
??? If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
?
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
?
Private Sub 教師姓名_DblClick(Cancel As Integer)
DoCmd.OpenForm "教師信息更新刪除", acNormal, , "教師姓名='" & 教師姓名 & "'"
End Sub
?
教師信息更新刪除
Option Compare Database
Option Explicit
?
Private Sub Command保存_Click()
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If Error.Number <> 0 Then
MsgBox Error.Description
Else
MsgBox "保存成功"
End If
End Sub
?
Private Sub Command撤銷_Click()
On Error Resume Next
DoCmd.RunCommand acCmdUndo
If Error.Number <> 0 Then
MsgBox Error.Description
End If
End Sub
?
Private Sub Command刪除_Click()
On Error Resume Next
DoCmd.SetWarnings (False)
If MsgBox("是否刪除該記錄", vbOKCancel) = vbOK Then
DoCmd.RunCommand acCmdDeleteRecord
MsgBox "刪除成功"
DoCmd.Close acForm, Me.Name
Else
Exit Sub
End If
If Error.Number <> 0 Then
MsgBox Error.Description
End If
End Sub
?
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
??? If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
?
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
?
Private Sub Form_Close()
Forms("教師管理").Form.數(shù)據(jù)表子窗體.Requery
End Sub
課程管理
Option Compare Database
Option Explicit
?
Private Sub Command清空_Click()
課程編號.Value = ""
課程名稱.Value = ""
任課教師.Value = ""
上課地點(diǎn).Value = ""
上課周數(shù).Value = ""
上課時間.Value = ""
備注.Value = ""
End Sub
?
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
End Sub
?
Private Sub Command添加_Click()
On Error GoTo 添加失敗錯誤
?
?
If 課程編號 = "" Or IsNull(課程編號) = True Then
MsgBox "課程編號值為空！"
Exit Sub
End If
If 課程名稱 = "" Or IsNull(課程名稱) = True Then
MsgBox "課程名稱值為空！"
Exit Sub
End If
If 任課教師 = "" Or IsNull(任課教師) = True Then
MsgBox "任課教師值為空！"
Exit Sub
End If
?
If Nz(DCount("課程編號", "課程表", "課程編號='" & Me.課程編號 & "'"), 0) > 0 Then
MsgBox "該課程編號已存在！請輸入其他課程編號"
Exit Sub
End If
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("課程表", dbOpenTable)
With add_rs
.AddNew
!課程編號.Value = 課程編號.Value
!課程名稱.Value = 課程名稱.Value
!任課教師.Value = 任課教師.Value
!上課地點(diǎn).Value = 上課地點(diǎn).Value
!上課周數(shù).Value = 上課周數(shù).Value
!上課時間.Value = 上課時間.Value
!備注.Value = 備注.Value
.Update
.Close
End With
Set add_rs = Nothing
?
?
MsgBox "添加成功！"
?
Me.數(shù)據(jù)表子窗體.Requery
?
Exit Sub
添加失敗錯誤:
MsgBox "添加失??！"
MsgBox Err.Description
End Sub
課程簽到情況
Option Compare Database
Option Explicit
?
Private Sub Command全部_Click()
Me.選擇課程 = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體2.Form.FilterOn = False
End Sub
?
Private Sub Command生成報表_Click()
If Me.選擇課程 <> "" Then
DoCmd.OpenReport "課程簽到情況報表", acViewReport, , "課程簽到情況匯總統(tǒng)計查詢_課程編號='" & Me.選擇課程 & "'"
Else
DoCmd.OpenReport "課程簽到情況報表", acViewReport
End If
End Sub
?
Private Sub 選擇課程_Change()
Me.數(shù)據(jù)表子窗體.Form.Filter = "課程編號='" & Me.選擇課程 & "'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體2.Form.Filter = "課程編號='" & Me.選擇課程 & "'"
Me.數(shù)據(jù)表子窗體2.Form.FilterOn = True
End Sub
?
課程數(shù)據(jù)表
Option Compare Database
Option Explicit
?
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
??? If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
?
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
?
Private Sub 課程編號_DblClick(Cancel As Integer)
DoCmd.OpenForm "課程信息更新刪除", acNormal, , "課程編號='" & 課程編號 & "'"
End Sub
課程信息更新刪除
Option Compare Database
Option Explicit
?
Private Sub Command保存_Click()
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If Error.Number <> 0 Then
MsgBox Error.Description
Else
MsgBox "保存成功"
End If
End Sub
?
Private Sub Command撤銷_Click()
On Error Resume Next
DoCmd.RunCommand acCmdUndo
If Error.Number <> 0 Then
MsgBox Error.Description
End If
End Sub
?
Private Sub Command刪除_Click()
On Error Resume Next
DoCmd.SetWarnings (False)
If MsgBox("是否刪除該記錄", vbOKCancel) = vbOK Then
DoCmd.RunCommand acCmdDeleteRecord
MsgBox "刪除成功"
DoCmd.Close acForm, Me.Name
Else
Exit Sub
End If
If Error.Number <> 0 Then
MsgBox Error.Description
End If
End Sub
?
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
??? If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
?
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
?
Private Sub Form_Close()
Forms("課程管理").Form.數(shù)據(jù)表子窗體.Requery
End Sub
簽到管理
Option Compare Database
Option Explicit
?
Private Sub Command查詢_Click()
Me.數(shù)據(jù)表子窗體.Form.Filter = "日期=#" & Me.日期 & "# and 課程編號='" & Me.選擇課程 & "'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
Private Sub Command生成簽到記錄_Click()
If 日期 = "" Or IsNull(日期) = True Then
MsgBox "日期值為空！"
Exit Sub
End If
If 選擇課程 = "" Or IsNull(選擇課程) = True Then
MsgBox "選擇課程值為空！"
Exit Sub
End If
If 上課時間 = "" Or IsNull(上課時間) = True Then
MsgBox "選擇課程值為空！"
Exit Sub
End If
Dim search_rs As DAO.Recordset
Dim search_sql As String
search_sql = "Select * From 選課表 Where 課程編號='" & Me.選擇課程 & "'"
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("簽到記錄表", dbOpenTable)
With add_rs
Do While search_rs.EOF = False
'----------------------------------生成簽到記錄
?
.AddNew
?
!日期.Value = 日期.Value
!課程編號.Value = 選擇課程
!學(xué)號.Value = search_rs!學(xué)號.Value
!上課時間.Value = 上課時間.Value
!遲到.Value = False
!早退.Value = False
!請假.Value = False
!曠課.Value = False
.Update
search_rs.MoveNext
Loop
.Close
End With
Set add_rs = Nothing
search_rs.Close
Set search_rs = Nothing
Me.數(shù)據(jù)表子窗體.Form.Filter = "日期=#" & Me.日期 & "# and 課程編號='" & Me.選擇課程 & "'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
Private Sub Form_Load()
Me.日期 = Date
Me.上課時間 = Time
End Sub
簽到記錄查詢數(shù)據(jù)表
Option Compare Database
Option Explicit
?
Private Sub 簽到ID_DblClick(Cancel As Integer)
If MsgBox("是否刪除該選課記錄", vbOKCancel) = vbOK Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 簽到記錄表 Where? 簽到ID=" & 簽到ID
DoCmd.RunSQL del_sql
Forms("簽到管理").數(shù)據(jù)表子窗體.Requery
End If
End Sub
?
Private Sub 簽到時間_DblClick(Cancel As Integer)
Me.簽到時間 = Time
End Sub
系統(tǒng)登錄
Option Compare Database
Option Explicit
?
Private Sub 登錄_Click()
If 賬號 <> "" And 密碼 <> "" Then
??? If Me.密碼 = DLookup("密碼", "賬號密碼表", "賬號='" & Me.賬號 & "'") Then?? '修改域函數(shù)參數(shù)
??????? MsgBox "登錄成功"
??????? 學(xué)生管理權(quán)限 = DLookup("學(xué)生管理", "賬號密碼表", "賬號='" & Me.賬號 & "'")
??????? 教師管理權(quán)限 = DLookup("教師管理", "賬號密碼表", "賬號='" & Me.賬號 & "'")
??????? 課程管理權(quán)限 = DLookup("課程管理", "賬號密碼表", "賬號='" & Me.賬號 & "'")
??????? 選課管理權(quán)限 = DLookup("選課管理", "賬號密碼表", "賬號='" & Me.賬號 & "'")
??????? 簽到管理權(quán)限 = DLookup("簽到管理", "賬號密碼表", "賬號='" & Me.賬號 & "'")
??????? 課程簽到情況查詢權(quán)限 = DLookup("課程簽到情況查詢", "賬號密碼表", "賬號='" & Me.賬號 & "'")
??????? 學(xué)生簽到情況查詢權(quán)限 = DLookup("學(xué)生簽到情況查詢", "賬號密碼表", "賬號='" & Me.賬號 & "'")
??????? DoCmd.OpenForm "系統(tǒng)主頁", acNormal, , "賬號='" & Me.賬號 & "'"
??????? DoCmd.Close acForm, Me.Name
??? Else
??????? MsgBox "賬號或密碼錯誤"
??? End If
Else
MsgBox "請輸入賬號和密碼"
End If
End Sub
?
Private Sub 退出_Click()
Application.Quit
End Sub
系統(tǒng)主頁
Option Compare Database
Option Explicit
?
Private Sub Command教師管理_Click()
If 教師管理權(quán)限 = True Then
DoCmd.OpenForm "教師管理", acNormal
Else
MsgBox "無權(quán)限"
Exit Sub
End If
End Sub
?
Private Sub Command課程管理_Click()
If 教師管理權(quán)限 = True Then
DoCmd.OpenForm "課程管理", acNormal
Else
MsgBox "無權(quán)限"
Exit Sub
End If
End Sub
?
Private Sub Command課程簽到情況_Click()
If 課程簽到情況查詢權(quán)限 = True Then
DoCmd.OpenForm "課程簽到情況", acNormal
Else
MsgBox "無權(quán)限"
Exit Sub
End If
End Sub
?
Private Sub Command簽到管理_Click()
If 簽到管理權(quán)限 = True Then
DoCmd.OpenForm "簽到管理", acNormal
Else
MsgBox "無權(quán)限"
Exit Sub
End If
End Sub
?
Private Sub Command退出系統(tǒng)_Click()
Application.Quit acQuitSaveAll
End Sub
?
Private Sub Command選課管理_Click()
If 選課管理權(quán)限 = True Then
DoCmd.OpenForm "選課管理", acNormal
Else
MsgBox "無權(quán)限"
Exit Sub
End If
End Sub
?
Private Sub Command學(xué)生管理_Click()
If 學(xué)生管理權(quán)限 = True Then
DoCmd.OpenForm "學(xué)生管理", acNormal
Else
MsgBox "無權(quán)限"
Exit Sub
End If
End Sub
?
Private Sub Command學(xué)生簽到情況_Click()
If 學(xué)生簽到情況查詢權(quán)限 = True Then
DoCmd.OpenForm "學(xué)生簽到情況", acNormal
Else
MsgBox "無權(quán)限"
Exit Sub
End If
End Sub
選課管理
Option Compare Database
Option Explicit
Private Sub 選擇課程_Change()
Me.Filter = "課程編號='" & Me.選擇課程 & "'"
Me.FilterOn = True
'---------計算選課人數(shù)
Me.選課人數(shù) = Nz(DCount("選課ID", "選課表", "課程編號='" & Me.課程編號 & "'"), 0)
End Sub
學(xué)生管理
Option Compare Database
Option Explicit
?
Private Sub Command清空_Click()
學(xué)號.Value = ""
姓名.Value = ""
性別.Value = ""
班級.Value = ""
專業(yè).Value = ""
學(xué)院.Value = ""
聯(lián)系方式.Value = ""
備注.Value = ""
End Sub
?
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
End Sub
?
Private Sub Command添加_Click()
On Error GoTo 添加失敗錯誤
?
?
If 學(xué)號 = "" Or IsNull(學(xué)號) = True Then
MsgBox "學(xué)號值為空！"
Exit Sub
End If
If 姓名 = "" Or IsNull(姓名) = True Then
MsgBox "姓名值為空！"
Exit Sub
End If
?
If Nz(DCount("學(xué)號", "學(xué)生表", "學(xué)號='" & Me.學(xué)號 & "'"), 0) > 0 Then
MsgBox "該學(xué)號已存在！請?zhí)砑悠渌麑W(xué)號"
Exit Sub
End If
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("學(xué)生表", dbOpenTable)
With add_rs
.AddNew
!學(xué)號.Value = 學(xué)號.Value
!姓名.Value = 姓名.Value
!性別.Value = 性別.Value
!班級.Value = 班級.Value
!專業(yè).Value = 專業(yè).Value
!學(xué)院.Value = 學(xué)院.Value
!聯(lián)系方式.Value = 聯(lián)系方式.Value
!備注.Value = 備注.Value
.Update
.Close
End With
Set add_rs = Nothing
?
?
MsgBox "添加成功！"
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
添加失敗錯誤:
MsgBox "添加失??！"
MsgBox Err.Description
End Sub
學(xué)生簽到情況
Option Compare Database
Option Explicit
?
Private Sub Command全部_Click()
Me.選擇學(xué)生 = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體2.Form.FilterOn = False
End Sub
?
Private Sub Command生成報表_Click()
If Me.選擇學(xué)生 <> "" Then
DoCmd.OpenReport "學(xué)生簽到情況報表", acViewReport, , "學(xué)號='" & Me.選擇學(xué)生 & "'"
Else
DoCmd.OpenReport "學(xué)生簽到情況報表", acViewReport
End If
End Sub
?
?
?
Private Sub 選擇學(xué)生_Change()
Me.數(shù)據(jù)表子窗體.Form.Filter = "學(xué)號='" & Me.選擇學(xué)生 & "'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體2.Form.Filter = "學(xué)號='" & Me.選擇學(xué)生 & "'"
Me.數(shù)據(jù)表子窗體2.Form.FilterOn = True
End Sub
學(xué)生數(shù)據(jù)表
Option Compare Database
Option Explicit
?
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
??? If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
?
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
?
Private Sub 學(xué)號_DblClick(Cancel As Integer)
DoCmd.OpenForm "學(xué)生信息更新刪除", acNormal, , "學(xué)號='" & 學(xué)號 & "'"
End Sub
學(xué)生數(shù)據(jù)表2
Option Compare Database
Option Explicit
?
Private Sub 學(xué)號_DblClick(Cancel As Integer)
If Nz(DCount("選課ID", "選課表", "課程編號='" & Forms("選課管理").課程編號 & "' and 學(xué)號='" & Me.學(xué)號 & "'"), 0) > 0 Then
MsgBox "該學(xué)生已選擇該課程！請勿重復(fù)選擇"
Exit Sub
Else
DoCmd.SetWarnings (False)
Dim add_sql As String
add_sql = "Insert Into 選課表 (課程編號,學(xué)號) Values ('" & Forms("選課管理").課程編號 & "','" & 學(xué)號 & "')"
DoCmd.RunSQL add_sql
Forms("選課管理").Form.數(shù)據(jù)表子窗體2.Requery
Forms("選課管理").選課人數(shù) = Nz(DCount("選課ID", "選課表", "課程編號='" & Forms("選課管理").課程編號 & "'"), 0)
End If
End Sub
學(xué)生信息更新刪除
Option Compare Database
Option Explicit
?
Private Sub Command保存_Click()
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If Error.Number <> 0 Then
MsgBox Error.Description
Else
MsgBox "保存成功"
End If
End Sub
?
Private Sub Command撤銷_Click()
On Error Resume Next
DoCmd.RunCommand acCmdUndo
If Error.Number <> 0 Then
MsgBox Error.Description
End If
End Sub
?
Private Sub Command刪除_Click()
On Error Resume Next
DoCmd.SetWarnings (False)
If MsgBox("是否刪除該記錄", vbOKCancel) = vbOK Then
DoCmd.RunCommand acCmdDeleteRecord
MsgBox "刪除成功"
DoCmd.Close acForm, Me.Name
Else
Exit Sub
End If
If Error.Number <> 0 Then
MsgBox Error.Description
End If
End Sub
?
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
??? If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
?
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
?
Private Sub Form_Close()
Forms("學(xué)生管理").Form.數(shù)據(jù)表子窗體.Requery
End Sub
學(xué)生選課查詢數(shù)據(jù)表
Option Compare Database
Option Explicit
?
Private Sub 選課ID_DblClick(Cancel As Integer)
If MsgBox("是否刪除該選課記錄", vbOKCancel) = vbOK Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 選課表 Where? 選課ID=" & 選課ID
DoCmd.RunSQL del_sql
Forms("選課管理").數(shù)據(jù)表子窗體2.Requery
Forms("選課管理").選課人數(shù) = Nz(DCount("選課ID", "選課表", "課程編號='" & Forms("選課管理").課程編號 & "'"), 0)
End If
End Sub
?
模塊1
Option Compare Database
Option Explicit
?
Public 學(xué)生管理權(quán)限 As Boolean
Public 教師管理權(quán)限 As Boolean
Public 課程管理權(quán)限 As Boolean
Public 選課管理權(quán)限 As Boolean
Public 簽到管理權(quán)限 As Boolean
Public 課程簽到情況查詢權(quán)限 As Boolean
Public 學(xué)生簽到情況查詢權(quán)限 As Boolean
