VERSION 5.00
Begin VB.Form Form7 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "借還書"
   ClientHeight    =   1050
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   3210
   Icon            =   "BrRt.frx":0000
   LinkTopic       =   "Form7"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   1050
   ScaleWidth      =   3210
   StartUpPosition =   2  '屏幕中心
   Begin VB.CommandButton Command3 
      Caption         =   "退  出"
      Height          =   375
      Left            =   2280
      TabIndex        =   6
      Top             =   600
      Width           =   855
   End
   Begin VB.Data Data2 
      Caption         =   "Data2"
      Connect         =   "Access"
      DatabaseName    =   "D:\My Documents\ZQH\VB\mis\mis.mdb"
      DefaultCursorType=   0  '缺省游標
      DefaultType     =   2  '使用 ODBC
      Exclusive       =   0   'False
      Height          =   375
      Left            =   120
      Options         =   0
      ReadOnly        =   0   'False
      RecordsetType   =   1  'Dynaset
      RecordSource    =   "STUDBR"
      Top             =   960
      Visible         =   0   'False
      Width           =   1140
   End
   Begin VB.CommandButton Command1 
      Caption         =   "借  書"
      Height          =   375
      Left            =   2280
      TabIndex        =   4
      Top             =   120
      Width           =   855
   End
   Begin VB.TextBox Text2 
      Height          =   375
      Left            =   720
      TabIndex        =   3
      Top             =   600
      Width           =   1455
   End
   Begin VB.TextBox Text1 
      Height          =   375
      Left            =   720
      TabIndex        =   2
      Top             =   120
      Width           =   1455
   End
   Begin VB.CommandButton Command2 
      Caption         =   "還  書"
      Height          =   375
      Left            =   2280
      TabIndex        =   5
      Top             =   120
      Width           =   855
   End
   Begin VB.Label Label2 
      Caption         =   "學 號"
      Height          =   375
      Left            =   120
      TabIndex        =   1
      Top             =   720
      Width           =   975
   End
   Begin VB.Label Label1 
      Caption         =   "書 號"
      Height          =   375
      Left            =   120
      TabIndex        =   0
      Top             =   240
      Width           =   975
   End
End
Attribute VB_Name = "Form7"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private DbStr1, DbStr2, DbStr3 As String '分別指示STUDENT表、BOOK表和STUDBR表
Private sign As Boolean     '作為標記

Private Sub Command1_Click()    '借書操作
Dim count As Integer    '用于計算借閱量
Dim i As Integer
Dim text As String  '傳送書名
sign = False
'文本框不能為空
If Text1.text = "" Then
   MsgBox "請輸入書號！", 48, "提示"
   Exit Sub
End If
If Text2.text = "" Then
   MsgBox "請輸入學號！", 48, "提示"
   Exit Sub
End If

'搜索圖書庫中是否有該書，是否可借及確定是否輸入書號有錯
Data2.RecordSource = DbStr2
'更新數據庫
Data2.Refresh
Data2.Recordset.MoveLast
Data2.Recordset.MoveFirst
For i = 1 To Data2.Recordset.RecordCount
    If Data2.Recordset.Fields("書號") = Text1.text Then
       sign = True  '表示圖書庫中有該書
       If Data2.Recordset.Fields("標志") = "不可借" Then
          MsgBox "此書已借出，不可借！檢查是否書號錯誤！", 48, "提示"
          Exit Sub
       Else
          text = Data2.Recordset.Fields("書名")
       End If
    End If
    Data2.Recordset.MoveNext
Next i
If sign = False Then
    MsgBox "圖書庫中沒有該書，檢查是否書號錯誤！", 48, "提示"
    Exit Sub
End If

'學生庫中搜索學號
sign = False
Data2.RecordSource = DbStr1
'更新數據庫
Data2.Refresh
Data2.Recordset.MoveLast
Data2.Recordset.MoveFirst
For i = 1 To Data2.Recordset.RecordCount
    If Data2.Recordset.Fields("學號") = Text2.text Then
       sign = True     '表示STUDENT庫中有該學生
       If Data2.Recordset.Fields("借閱量") = 4 Then '書已借滿，不可借
          MsgBox "該生已借滿四本書，不可再借！", 48, "提示"
          Exit Sub
       End If
    End If
    Data2.Recordset.MoveNext
Next i
If sign = False Then       '表示STUDENT庫中沒有該學生
   MsgBox "沒有該學生！檢查是否學號錯誤！", 48, "提示"
   Exit Sub
End If

'修改STUDENT數據表
Data2.RecordSource = DbStr1
'更新數據庫
Data2.Refresh
Data2.Recordset.MoveLast
Data2.Recordset.MoveFirst
For i = 1 To Data2.Recordset.RecordCount
    If Data2.Recordset.Fields("學號") = Text2.text Then
       Data2.Recordset.Edit
       count = Data2.Recordset.Fields("借閱量")
       Data2.Recordset.Fields("借閱量") = count + 1
       Data2.Recordset.Update       '更新記錄
       Data2.Refresh  '更新數據庫
       GoTo BOOKDB
    End If
    Data2.Recordset.MoveNext
Next i

BOOKDB:
'修改BOOK數據表
Data2.RecordSource = DbStr2
'更新數據庫
Data2.Refresh
Data2.Recordset.MoveLast
Data2.Recordset.MoveFirst
For i = 1 To Data2.Recordset.RecordCount
    If Data2.Recordset.Fields("書號") = Text1.text Then
       Data2.Recordset.Edit
       Data2.Recordset.Fields("標志") = "不可借"
       Data2.Recordset.Update       '更新記錄
       Data2.Refresh  '更新數據庫
       GoTo STUDBRDB
    End If
    Data2.Recordset.MoveNext
Next i

STUDBRDB:
'修改STUDBR數據表
Data2.RecordSource = DbStr3
'更新數據庫
Data2.Refresh
Data2.Recordset.AddNew
Data2.Recordset.Fields("書號") = Text1.text
Data2.Recordset.Fields("學號") = Text2.text
Data2.Recordset.Fields("書名") = text
Data2.Recordset.Fields("借閱日期") = Date
Data2.Recordset.Fields("應還日期") = Date + CDate("30")

Data2.Recordset.Update       '更新記錄
Data2.Refresh  '更新數據庫

MsgBox "借書成功！", 48, "提示"
Unload Me
End Sub

Private Sub Command2_Click()       '還書操作
Dim text As String   '傳送學號
Dim sign As Boolean
If Text1.text = "" Then     '必須填寫書號
   MsgBox "請輸入書號！", 48, "提示"
   Exit Sub
End If

'搜索圖書庫中是否有該書，該書是否借出及確定是否輸入書號有錯
Data2.RecordSource = DbStr2
'更新數據庫
Data2.Refresh
Data2.Recordset.MoveLast
Data2.Recordset.MoveFirst
For i = 1 To Data2.Recordset.RecordCount
    If Data2.Recordset.Fields("書號") = Text1.text Then
       sign = True  '表示BOOK中有該書
       If Data2.Recordset.Fields("標志") = "可借" Then
          MsgBox "此書未借出！檢查是否書號錯誤！", 48, "提示"
          Exit Sub
       End If
    End If
    Data2.Recordset.MoveNext
Next i
If sign = False Then     '表示BOOK中沒有該書
    MsgBox "圖書庫中沒有該書，檢查是否書號錯誤！", 48, "提示"
    Exit Sub
End If

'修改STUDBR數據表
Data2.RecordSource = DbStr3
'更新數據庫
Data2.Refresh
Data2.Recordset.MoveLast
Data2.Recordset.MoveFirst
For i = 1 To Data2.Recordset.RecordCount
    If Data2.Recordset.Fields("書號") = Text1.text Then
       sign = True  '表示STUDBR庫中有該書
       If Date - CDate(Data2.Recordset.Fields("借閱日期")) > 30 Then
          MsgBox "超出期限，罰款處理！", 48, "提示"
       End If
       text = Data2.Recordset.Fields("學號")    '帶回學號，以便修改STUDENT庫
       Data2.Recordset.Delete   '從STUDBR庫中刪除該記錄
       Data2.Refresh
       GoTo DBSTUDENT
    End If
    Data2.Recordset.MoveNext
Next i
If sign = False Then     '表示STUDBR庫中沒有該書
    MsgBox "借還書庫中沒有該書，檢查是否書號錯誤！", 48, "提示"
    Exit Sub
End If

DBSTUDENT:
'修改STUDENT數據表
Data2.RecordSource = DbStr1
'更新數據庫
Data2.Refresh
Data2.Recordset.MoveLast
Data2.Recordset.MoveFirst
For i = 1 To Data2.Recordset.RecordCount
    If Data2.Recordset.Fields("學號") = text Then
       Data2.Recordset.Edit
       Data2.Recordset.Fields("借閱量") = Data2.Recordset.Fields("借閱量") - 1
       Data2.Recordset.Update       '更新記錄
       Data2.Refresh  '更新數據庫
       GoTo DBBOOK
    End If
    Data2.Recordset.MoveNext
Next i

DBBOOK:
'修改BOOK數據表
Data2.RecordSource = DbStr2
'更新數據庫
Data2.Refresh
Data2.Recordset.MoveLast
Data2.Recordset.MoveFirst
For i = 1 To Data2.Recordset.RecordCount
    If Data2.Recordset.Fields("書號") = Text1.text Then
       Data2.Recordset.Edit
       Data2.Recordset.Fields("標志") = "可借"
       Data2.Recordset.Update       '更新記錄
       Data2.Refresh  '更新數據庫
    End If
    Data2.Recordset.MoveNext
Next i

MsgBox "書已還", 48, "提示"
Unload Me
End Sub

Private Sub Command3_Click()       '退出

Unload Me
End Sub

Private Sub Form_Load()       '裝載窗體
DbStr1 = "STUDENT"    '學生庫
DbStr2 = "BOOK"      '圖書庫
DbStr3 = "STUDBR"      '學生借書檔案
Data2.DatabaseName = App.Path + "\mis.mdb"
End Sub