家庭收支理財管理系統(tǒng) Access數(shù)據(jù)庫系統(tǒng)課程設(shè)計實例 VBA代碼 數(shù)據(jù)庫文件 設(shè)計報告
?本系統(tǒng)包含主要功能有：家庭成員管理，收支管理，理財管理，存款管理，收支統(tǒng)計，理財統(tǒng)計，存款統(tǒng)計，生成報表。
?商品中包含簡要的設(shè)計報告（功能模塊圖，業(yè)務(wù)流程圖，UC矩陣，數(shù)據(jù)流圖，數(shù)據(jù)字典，E-R圖等）和制作截圖文檔。
?數(shù)據(jù)庫系統(tǒng)包含：表，查詢，窗體，報表，VBA代碼
?系統(tǒng)為單機(jī)使用的access數(shù)據(jù)庫系統(tǒng)，可作為課程學(xué)習(xí)實例使用。
設(shè)計部分
功能模塊圖
業(yè)務(wù)流程圖
數(shù)據(jù)流程
E-R圖
程序流程圖
關(guān)系模型
理財（家庭成員，理財項目，理財內(nèi)容，開始日期，結(jié)束日期，本金，收益率，收入，備注）
收支（日期，類型，家庭成員，項目，內(nèi)容，收入金額，支出金額，備注）
家庭成員（姓名，性別，家庭關(guān)系，生日，職業(yè)狀態(tài)，聯(lián)系方式，備注）
存款（賬戶，戶名，存取類型，存入，取出，日期，備注）
系統(tǒng)部分
表關(guān)系
窗體
VBA代碼
系統(tǒng)主頁
Private Sub Command參數(shù)設(shè)置_Click()
Me.顯示界面子窗體.SourceObject = "參數(shù)設(shè)置"
Me.顯示界面子窗體.SetFocus
End Sub
Private Sub Command存款管理_Click()
Me.顯示界面子窗體.SourceObject = "存款管理"
Me.顯示界面子窗體.SetFocus
End Sub
Private Sub Command存款統(tǒng)計_Click()
Me.顯示界面子窗體.SourceObject = "存款統(tǒng)計查詢"
Me.顯示界面子窗體.SetFocus
End Sub
Private Sub Command家庭成員管理_Click()
Me.顯示界面子窗體.SourceObject = "家庭成員管理"
Me.顯示界面子窗體.SetFocus
End Sub
Private Sub Command理財管理_Click()
Me.顯示界面子窗體.SourceObject = "理財管理"
Me.顯示界面子窗體.SetFocus
End Sub
Private Sub Command理財統(tǒng)計_Click()
Me.顯示界面子窗體.SourceObject = "理財統(tǒng)計查詢"
Me.顯示界面子窗體.SetFocus
End Sub
Private Sub Command收支管理_Click()
Me.顯示界面子窗體.SourceObject = "收支管理"
Me.顯示界面子窗體.SetFocus
End Sub
Private Sub Command收支統(tǒng)計查詢_Click()
Me.顯示界面子窗體.SourceObject = "收支統(tǒng)計查詢"
Me.顯示界面子窗體.SetFocus
End Sub
Private Sub Command退出系統(tǒng)_Click()
If MsgBox("是否退出系統(tǒng)", vbYesNo) <> vbYes Then
Exit Sub
End If
Application.Quit acQuitSaveAll
End Sub
Private Sub Command系統(tǒng)后臺_Click()
DoCmd.Close acForm, Me.Name
DoCmd.SelectObject acForm, , True
End Sub
存款管理
Option Compare Database
Private Sub Command報表_Click()
If Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
DoCmd.OpenReport "存款記錄報表", acViewReport, , Me.數(shù)據(jù)表子窗體.Form.Filter
Else
DoCmd.OpenReport "存款記錄報表", acViewReport
End If
End Sub
Private Sub Command查詢_Click()
On Error GoTo 結(jié)束查詢
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
If 查詢字段.Value = "日期" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " =#" & Me.查詢內(nèi)容 & "#"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
If 查詢字段.Value = "存入" Or 查詢字段.Value = "取出" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & ">= " & Me.查詢內(nèi)容
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " like '*" & Me.查詢內(nèi)容 & "*'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Exit Sub
結(jié)束查詢:
MsgBox Err.Description
End Sub
Private Sub Command清空_Click()
賬戶.Value = ""
戶名.Value = ""
存取類型.Value = ""
存入.Value = ""
取出.Value = ""
日期.Value = ""
備注.Value = ""
賬戶余額.Value = ""
End Sub
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
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
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 add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("存款表", dbOpenTable)
With add_rs
.AddNew
!賬戶.Value = 賬戶.Value
!戶名.Value = 戶名.Value
!存取類型.Value = 存取類型.Value
!存入.Value = 存入.Value
!取出.Value = 取出.Value
!日期.Value = 日期.Value
!備注.Value = 備注.Value
.Update
.Close
End With
Set add_rs = Nothing
MsgBox "添加完成"
Me.數(shù)據(jù)表子窗體.Form.Requery
End Sub
Private Sub 賬戶_Change()
If 賬戶 <> "" Then
賬戶余額.Value = Nz(DLookup("余額", "存款統(tǒng)計查詢", "賬戶='" & Me.賬戶 & "'"), 0)
Else
賬戶余額.Value = ""
End If
End Sub
存款數(shù)據(jù)表
Private Sub Form_BeforeUpdate(Cancel As Integer)
If 賬戶.Value <> "" And 戶名.Value <> "" And 存取類型.Value <> "" And 存入.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 "賬戶，戶名，存取類型，存入，取出，日期都不能為空"
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 存款I(lǐng)D_DblClick(Cancel As Integer)
If MsgBox("是否刪除該記錄：" & Me.存款I(lǐng)D & "？", vbYesNo) = vbYes Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 存款表 Where 存款I(lǐng)D = " & 存款I(lǐng)D
DoCmd.RunSQL del_sql
Me.Requery
End If
End Sub
存款統(tǒng)計查詢
Private Sub Command報表_Click()
If Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
DoCmd.OpenReport "存款統(tǒng)計報表", acViewReport, , Me.數(shù)據(jù)表子窗體.Form.Filter
Else
DoCmd.OpenReport "存款統(tǒng)計報表", acViewReport
End If
End Sub
Private Sub Command查詢_Click()
On Error GoTo 結(jié)束查詢
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
If 查詢字段.Value = "日期" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " =#" & Me.查詢內(nèi)容 & "#"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
If 查詢字段.Value = "存入合計" Or 查詢字段.Value = "取出合計" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & ">= " & Me.查詢內(nèi)容
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " like '*" & Me.查詢內(nèi)容 & "*'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Exit Sub
結(jié)束查詢:
MsgBox Err.Description
End Sub
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
家庭成員查詢數(shù)據(jù)表
Private Sub Form_BeforeUpdate(Cancel As Integer)
If 姓名.Value <> "" And 性別.Value <> "" And 家庭關(guān)系.Value <> "" And 生日.Value <> "" And 職業(yè)狀態(tài).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 "姓名，性別，家庭關(guān)系，生日，職業(yè)狀態(tài)，聯(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 姓名_DblClick(Cancel As Integer)
If MsgBox("是否刪除該記錄：" & Me.姓名 & "？注意：刪除家庭成員后涉及關(guān)聯(lián)的收支理財?shù)扔涗浺矔粍h除", vbYesNo) = vbYes Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 家庭成員表 Where 姓名 = '" & 姓名 & "'"
DoCmd.RunSQL del_sql
Me.Requery
End If
End Sub
家庭成員管理
Option Compare Database
Private Sub Command報表_Click()
If Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
DoCmd.OpenReport "家庭成員標(biāo)簽", acViewReport, , Me.數(shù)據(jù)表子窗體.Form.Filter
Else
DoCmd.OpenReport "家庭成員標(biāo)簽", acViewReport
End If
End Sub
Private Sub Command查詢_Click()
On Error GoTo 結(jié)束查詢
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " like '*" & Me.查詢內(nèi)容 & "*'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Me.數(shù)據(jù)表子窗體.SetFocus
Exit Sub
結(jié)束查詢:
MsgBox Err.Description
End Sub
Private Sub Command清空_Click()
姓名.Value = ""
性別.Value = ""
家庭關(guān)系.Value = ""
生日.Value = ""
職業(yè)狀態(tài).Value = ""
聯(lián)系方式.Value = ""
備注.Value = ""
End Sub
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
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 家庭關(guān)系 = "" Or IsNull(家庭關(guān)系) = True Then
MsgBox "家庭關(guān)系值為空！"
Exit Sub
End If
If 生日 = "" Or IsNull(生日) = True Then
MsgBox "生日值為空！"
Exit Sub
End If
If 職業(yè)狀態(tài) = "" Or IsNull(職業(yè)狀態(tài)) = True Then
MsgBox "職業(yè)狀態(tài)值為空！"
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 "該家庭成員姓名已存在！不能重復(fù)"
Exit Sub
End If
On Error Resume Next
DoCmd.SetWarnings (False)
Dim add_sql As String
add_sql = "Insert Into 家庭成員表 (姓名,性別,家庭關(guān)系,生日,職業(yè)狀態(tài),聯(lián)系方式,備注) Values ('" & 姓名 & "','" & 性別 & "','" & 家庭關(guān)系 & "',#" & 生日 & "#,'" & 職業(yè)狀態(tài) & "','" & 聯(lián)系方式 & "','" & 備注 & "')"
DoCmd.RunSQL add_sql
MsgBox "添加完成"
Me.數(shù)據(jù)表子窗體.Form.Requery
End Sub
理財管理
Option Compare Database
Private Sub Command報表_Click()
If Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
DoCmd.OpenReport "理財記錄報表", acViewReport, , Me.數(shù)據(jù)表子窗體.Form.Filter
Else
DoCmd.OpenReport "理財記錄報表", acViewReport
End If
End Sub
Private Sub Command查詢_Click()
On Error GoTo 結(jié)束查詢
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
If 查詢字段.Value = "日期" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " =#" & Me.查詢內(nèi)容 & "#"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
If 查詢字段.Value = "本金" Or 查詢字段.Value = "收入" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & ">= " & Me.查詢內(nèi)容
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " like '*" & Me.查詢內(nèi)容 & "*'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Exit Sub
結(jié)束查詢:
MsgBox Err.Description
End Sub
Private Sub Command清空_Click()
家庭成員.Value = ""
理財項目.Value = ""
理財內(nèi)容.Value = ""
開始日期.Value = ""
結(jié)束日期.Value = ""
本金.Value = ""
收益率.Value = ""
收入.Value = ""
備注.Value = ""
End Sub
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
Private Sub Command收入收益率_Click()
If Me.收入 <> "" And Me.本金 <> "" And Me.本金 <> 0 Then
Me.收益率 = Me.收入 / Me.本金
Else
Me.收益率 = ""
End If
End Sub
Private Sub Command收益率收入_Click()
If Me.收益率 <> "" And Me.本金 <> "" Then
Me.收入 = Me.收益率 * Me.本金
Else
Me.收入 = ""
End If
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 理財內(nèi)容 = "" Or IsNull(理財內(nèi)容) = True Then
MsgBox "理財內(nèi)容值為空！"
Exit Sub
End If
If 開始日期 = "" Or IsNull(開始日期) = True Then
MsgBox "開始日期值為空！"
Exit Sub
End If
If 結(jié)束日期 = "" Or IsNull(結(jié)束日期) = True Then
MsgBox "結(jié)束日期值為空！"
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
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("理財表", dbOpenTable)
With add_rs
.AddNew
!家庭成員.Value = 家庭成員.Value
!理財項目.Value = 理財項目.Value
!理財內(nèi)容.Value = 理財內(nèi)容.Value
!開始日期.Value = 開始日期.Value
!結(jié)束日期.Value = 結(jié)束日期.Value
!本金.Value = 本金.Value
!收益率.Value = 收益率.Value
!收入.Value = 收入.Value
!備注.Value = 備注.Value
.Update
.Close
End With
Set add_rs = Nothing
MsgBox "添加完成"
Me.數(shù)據(jù)表子窗體.Form.Requery
End Sub
理財數(shù)據(jù)表
Private Sub Form_BeforeUpdate(Cancel As Integer)
If 家庭成員.Value <> "" And 理財項目.Value <> "" And 理財內(nèi)容.Value <> "" And 開始日期.Value <> "" And 結(jié)束日期.Value <> "" And 本金.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 "家庭成員，理財項目，理財內(nèi)容，開始日期，結(jié)束日期，本金，收益率，收入都不能為空"
On Error Resume Next
DoCmd.RunCommand acCmdUndo
Exit Sub
End If
Exit Sub
數(shù)據(jù)更新前提醒_Err:
MsgBox Error$
End Sub
Private Sub 理財ID_DblClick(Cancel As Integer)
If MsgBox("是否刪除該記錄：" & Me.理財ID & "？", vbYesNo) = vbYes Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 理財表 Where 理財ID = " & 理財ID
DoCmd.RunSQL del_sql
Me.Requery
End If
End Sub
理財統(tǒng)計查詢
Private Sub Command報表_Click()
If Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
DoCmd.OpenReport "理財統(tǒng)計報表", acViewReport, , Me.數(shù)據(jù)表子窗體.Form.Filter
Else
DoCmd.OpenReport "理財統(tǒng)計報表", acViewReport
End If
End Sub
Private Sub Command查詢_Click()
On Error GoTo 結(jié)束查詢
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
If 查詢字段.Value = "日期" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " =#" & Me.查詢內(nèi)容 & "#"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
If 查詢字段.Value = "本金合計" Or 查詢字段.Value = "收入合計" Or 查詢字段.Value = "平均收益率" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & ">= " & Me.查詢內(nèi)容
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " like '*" & Me.查詢內(nèi)容 & "*'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Exit Sub
結(jié)束查詢:
MsgBox Err.Description
End Sub
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
理財項目數(shù)據(jù)表
Private Sub Form_BeforeUpdate(Cancel As Integer)
If 理財項目.Value <> "" Then
On Error GoTo 數(shù)據(jù)更新前提醒_Err
If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
Beep
Else
DoCmd.RunCommand acCmdUndo
End If
Else
MsgBox "理財項目不能為空"
On Error Resume Next
DoCmd.RunCommand acCmdUndo
Exit Sub
End If
Exit Sub
數(shù)據(jù)更新前提醒_Err:
MsgBox Error$
End Sub
Private Sub 理財項目_DblClick(Cancel As Integer)
If MsgBox("是否刪除該記錄：" & Me.理財項目 & "？", vbYesNo) = vbYes Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 理財項目表 Where 理財項目 = '" & Me.理財項目 & "'"
DoCmd.RunSQL del_sql
Me.Requery
End If
End Sub
收支管理
Option Compare Database
Private Sub Command報表_Click()
If Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
DoCmd.OpenReport "收支記錄報表", acViewReport, , Me.數(shù)據(jù)表子窗體.Form.Filter
Else
DoCmd.OpenReport "收支記錄報表", acViewReport
End If
End Sub
Private Sub Command查詢_Click()
On Error GoTo 結(jié)束查詢
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
If 查詢字段.Value = "日期" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " =#" & Me.查詢內(nèi)容 & "#"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
If 查詢字段.Value = "收入" Or 查詢字段.Value = "支出" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & ">= " & Me.查詢內(nèi)容
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
End If
Me.數(shù)據(jù)表子窗體.Form.Filter = Me.查詢字段 & " like '*" & Me.查詢內(nèi)容 & "*'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Me.數(shù)據(jù)表子窗體.Requery
Else
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End If
Exit Sub
結(jié)束查詢:
MsgBox Err.Description
End Sub
Private Sub Command清空_Click()
日期.Value = ""
類型.Value = ""
家庭成員.Value = ""
項目.Value = ""
內(nèi)容.Value = ""
收入.Value = ""
支出.Value = ""
備注.Value = ""
End Sub
Private Sub Command全部_Click()
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
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
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 add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("收支表", dbOpenTable)
With add_rs
.AddNew
!日期.Value = 日期.Value
!類型.Value = 類型.Value
!家庭成員.Value = 家庭成員.Value
!項目.Value = 項目.Value
!內(nèi)容.Value = 內(nèi)容.Value
!收入.Value = 收入.Value
!支出.Value = 支出.Value
!備注.Value = 備注.Value
.Update
.Close
End With
Set add_rs = Nothing
MsgBox "添加完成"
Me.數(shù)據(jù)表子窗體.Form.Requery
End Sub
收支數(shù)據(jù)表
Private Sub Form_BeforeUpdate(Cancel As Integer)
If 日期.Value <> "" And 類型.Value <> "" And 家庭成員.Value <> "" And 項目.Value <> "" And 內(nèi)容.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 "日期，類型，家庭成員，項目，內(nèi)容，收入，支出都不能為空"
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 收支ID_DblClick(Cancel As Integer)
If MsgBox("是否刪除該記錄：" & Me.收支ID & "？", vbYesNo) = vbYes Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 收支表 Where 收支ID = " & 收支ID
DoCmd.RunSQL del_sql
Me.Requery
End If
End Sub
收支統(tǒng)計查詢
Private Sub Command報表_Click()
If Me.收支項目統(tǒng)計子窗體.Form.FilterOn = True Then
DoCmd.OpenReport "收支項目統(tǒng)計報表", acViewReport, , Me.收支項目統(tǒng)計子窗體.Form.Filter
Else
DoCmd.OpenReport "收支項目統(tǒng)計報表", acViewReport
End If
End Sub
Private Sub Command報表2_Click()
If Me.月收支統(tǒng)計子窗體.Form.FilterOn = True Then
DoCmd.OpenReport "月收支統(tǒng)計報表", acViewReport, , Me.月收支統(tǒng)計子窗體.Form.Filter
Else
DoCmd.OpenReport "月收支統(tǒng)計報表", acViewReport
End If
End Sub
Private Sub Command報表3_Click()
If Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
DoCmd.OpenReport "家庭成員收支統(tǒng)計報表", acViewReport, , Me.數(shù)據(jù)表子窗體.Form.Filter
Else
DoCmd.OpenReport "家庭成員收支統(tǒng)計報表", acViewReport
End If
End Sub
收支項目數(shù)據(jù)表
Private Sub Form_BeforeUpdate(Cancel As Integer)
If 項目.Value <> "" And 類型.Value <> "" Then
On Error GoTo 數(shù)據(jù)更新前提醒_Err
If (MsgBox("是否保存對記錄的修改", 1, "修改記錄提醒") = 1) Then
Beep
Else
DoCmd.RunCommand acCmdUndo
End If
Else
MsgBox "項目和類型都不能為空"
On Error Resume Next
DoCmd.RunCommand acCmdUndo
Exit Sub
End If
Exit Sub
數(shù)據(jù)更新前提醒_Err:
MsgBox Error$
End Sub
Private Sub 項目_DblClick(Cancel As Integer)
If MsgBox("是否刪除該記錄：" & Me.項目 & "？", vbYesNo) = vbYes Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 收支項目表 Where 項目 = '" & Me.項目 & "'"
DoCmd.RunSQL del_sql
Me.Requery
End If
End Sub
需要系統(tǒng)原文件和全部設(shè)計資料可訪問同名↓
