學生通訊錄管理系統(tǒng) Access數(shù)據(jù)庫系統(tǒng) 設計資料 VBA代碼分享
設計資料
功能模塊圖
E-R圖
VBA代碼
系統(tǒng)主頁
Private Sub Command參數(shù)設置_Click()
DoCmd.OpenForm "參數(shù)設置", acNormal
End Sub
Private Sub Command聯(lián)系人查詢_Click()
DoCmd.OpenForm "聯(lián)系人查詢", acNormal
End Sub
Private Sub Command聯(lián)系人管理_Click()
DoCmd.OpenForm "聯(lián)系人管理", acNormal
End Sub
Private Sub Command聯(lián)系人添加_Click()
DoCmd.OpenForm "聯(lián)系人添加", acNormal
End Sub
Private Sub Command退出系統(tǒng)_Click()
If MsgBox("是否退出該系統(tǒng)？", vbYesNo) = vbYes Then
Application.Quit acQuitSaveAll
End If
End Sub
Private Sub Command學生信息查詢_Click()
DoCmd.OpenForm "學生信息查詢", acNormal
End Sub
Private Sub Command學生信息管理_Click()
DoCmd.OpenForm "學生信息管理", acNormal
End Sub
Private Sub Command學生信息添加_Click()
DoCmd.OpenForm "學生信息添加", acNormal
End Sub
參數(shù)設置
Private Sub Form_Load()
DoCmd.SetWarnings (True)
End Sub
關系數(shù)據(jù)表
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
Exit Sub
數(shù)據(jù)更新前提醒_Err:
MsgBox Err.Description
End Sub
聯(lián)系人查詢
Private Sub Command查詢_Click()
On Error GoTo 結束查詢
Dim xs_filter As String
If Me.查詢類型 = "日期" Then
If 起始日期 <> "" And IsNull(起始日期) = False And 截止日期 <> "" And IsNull(截止日期) = False And 查詢類型 <> "" And IsNull(查詢類型) = False Then
xs_filter = Me.查詢類型 & " between #" & Me.起始日期 & "# and #" & Me.截止日期 & "#"
Me.數(shù)據(jù)表子窗體.Form.Filter = xs_filter
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
xs_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Me.數(shù)據(jù)表子窗體.SetFocus
Exit Sub
End If
If Me.查詢類型 = "數(shù)值" Then
If 最小 <> "" And IsNull(最小) = False And 最大 <> "" And IsNull(最大) = False And 查詢類型 <> "" And IsNull(查詢類型) = False Then
xs_filter = Me.查詢類型 & " >= " & Me.最小 & " And " & Me.查詢類型 & " <= " & Me.最大
Me.數(shù)據(jù)表子窗體.Form.Filter = xs_filter
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
xs_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Me.數(shù)據(jù)表子窗體.SetFocus
Exit Sub
End If
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢類型 <> "" And IsNull(查詢類型) = False Then
xs_filter = Me.查詢類型 & " like '*" & Me.查詢內(nèi)容 & "*'"
Me.數(shù)據(jù)表子窗體.Form.Filter = xs_filter
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
xs_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Me.數(shù)據(jù)表子窗體.SetFocus
Exit Sub
結束查詢:
MsgBox Err.Description
End Sub
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
Private Sub Command生成報表_Click()
If Me.數(shù)據(jù)表子窗體.Form.FilterOn = False Then
DoCmd.OpenReport "聯(lián)系人標簽報表", acViewReport
Else
DoCmd.OpenReport "聯(lián)系人標簽報表", acViewReport, , Me.數(shù)據(jù)表子窗體.Form.Filter
End If
End Sub
Private Sub Command添加聯(lián)系人_Click()
DoCmd.OpenForm "聯(lián)系人添加", acNormal
End Sub
Private Sub Form_Load()
Me.查詢內(nèi)容.Visible = True
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
End Sub
Private Sub 查詢類型_Change()
If Me.查詢類型 = "日期" Then
Me.起始日期.Visible = True
Me.截止日期.Visible = True
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
If Me.查詢類型 = "數(shù)值" Then
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = True
Me.最大.Visible = True
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
End Sub
聯(lián)系人查詢數(shù)據(jù)表
Private Sub 聯(lián)系人ID_DblClick(Cancel As Integer)
DoCmd.OpenForm "聯(lián)系人管理", acNormal, , "聯(lián)系人ID=" & 聯(lián)系人ID
End Sub
聯(lián)系人管理
Private Sub Command更新_Click()
If 學號.Value <> "" And 聯(lián)系人姓名.Value <> "" And 關系.Value <> "" And 聯(lián)系人電話.Value <> "" Then
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "學號，聯(lián)系人電話，關系，聯(lián)系人姓名不能為空"
On Error Resume Next
DoCmd.RunCommand acCmdUndo
Exit Sub
End If
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("是否刪除該聯(lián)系人記錄？", vbYesNo) = vbYes 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)
If 學號.Value <> "" And 聯(lián)系人姓名.Value <> "" And 關系.Value <> "" And 聯(lián)系人電話.Value <> "" Then
On Error GoTo 數(shù)據(jù)更新前提醒_Err
If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
Beep
Else
DoCmd.RunCommand acCmdUndo
End If
Else
MsgBox "學號，聯(lián)系人電話，關系，聯(lián)系人姓名不能為空"
On Error Resume Next
DoCmd.RunCommand acCmdUndo
Exit Sub
End If
數(shù)據(jù)更新前提醒_Exit:
Exit Sub
數(shù)據(jù)更新前提醒_Err:
MsgBox Error$
Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
Private Sub Form_Close()
On Error Resume Next
Forms("聯(lián)系人查詢").Form.數(shù)據(jù)表子窗體.Requery
End Sub
聯(lián)系人添加
Private Sub Command清空_Click()
學號.Value = ""
聯(lián)系人姓名.Value = ""
關系.Value = ""
聯(lián)系人電話.Value = ""
其他聯(lián)系方式.Value = ""
備注.Value = ""
End Sub
Private Sub Command添加_Click()
On Error GoTo 添加失敗
If 學號 = "" Or IsNull(學號) = True Then
MsgBox "學號值為空！"
Exit Sub
End If
If 聯(lián)系人姓名 = "" Or IsNull(聯(lián)系人姓名) = True Then
MsgBox "聯(lián)系人姓名值為空！"
Exit Sub
End If
If 關系 = "" Or IsNull(關系) = True Then
MsgBox "關系值為空！"
Exit Sub
End If
If 聯(lián)系人電話 = "" Or IsNull(聯(lián)系人電話) = True Then
MsgBox "聯(lián)系人電話值為空！"
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("聯(lián)系人表", dbOpenTable)
With add_rs
.AddNew
!學號.Value = 學號.Value
!聯(lián)系人姓名.Value = 聯(lián)系人姓名.Value
!關系.Value = 關系.Value
!聯(lián)系人電話.Value = 聯(lián)系人電話.Value
!其他聯(lián)系方式.Value = 其他聯(lián)系方式.Value
!備注.Value = 備注.Value
.Update
.Close
End With
Set add_rs = Nothing
MsgBox "添加完成！"
Exit Sub
添加失敗:
MsgBox Err.Description
End Sub
Private Sub Form_Close()
On Error Resume Next
Forms("聯(lián)系人查詢").Form.數(shù)據(jù)表子窗體.Requery
End Sub
學生信息查詢
Private Sub Command查詢_Click()
On Error GoTo 結束查詢
Dim xs_filter As String
If Me.查詢類型 = "出生日期" Then
If 起始日期 <> "" And IsNull(起始日期) = False And 截止日期 <> "" And IsNull(截止日期) = False And 查詢類型 <> "" And IsNull(查詢類型) = False Then
xs_filter = Me.查詢類型 & " between #" & Me.起始日期 & "# and #" & Me.截止日期 & "#"
Me.數(shù)據(jù)表子窗體.Form.Filter = xs_filter
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
xs_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Me.數(shù)據(jù)表子窗體.SetFocus
Exit Sub
End If
If Me.查詢類型 = "年齡" Then
If 最小 <> "" And IsNull(最小) = False And 最大 <> "" And IsNull(最大) = False And 查詢類型 <> "" And IsNull(查詢類型) = False Then
xs_filter = Me.查詢類型 & " >= " & Me.最小 & " And " & Me.查詢類型 & " <= " & Me.最大
Me.數(shù)據(jù)表子窗體.Form.Filter = xs_filter
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
xs_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Me.數(shù)據(jù)表子窗體.SetFocus
Exit Sub
End If
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢類型 <> "" And IsNull(查詢類型) = False Then
xs_filter = Me.查詢類型 & " like '*" & Me.查詢內(nèi)容 & "*'"
Me.數(shù)據(jù)表子窗體.Form.Filter = xs_filter
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
xs_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Me.數(shù)據(jù)表子窗體.SetFocus
Exit Sub
結束查詢:
MsgBox Err.Description
End Sub
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
Private Sub Command生成報表_Click()
If Me.數(shù)據(jù)表子窗體.Form.FilterOn = False Then
DoCmd.OpenReport "學生信息報表", acViewReport
Else
DoCmd.OpenReport "學生信息報表", acViewReport, , Me.數(shù)據(jù)表子窗體.Form.Filter
End If
End Sub
Private Sub Command添加學生_Click()
DoCmd.OpenForm "學生信息添加", acNormal
End Sub
Private Sub Form_Load()
Me.查詢內(nèi)容.Visible = True
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
End Sub
Private Sub 查詢類型_Change()
If Me.查詢類型 = "出生日期" Then
Me.起始日期.Visible = True
Me.截止日期.Visible = True
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
If Me.查詢類型 = "年齡" Then
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = True
Me.最大.Visible = True
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
End Sub
學生信息查詢數(shù)據(jù)表
Private Sub 學號_DblClick(Cancel As Integer)
DoCmd.OpenForm "學生信息管理", acNormal, , "學號='" & 學號 & "'"
End Sub
學生信息管理
Private Sub Command報表_Click()
DoCmd.OpenReport "學生聯(lián)系人報表", acViewReport, , "學號='" & Me.學號 & "'"
End Sub
Private Sub Command更新_Click()
If 學號.Value <> "" And 姓名.Value <> "" And 性別.Value <> "" And 班級.Value <> "" And 專業(yè).Value <> "" And 出生日期.Value <> "" And 家庭地址.Value <> "" Then
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "學號，姓名，性別，班級，專業(yè)，出生日期和家庭地址不能為空"
On Error Resume Next
DoCmd.RunCommand acCmdUndo
Exit Sub
End If
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("是否刪除該學生信息？注意：刪除學生信息后涉及該學生聯(lián)系人也會被刪除！", vbYesNo) = vbYes 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)
If 學號.Value <> "" And 姓名.Value <> "" And 性別.Value <> "" And 班級.Value <> "" And 專業(yè).Value <> "" And 出生日期.Value <> "" And 家庭地址.Value <> "" Then
On Error GoTo 數(shù)據(jù)更新前提醒_Err
If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
Beep
Else
DoCmd.RunCommand acCmdUndo
End If
Else
MsgBox "學號，姓名，性別，班級，專業(yè)，出生日期和家庭地址不能為空"
On Error Resume Next
DoCmd.RunCommand acCmdUndo
Exit Sub
End If
數(shù)據(jù)更新前提醒_Exit:
Exit Sub
數(shù)據(jù)更新前提醒_Err:
MsgBox Error$
Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
Private Sub Form_Close()
On Error Resume Next
Forms("學生信息查詢").Form.數(shù)據(jù)表子窗體.Requery
End Sub
學生信息添加
Private Sub Command清空_Click()
學號.Value = ""
姓名.Value = ""
性別.Value = ""
班級.Value = ""
專業(yè).Value = ""
出生日期.Value = ""
家庭地址.Value = ""
備注.Value = ""
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 班級 = "" Or IsNull(班級) = True Then
MsgBox "班級值為空！"
Exit Sub
End If
If 專業(yè) = "" Or IsNull(專業(yè)) = True Then
MsgBox "專業(yè)值為空！"
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
!班級.Value = 班級.Value
!專業(yè).Value = 專業(yè).Value
!出生日期.Value = 出生日期.Value
!家庭地址.Value = 家庭地址.Value
!備注.Value = 備注.Value
.Update
.Close
End With
Set add_rs = Nothing
MsgBox "添加完成！"
Exit Sub
添加失敗:
MsgBox Err.Description
End Sub
Private Sub Form_Close()
On Error Resume Next
Forms("學生信息查詢").Form.數(shù)據(jù)表子窗體.Requery
End Sub
專業(yè)數(shù)據(jù)表
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
Exit Sub
數(shù)據(jù)更新前提醒_Err:
MsgBox Err.Description
End Sub
聯(lián)系人標簽報表
Private Sub Report_Load()
On Error Resume Next
If Forms("聯(lián)系人查詢").數(shù)據(jù)表子窗體.Form.OrderByOn = True Then
Me.OrderBy = Forms("聯(lián)系人查詢").數(shù)據(jù)表子窗體.Form.OrderBy
Me.OrderByOn = True
Else
Me.OrderByOn = False
End If
End Sub
學生信息報表
Private Sub Report_Load()
On Error Resume Next
If Forms("學生信息查詢").數(shù)據(jù)表子窗體.Form.OrderByOn = True Then
Me.OrderBy = Forms("學生信息查詢").數(shù)據(jù)表子窗體.Form.OrderBy
Me.OrderByOn = True
Else
Me.OrderByOn = False
End If
End Sub
