統(tǒng)計(jì)數(shù)據(jù)

Private Sub Command清空原數(shù)據(jù)_Click()

DoCmd.SetWarnings (False)

Dim del_sql As String

del_sql = "Delete From 原數(shù)據(jù)表"

DoCmd.RunSQL del_sql

Me.數(shù)據(jù)表子窗體.Requery

End Sub

Private Sub Command刷新_Click()

Me.數(shù)據(jù)表子窗體.Requery

End Sub

復(fù)選框多選導(dǎo)出數(shù)據(jù)

查詢(xún)窗體

Private Sub Command導(dǎo)出_Click()

'清空查詢(xún)

On Error Resume Next

DoCmd.OutputTo acOutputQuery, "導(dǎo)出學(xué)生成績(jī)"

End Sub

Private Sub Command反選_Click()

Dim update_sql As String

update_sql = "Select * From 學(xué)生成績(jī)表"

Dim search_rs As DAO.Recordset

Set search_rs = CurrentDb.OpenRecordset(update_sql, dbOpenDynaset)

With search_rs

Do While .EOF = False

.Edit

If !選擇.Value = True Then

!選擇.Value = False

Else

!選擇.Value = True

End If

.Update

.MoveNext

Loop

.Close

End With

Set search_rs = Nothing

Me.數(shù)據(jù)表子窗體.Requery

End Sub

Private Sub Command全不選_Click()

DoCmd.SetWarnings (False)

Dim update_sql As String

update_sql = "Update 學(xué)生成績(jī)表 Set 選擇=false"

DoCmd.RunSQL update_sql

Me.數(shù)據(jù)表子窗體.Requery

End Sub

Private Sub Command全選_Click()

DoCmd.SetWarnings (False)

Dim update_sql As String

update_sql = "Update 學(xué)生成績(jī)表 Set 選擇=true"

DoCmd.RunSQL update_sql

Me.數(shù)據(jù)表子窗體.Requery

End Sub

借還書(shū)示例

還書(shū)

Private Sub Command還書(shū)_Click()

If Me.圖書(shū)編號(hào) <> "" And Me.讀者 <> "" Then

? ? If CBool(Nz(DLookup("是否借出", "圖書(shū)表", "圖書(shū)編號(hào)='" & Me.圖書(shū)編號(hào) & "'"), "false")) = False Then

? ? MsgBox "該圖書(shū)不存在或已還書(shū)！"

? ? Exit Sub

? ? End If

? ? '生成借閱記錄

? ? DoCmd.SetWarnings (False)

? ? Dim update_sql As String

? ? update_sql = "Update 借書(shū)記錄表 Set 還書(shū)日期=#" & Date & "# Where? 借書(shū)ID=" & 借書(shū)ID_num

? ? DoCmd.RunSQL update_sql

? ? '更新圖書(shū)借出

? ? update_sql = "Update 圖書(shū)表 Set 是否借出=false? Where 圖書(shū)編號(hào)='" & Me.圖書(shū)編號(hào) & "'"

? ? DoCmd.RunSQL update_sql

? ? MsgBox "還書(shū)成功"

? ? Me.數(shù)據(jù)表子窗體.Requery

Else

MsgBox "請(qǐng)選擇借書(shū)ID和讀者"

Exit Sub

End If

End Sub

Private Sub 讀者_(dá)Change()

Me.數(shù)據(jù)表子窗體.Form.Filter = "讀者編號(hào)='" & Me.讀者 & "'"

Me.數(shù)據(jù)表子窗體.Form.FilterOn = True

End Sub

借書(shū)

Private Sub Command借書(shū)_Click()

If Me.圖書(shū) <> "" And Me.讀者 <> "" Then

? ? If CBool(Nz(DLookup("是否借出", "圖書(shū)表", "圖書(shū)編號(hào)='" & Me.圖書(shū) & "'"), "true")) = True Then

? ? MsgBox "該圖書(shū)不存在或已借出！"

? ? Exit Sub

? ? End If

? ? '生成借閱記錄

? ? DoCmd.SetWarnings (False)

? ? Dim add_sql As String

? ? add_sql = "Insert Into 借書(shū)記錄表 (圖書(shū)編號(hào),讀者編號(hào),借書(shū)日期) Values ( '" & 圖書(shū) & "','" & 讀者 & "',#" & Date & "#)"

? ? DoCmd.RunSQL add_sql

? ? '更新圖書(shū)借出

? ? Dim update_sql As String

? ? update_sql = "Update 圖書(shū)表 Set 是否借出=True? Where 圖書(shū)編號(hào)='" & Me.圖書(shū) & "'"

? ? DoCmd.RunSQL update_sql

? ? MsgBox "借書(shū)成功"

? ? Me.圖書(shū).Requery

? ? Me.數(shù)據(jù)表子窗體.Requery

Else

MsgBox "請(qǐng)選擇圖書(shū)和讀者"

Exit Sub

End If

End Sub

Private Sub 讀者_(dá)Change()

Me.數(shù)據(jù)表子窗體.Form.Filter = "讀者編號(hào)='" & Me.讀者 & "'"

Me.數(shù)據(jù)表子窗體.Form.FilterOn = True

End Sub

未還書(shū)查詢(xún)數(shù)據(jù)表

Private Sub 借書(shū)ID_DblClick(Cancel As Integer)

借書(shū)ID_num = Me.借書(shū)ID

Forms("還書(shū)").圖書(shū)編號(hào).Value = Me.圖書(shū)編號(hào)

Forms("還書(shū)").書(shū)名.Value = Me.書(shū)名

End Sub

公共變量

Public 借書(shū)ID_num As Long