frmlendreturn.frm

图书管理系统

                & DateAdd("m", Val(Left(txtDays, 1)), Date) & "'" _
                & "WHERE 条码='" & .TextMatrix(Index, 2) _
                & "' AND 证号='" & Trim(txtNum) & "' AND 库号='" _
                & cmbRoom & "'"
    End With
    Library_Manage.Library_Manage_Con.Execute strSQL
    '修改借书清单
    LendedBooks.TextMatrix(Index, 4) = Date
    LendedBooks.TextMatrix(Index, 5) = DateAdd("m", Val(Left(txtDays, 1)), Date)
End Sub
Private Sub cmdLend_Click()
    Dim strCode$, strT$, i%
    If Val(txtLends) = Val(txtLended) Then
        MsgBox "已到最大借书量，不能再借书！", vbCritical, "图书借阅管理"
        Exit Sub
    End If
    strCode = UCase(InputBox("请输入图书条码：", "图书借阅管理"))
    If strCode = "" Then Exit Sub
    '检查当前库中有该条码的图书是否输入正确
    With Library_Manage.rsBooksStore
        If .State = adStateClosed Then .Open
        .Filter = "库室名='" & cmbRoom & "'"
        .Find "条码='" & strCode & "'"
        If .EOF Then
            MsgBox cmbRoom & "中无条码为<" & strCode & ">的图书！", _
                    vbCritical, "图书借阅管理"
            Exit Sub
        End If
    End With
    
    '检查指定条码对应的图书是否已在借书清单中,不允许借同样的图书
    With LendedBooks
        For i = 1 To .Rows - 1
            If .TextMatrix(i, 2) = strCode Then Exit For
        Next
        If i <> .Rows Then
            MsgBox strCode & "条码图书你已经借阅,在借书清单中序号为" _
                   & i & "！", , "图书借阅管理"
            Exit Sub
        End If
    End With
    
    '添加借书记录
    With Library_Manage.rsLendReturn
        If .State = adStateClosed Then .Open
        .AddNew
        .Fields("条码") = strCode
        .Fields("证号") = Trim(txtNum)
        .Fields("借期") = Date
        If txtDays <> "无限制" Then _
        .Fields("还期") = DateAdd("m", Val(Left(txtDays, 1)), Date)
        .Fields("库号") = cmbRoom.ListIndex
        .Update
    End With
    '修改馆藏书目可借数,在检查条码时对应的馆藏记录已是当前记录
    With Library_Manage.rsBooksStore
        .Fields("可借数") = .Fields("可借数") - 1
        '生成借书清单项目信息
        If Val(txtLended) = 0 Then
            strT = "   " & 1
        Else
            strT = "   " & LendedBooks.Rows
        End If
        strT = strT & vbTab & .Fields("书名")
        strT = strT & vbTab & strCode
        strT = strT & vbTab & .Fields("索书号")
        .Update             '保存对可借数的修改
        .Filter = ""        '取消按库室名过滤
    End With
    '生成借书清单项目信息
    strT = strT & vbTab & Date
    strT = strT & vbTab & DateAdd("m", Val(Left(txtDays, 1)), Date)
    strT = strT & vbTab & "False"
    strT = strT & vbTab & cmbRoom
    '添加借书清单项目
    With LendedBooks
        If Val(txtLended) <> 0 Then
            .AddItem strT
        Else
            .FixedRows = 0
            .RemoveItem 1
            .AddItem strT
            .FixedRows = 1
        End If
    End With
    '修改显示的个人借书数量
    txtLends = Val(txtLends) - 1
    txtLended = Val(txtLended) + 1
End Sub

Private Sub cmdNum_Click()
    Dim strNum$, strSQL1$, strSQL2$, i%
    '输入借阅证编号
    strNum = InputBox("请输入借阅证号码：", "图书借阅管理")
    
    '检查借阅证号是否正确
    With Library_Manage.rsReaderData
        If .State = adStateClosed Then .Open
        .MoveFirst
        .Find "证号='" & strNum & "'"
        If .EOF Then
            MsgBox "借阅证号输入错误！", vbCritical, "图书借阅管理"
            Exit Sub
        End If
    End With
    If strNum = "" Then Exit Sub
    
    '获得读者个人及借书信息
    strSQL1 = "select 读者数据.*,读者类别.* from 读者数据,读者类别 " _
             & "where 类号=编号 and 证号='" & strNum & "'"
    strSQL2 = "select distinct 书名,图书借阅.条码,索书号,借期,还期,已还,库号 " & _
             "from 图书借阅,馆藏书目 where 图书借阅.条码=馆藏书目.条码 " & _
             "and 图书借阅.证号='" & strNum & "'" & "order by 借期"
    With Library_Manage.Library_Manage_Con
        If .State = adStateClosed Then .Open
        Set objInfo = New Recordset
        Set objInfo = .Execute(strSQL1)
        Set objBooks = New Recordset
        Set objBooks = .Execute(strSQL2)
    End With
    
    '显示读者个人信息
    If objInfo.RecordCount > 0 Then
        With objInfo
            txtName = .Fields("姓名")
            txtDept = .Fields("部门")
            txtNum = .Fields("证号")
            If .Fields("有效期") = 0 Then
                txtDate = "无"
            Else
                txtDate = DateAdd("yyyy", 4, .Fields("日期"))
            End If
            If .Fields("借书期") = 0 Then
                txtDays = "无限制"
            Else
                txtDays = .Fields("借书期") & "个月"
            End If
            txtType = .Fields("名称")
            txtLended = objBooks.RecordCount
            txtLends = .Fields("借书量") - objBooks.RecordCount
        End With
        '根据借书情况请用借阅功能按钮
        cmdNum.Enabled = False
        If objBooks.RecordCount > 0 Then
            cmdReLend.Enabled = True
            cmdReturn.Enabled = True
        End If
        If Val(txtLends) > Val(txtLended) Then
            cmdLend.Enabled = True
            cmdReLend.Enabled = True
        Else
            cmdLend.Enabled = False
            cmdReLend.Enabled = False
        End If
        '显示借书清单
        If objBooks.RecordCount > 0 Then _
            ShowBooksList objBooks, LendedBooks
    End If
End Sub

Private Sub cmdReturn_Click()
    Dim strCode$, i%, isOut As Boolean
    strCode = UCase(InputBox("请输入图书条码：", "图书借阅管理"))
    If strCode = "" Then Exit Sub
    '检查指定条码对应的图书是否已在借书清单中
    With LendedBooks
        For i = 1 To .Rows - 1
            If .TextMatrix(i, 2) = strCode Then Exit For
        Next
        If i = .Rows Then
            MsgBox strCode & "条码输入错误！", , "图书借阅管理"
            Exit Sub
        Else
            '检查图书是否超期
            If .TextMatrix(i, 0) Like "[*]*" Then isOut = True
         End If
    End With
    '删除或修改借书记录
    With Library_Manage.rsLendReturn
        If .State = adStateClosed Then .Open
        .Filter = "证号='" & Trim(txtNum) & "'"
        .Find "条码='" & strCode & "'"
        If isOut Then
            .Fields("已还") = True  '修改超期借书记录的已还字段
        Else
            .Delete                 '删除未超期借书记录
        End If
        .Update
        .Filter = ""
    End With
    '修改可借数
    With Library_Manage.rsBooksStore
        If .State = adStateClosed Then .Open
        .Filter = "库室名='" & LendedBooks.TextMatrix(i, 7) & "'"
        .Find "条码='" & strCode & "'"
        .Fields("可借数") = .Fields("可借数") + 1
        .Update
        .Filter = ""
    End With
    '删除或修改借书清单项目
    With LendedBooks
        If isOut Then
            '修改超期借书记录的已还字段
            .TextMatrix(i, 6) = "True"
        Else
            '删除借书清单中的借书记录
            If .Rows <> 2 Then
                .RemoveItem i       '如果不是最后一条借书记录，则将其删除
            Else
                '清除第1行中的借书记录数据
                For i = 0 To .Cols - 1
                    .TextMatrix(1, i) = ""
                Next
            End If
        End If
    End With
    '修改读者借书数目
    txtLends = Val(txtLends) + 1
    txtLended = Val(txtLended) - 1
End Sub

Private Sub Form_Load()
    Dim strCN, i%
    With LendedBooks
        '调整各列宽度
        .ColWidth(0) = 600:     .ColWidth(1) = 3000:    .ColWidth(2) = 1000
        .ColWidth(3) = 2000:    .ColWidth(4) = 800:     .ColWidth(5) = 800
        .ColWidth(6) = 800:     .ColWidth(7) = 2000
        .ColAlignment(0) = 1:   .ColAlignment(4) = 1
        .ColAlignment(5) = 1:   .ColAlignment(7) = 1
        '显示列表字段名称
        strCN = Array("序号", "书名", "条码", "索书号", "借期", "还期", "已还", "库名")
        For i = 0 To 7
            .TextMatrix(0, i) = strCN(i)
        Next
    End With
End Sub

Private Sub Form_Resize()
    LendedBooks.Width = Abs(Me.ScaleWidth - 2 * LendedBooks.Left)
    LendedBooks.Height = Abs(Me.ScaleHeight - LendedBooks.Top - 200)
End Sub

Private Sub cmdExit_Click()
    Unload Me       '关闭借阅管理窗体
End Sub

Private Sub ShowBooksList(Data As Recordset, List As MSFlexGrid)
    Dim i%, iRow%, OutDate As Boolean, strT$
    With List
        .FixedRows = 0
        .RemoveItem 1
        '显示借书记录
        While Not Data.EOF
            strT = Data.AbsolutePosition
            For i = 0 To Data.Fields.Count - 2
                strT = strT & vbTab & Data.Fields(i)
            Next
            strT = strT & vbTab & cmbRoom.List(Data.Fields(i))
            If IsDate(Data.Fields("还期")) Then
                If CDate(Data.Fields("还期")) < Date Then
                    OutDate = True
                    iRow = iRow + 1
                    strT = "*  " & strT     '为超期的借书记录添加标记
                Else
                    strT = "   " & strT
                End If
            Else
                strT = "   " & strT
            End If
            .AddItem strT
            Data.MoveNext
        Wend
        .FixedRows = 1     '固定字段标题
    End With
    If OutDate Then         '显示借书超期提示
        MsgBox "你共有" & iRow & "本书已经超期！", vbCritical, "图书借阅管理"
        '禁用借书、续借按钮
        cmdLend.Enabled = False
        cmdReLend.Enabled = False
    End If
End Sub