统计数据

Private Sub Command清空原数据_Click()

DoCmd.SetWarnings (False)

Dim del_sql As String

del_sql = "Delete From 原数据表"

DoCmd.RunSQL del_sql

Me.数据表子窗体.Requery

End Sub

Private Sub Command刷新_Click()

Me.数据表子窗体.Requery

End Sub

复选框多选导出数据

查询窗体

Private Sub Command导出_Click()

'清空查询

On Error Resume Next

DoCmd.OutputTo acOutputQuery, "导出学生成绩"

End Sub

Private Sub Command反选_Click()

Dim update_sql As String

update_sql = "Select * From 学生成绩表"

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.数据表子窗体.Requery

End Sub

Private Sub Command全不选_Click()

DoCmd.SetWarnings (False)

Dim update_sql As String

update_sql = "Update 学生成绩表 Set 选择=false"

DoCmd.RunSQL update_sql

Me.数据表子窗体.Requery

End Sub

Private Sub Command全选_Click()

DoCmd.SetWarnings (False)

Dim update_sql As String

update_sql = "Update 学生成绩表 Set 选择=true"

DoCmd.RunSQL update_sql

Me.数据表子窗体.Requery

End Sub

借还书示例

还书

Private Sub Command还书_Click()

If Me.图书编号 <> "" And Me.读者 <> "" Then

    If CBool(Nz(DLookup("是否借出", "图书表", "图书编号='" & Me.图书编号 & "'"), "false")) = False Then

    MsgBox "该图书不存在或已还书！"

    Exit Sub

    End If

    '生成借阅记录

    DoCmd.SetWarnings (False)

    Dim update_sql As String

    update_sql = "Update 借书记录表 Set 还书日期=#" & Date & "# Where  借书ID=" & 借书ID_num

    DoCmd.RunSQL update_sql

    '更新图书借出

    update_sql = "Update 图书表 Set 是否借出=false  Where 图书编号='" & Me.图书编号 & "'"

    DoCmd.RunSQL update_sql

    MsgBox "还书成功"

    Me.数据表子窗体.Requery

Else

MsgBox "请选择借书ID和读者"

Exit Sub

End If

End Sub

Private Sub 读者_Change()

Me.数据表子窗体.Form.Filter = "读者编号='" & Me.读者 & "'"

Me.数据表子窗体.Form.FilterOn = True

End Sub

借书

Private Sub Command借书_Click()

If Me.图书 <> "" And Me.读者 <> "" Then

    If CBool(Nz(DLookup("是否借出", "图书表", "图书编号='" & Me.图书 & "'"), "true")) = True Then

    MsgBox "该图书不存在或已借出！"

    Exit Sub

    End If

    '生成借阅记录

    DoCmd.SetWarnings (False)

    Dim add_sql As String

    add_sql = "Insert Into 借书记录表 (图书编号,读者编号,借书日期) Values ( '" & 图书 & "','" & 读者 & "',#" & Date & "#)"

    DoCmd.RunSQL add_sql

    '更新图书借出

    Dim update_sql As String

    update_sql = "Update 图书表 Set 是否借出=True  Where 图书编号='" & Me.图书 & "'"

    DoCmd.RunSQL update_sql

    MsgBox "借书成功"

    Me.图书.Requery

    Me.数据表子窗体.Requery

Else

MsgBox "请选择图书和读者"

Exit Sub

End If

End Sub

Private Sub 读者_Change()

Me.数据表子窗体.Form.Filter = "读者编号='" & Me.读者 & "'"

Me.数据表子窗体.Form.FilterOn = True

End Sub

未还书查询数据表

Private Sub 借书ID_DblClick(Cancel As Integer)

借书ID_num = Me.借书ID

Forms("还书").图书编号.Value = Me.图书编号

Forms("还书").书名.Value = Me.书名

End Sub

公共变量

Public 借书ID_num As Long