main_jygl_hs.frm

图书综合管理系统 VB毕业设计 原代码+论文

         Caption         =   "保存"
         Enabled         =   0   'False
         Height          =   390
         Left            =   1455
         Picture         =   "main_jygl_hs.frx":1635
         TabIndex        =   12
         Top             =   165
         Width           =   1215
      End
      Begin VB.CommandButton CmdCancel 
         BackColor       =   &H00FF8080&
         Caption         =   "取消"
         Enabled         =   0   'False
         Height          =   390
         Left            =   2670
         Picture         =   "main_jygl_hs.frx":2813
         TabIndex        =   11
         Top             =   165
         Width           =   1215
      End
      Begin VB.CommandButton CmdEnd 
         BackColor       =   &H00FF8080&
         Caption         =   "退出"
         Height          =   390
         Left            =   3885
         Picture         =   "main_jygl_hs.frx":39F1
         TabIndex        =   10
         Top             =   165
         Width           =   1215
      End
   End
   Begin VB.Label Label2 
      BackStyle       =   0  'Transparent
      Caption         =   "图 书 归 还 登 记"
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   14.25
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H000000FF&
      Height          =   285
      Index           =   0
      Left            =   4635
      TabIndex        =   19
      Top             =   90
      Width           =   2655
   End
   Begin VB.Label Label2 
      BackStyle       =   0  'Transparent
      Caption         =   "图 书 归 还 登 记"
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   14.25
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   285
      Index           =   1
      Left            =   4605
      TabIndex        =   5
      Top             =   90
      Width           =   2655
   End
End
Attribute VB_Name = "main_jygl_hs"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim s, Y, i     '声明变量
'声明数据集对象
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
Dim rs4 As New ADODB.Recordset
Dim lsph As Integer     '声明一个整型变量
Private Sub Form_Load()
 '定义MS1表格的行数、列数
 MS1.Rows = 102
 MS1.Cols = 10
 '定义MS1表格的列宽和表头信息
 s = Array("300", "990", "3000", "800", "1200", "800", "600", "1000", "1200", "800")
 Y = Array("xh", "条形码", "书名", "作者", "出版社", "单价", "数量", "金额", "图书类别", "备注")
 For i = 0 To 9
     MS1.ColWidth(i) = s(i)
     MS1.TextMatrix(0, i) = Y(i)
 Next i
 '定义MS1表格的行号
 For i = 1 To 101
     MS1.TextMatrix(i, 0) = i
 Next i
 rq.Caption = Date     '设置还书日期
End Sub
Private Sub Form_Unload(Cancel As Integer)
 frm_main.Enabled = True
End Sub
Private Sub DataCombo1_Change()
  Adodc3.RecordSource = "select * from reads where 书证号='" + DataCombo1.text + "'"
  Adodc3.Refresh
  If Adodc3.Recordset.RecordCount > 0 Then
   hsr.Caption = Adodc3.Recordset.Fields("姓名")
  End If
End Sub
Private Sub datacombo1_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = vbKeyReturn Then Tjsr.SetFocus
End Sub
Private Sub Tjsr_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = vbKeyReturn Then     '按回车键
  text1.Visible = True
  text1.Enabled = True
  '到达第1行，第1列
  MS1.Row = 1
  MS1.Col = 1
  text1.SetFocus
 End If
End Sub
Private Sub MS1_Click()
 If MS1.Row >= 1 And MS1.TextMatrix(MS1.Row - 1, 2) <> "" Then     '在MS1表格第1行或大于第1行时
  text1.Visible = True     'text1可见
  text1.Enabled = True
  text1.SetFocus
 End If
End Sub
Private Sub MS1_entercell()
 Dim X, Y, p As String
 If MS1.CellWidth <= 0 Or MS1.CellHeight <= 0 Then Exit Sub
 X = MS1.TextMatrix(MS1.FixedRows, MS1.Col)
 Y = MS1.TextMatrix(MS1.Row, 0)
 If Y <> "" Then
  If MS1.Col - MS1.LeftCol <= 3 Then MS1.LeftCol = MS1.LeftCol + 1
  If MS1.CellWidth > 0 And MS1.CellHeight > 0 Then
   text1.Width = MS1.CellWidth:  text1.Height = MS1.CellHeight
   text1.Left = MS1.CellLeft + MS1.Left
   text1.Top = MS1.CellTop + MS1.Top
  End If
  X = MS1.TextMatrix(MS1.FixedRows, MS1.Col)
  Y = MS1.TextMatrix(MS1.Row, 0)
  p = MS1.TextMatrix(MS1.Row, MS1.Col)
  text1.text = MS1.text
  text1.SelStart = 0
  text1.SelLength = Len(text1.text)
 End If
End Sub
Private Sub MS1_RowColChange()     '格式化金额
  For i = 1 To 100
   If MS1.TextMatrix(i, 1) <> "" Then
    MS1.TextMatrix(i, 5) = Format(MS1.TextMatrix(i, 5), "#0.00")
    MS1.TextMatrix(MS1.Row, 7) = Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5))
    MS1.TextMatrix(i, 7) = Format(MS1.TextMatrix(i, 7), "#0.00")
   End If
  Next i
End Sub
Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = vbKeyReturn Then
   Adodc1.RecordSource = "select * from jsb where 条形码='" + MS1.TextMatrix(MS1.Row, 1) + "'"
   Adodc1.Refresh
   If MS1.Col = 1 Then
    With Adodc1.Recordset
    If .RecordCount > 0 Then     '当记录大于零时
       '赋值给MS1表格
       If .Fields("条形码") <> "" Then MS1.TextMatrix(MS1.Row, 1) = .Fields("条形码")
       If .Fields("书名") <> "" Then MS1.TextMatrix(MS1.Row, 2) = .Fields("书名")
       If .Fields("作者") <> "" Then MS1.TextMatrix(MS1.Row, 3) = .Fields("作者")
       If .Fields("出版社") <> "" Then MS1.TextMatrix(MS1.Row, 4) = .Fields("出版社")
       If .Fields("单价") <> "" Then MS1.TextMatrix(MS1.Row, 5) = .Fields("单价")
       If .Fields("图书类别") <> "" Then MS1.TextMatrix(MS1.Row, 8) = .Fields("图书类别")
       MS1.Col = 5
       text1.SetFocus
    End If
    End With
   End If
   If MS1.Col = 10 Then
      MS1.Row = MS1.Row + 1
      MS1.Col = 1
    Else
      If MS1.Col + 1 <= MS1.Cols - 1 Then
         MS1.Col = MS1.Col + 1
       Else
         If MS1.Row + 1 <= MS1.Rows - 1 Then
            MS1.Row = MS1.Row + 1
            MS1.Col = 1
         End If
      End If
   End If
  End If
  If KeyCode = vbKeyUp Then
     If MS1.Row > 1 Then MS1.Row = MS1.Row - 1
  End If
  If KeyCode = vbKeyDown And (MS1.TextMatrix(MS1.Row, 2)) <> "" Then
     If MS1.Row < 99 Then MS1.Row = MS1.Row + 1
  End If
  If KeyCode = vbKeyLeft Then
   If text1.text <> "" Then
      text1.SelStart = 0
      text1.SelLength = Len(text1.text)
   End If
   If MS1.Col - 8 <= MS1.Cols + 1 Then
      MS1.Col = MS1.Col - 1
      If MS1.Col = 0 Then MS1.Col = 1
     Else
      If MS1.Row + 1 <= MS1.Row - 1 Then
         MS1.Row = MS1.Row + 1
         MS1.Col = 1
      End If
   End If
  End If
  If KeyCode = vbKeyRight Then
   If text1.text <> "" Then
      text1.SelStart = 0
      text1.SelLength = Len(text1.text)
   End If
   If MS1.Col + 1 <= MS1.Cols - 1 Then
      MS1.Col = MS1.Col + 1
     Else
      If MS1.Row + 1 <= MS1.Rows - 1 Then
         MS1.Row = MS1.Row + 1
         MS1.Col = 1
      End If
   End If
  End If
End Sub
Private Sub Text1_Change()
 MS1.text = text1.text     '赋值给MS1.text
 If MS1.Col = 5 Then MS1.TextMatrix(MS1.Row, 7) = Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5))
 If MS1.Col = 6 Then MS1.TextMatrix(MS1.Row, 7) = Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5))
 Dim a, B As Single
 For i = 1 To 101
   a = Val(MS1.TextMatrix(i, 7)) + a
   B = Val(MS1.TextMatrix(i, 6)) + B
 If MS1.TextMatrix(i, 1) <> "" And MS1.TextMatrix(i, 6) <> "" Then pz.Caption = i
 Next i
 hjje.Caption = a
 hjsl.Caption = B     '计算合计金额，合计数量
End Sub
Private Sub CmdReg_Click()
 '查询所有借书数据,并按票号排序
 Adodc2.RecordSource = "select * from hsb order by 票号"
 Adodc2.Refresh
 '创建借书票号
 If Adodc2.Recordset.RecordCount > 0 Then
  If Not Adodc2.Recordset.EOF Then Adodc2.Recordset.MoveLast
  If Adodc2.Recordset.Fields("票号") <> "" Then
     lsph = Right(Trim(Adodc2.Recordset.Fields("票号")), 4) + 1
     PH.Caption = Date & "hs" & Format(lsph, "0000")
  End If
  Else
    PH.Caption = Date & "hs" & "0001"
 End If
 '设置Text1有效
 text1.Enabled = True
 '确定文本框在MS1表格中的位置
 text1.Width = MS1.CellWidth
 text1.Height = MS1.CellHeight
 text1.Left = MS1.CellLeft + MS1.Left
 text1.Top = MS1.CellTop + MS1.Top
 MS1.Enabled = True
 CmdSave.Enabled = True
 CmdCancel.Enabled = True
 CmdReg.Enabled = False
End Sub
Private Sub CmdSave_Click()
 For i = 1 To 101
  If MS1.TextMatrix(i, 1) <> "" Then
   Set rs1 = New ADODB.Recordset
   rs1.Open "select * from hsb", Cnn, adOpenKeyset, adLockOptimistic
   '添加还书信息到"hsb"表中
   rs1.AddNew
   If MS1.TextMatrix(i, 1) <> "" Then rs1.Fields("条形码") = MS1.TextMatrix(i, 1)
   If MS1.TextMatrix(i, 2) <> "" Then rs1.Fields("书名") = MS1.TextMatrix(i, 2)
   If MS1.TextMatrix(i, 3) <> "" Then rs1.Fields("作者") = MS1.TextMatrix(i, 3)
   If MS1.TextMatrix(i, 4) <> "" Then rs1.Fields("出版社") = MS1.TextMatrix(i, 4)
   If MS1.TextMatrix(i, 5) <> "" Then rs1.Fields("单价") = MS1.TextMatrix(i, 5)
   If MS1.TextMatrix(i, 6) <> "" Then rs1.Fields("还回数量") = MS1.TextMatrix(i, 6)
   If MS1.TextMatrix(i, 7) <> "" Then rs1.Fields("金额") = MS1.TextMatrix(i, 7)
   If MS1.TextMatrix(i, 8) <> "" Then rs1.Fields("图书类别") = MS1.TextMatrix(i, 8)
   If MS1.TextMatrix(i, 9) <> "" Then rs1.Fields("备注") = MS1.TextMatrix(i, 9)
   If DataCombo1.text <> "" Then rs1.Fields("书证号") = Trim(DataCombo1.text)
   If hsr.Caption <> "" Then rs1.Fields("还书人") = hsr.Caption
   If Tjsr.text <> "" Then rs1.Fields("还书经手人") = Tjsr.text
   If rq.Caption <> "" Then rs1.Fields("还书日期") = rq.Caption
   If PH.Caption <> "" Then rs1.Fields("票号") = PH.Caption
   If frm_main.St1.Panels(3).text <> "" Then rs1.Fields("操作员") = frm_main.St1.Panels(3).text
   rs1.Update     '更新表
   rs1.Close
   '查找库存图书信息
   Set rs2 = New ADODB.Recordset
   rs2.Open "select * from kcb where 条形码='" + MS1.TextMatrix(i, 1) + "'", Cnn, adOpenKeyset, adLockOptimistic
   If rs2.RecordCount > 0 Then
    '更新"kcb"表中的"库存"及"库存金额"
    rs2.Fields("现存数量") = Val(rs2.Fields("现存数量")) + Val(MS1.TextMatrix(i, 6))
    rs2.Fields("金额") = Val(rs2.Fields("现存数量")) * Val(rs2.Fields("单价"))
    rs2.Update
   Else
    MsgBox "库存无此图书,无法归还！"
    Exit Sub
   End If
   rs2.Close
   '查找借阅图书信息
   Set rs3 = New ADODB.Recordset
   rs3.Open "select * from jsb where 条形码='" + MS1.TextMatrix(i, 1) + "'and 是否归还='否'", Cnn, adOpenKeyset, adLockOptimistic
   If rs3.RecordCount > 0 Then
    '更新"jsb"表中的"还书日期"、"还书经手人"及"是否归还"
    rs3.Fields("还书日期") = rq.Caption
    rs3.Fields("还书经手人") = Tjsr.text
    rs3.Fields("是否归还") = "是"
    rs3.Update
    Dim s As Integer
    s = Val(MS1.TextMatrix(i, 6)) + s
    Set rs4 = New ADODB.Recordset
    rs4.Open "select * from reads where 书证号='" + DataCombo1.text + "'", Cnn, adOpenKeyset, adLockOptimistic
    If rs4.RecordCount > 0 Then
      '更新"kcb"表中的"库存"及"库存金额"
      rs4.Fields("已借书数") = Val(rs4.Fields("已借书数")) - s
      rs4.Update
    Else
      MsgBox "此读者不存在！"
      Exit Sub
    End If
   Else
    MsgBox "您没有借阅此图书！"
    Exit Sub
   End If
   rs3.Close
  End If
 Next i
 '清空数据
 For i = 1 To 100
 For j = 1 To 9
     MS1.TextMatrix(i, j) = ""
 Next j
 Next i
 Tjsr.text = ""
 pz.Caption = ""
 hjsl.Caption = ""
 hjje.Caption = ""
 text1.Visible = False   '设置控件不可见
 MS1.Enabled = False
 CmdSave.Enabled = False
 CmdReg.Enabled = True
 CmdCancel.Enabled = False
End Sub
Private Sub CmdCancel_Click()      '取消操作
 Tjsr.text = ""
 pz.Caption = ""
 hjsl.Caption = ""
 hjje.Caption = ""
 For i = 1 To 100
 For j = 1 To 9
     MS1.TextMatrix(i, j) = ""
 Next j
 Next i
 text1.Enabled = False
 MS1.Enabled = False
 CmdSave.Enabled = False
 CmdCancel.Enabled = False
 CmdReg.Enabled = True
 CmdReg.SetFocus
End Sub
Private Sub CmdEnd_Click()
 frm_main.Enabled = True
 Unload Me
End Sub