frmstatisticlend.frm

本人用VB 6.0和ACCESS编写的图书管理系统

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "msflxgrd.ocx"
Begin VB.Form frmStatisticLend 
   Caption         =   "借出情况统计表"
   ClientHeight    =   7380
   ClientLeft      =   3090
   ClientTop       =   2910
   ClientWidth     =   7560
   Icon            =   "frmStatisticLend.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   ScaleHeight     =   7380
   ScaleWidth      =   7560
   StartUpPosition =   2  '屏幕中心
   Begin VB.CommandButton cmdClose 
      Caption         =   "关闭(&C)"
      Height          =   375
      Left            =   6120
      TabIndex        =   2
      Top             =   120
      Width           =   975
   End
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Height          =   6615
      Left            =   120
      TabIndex        =   1
      Top             =   600
      Width           =   7335
      _ExtentX        =   12938
      _ExtentY        =   11668
      _Version        =   393216
      Cols            =   4
   End
   Begin VB.Label Label1 
      AutoSize        =   -1  'True
      Caption         =   "借出情况统计表"
      BeginProperty Font 
         Name            =   "华文行楷"
         Size            =   15.75
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H0080C0FF&
      Height          =   330
      Left            =   2678
      TabIndex        =   0
      Top             =   120
      Width           =   2205
   End
End
Attribute VB_Name = "frmStatisticLend"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim LendStr() As String, LendStat() As Integer, LendSort() As Integer

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub Form_Load()
    OFFCAT.Play "wave"
    Dim db As Database, rs As Recordset, rs1 As Recordset
    Dim strAppName, strSQL As String
    Dim intCountSort, intCountBook, intCount As Integer
    strAppName = App.Path & "\图书库.mdb"
    Set db = DBEngine.OpenDatabase(strAppName, False, True)         '共亨、只读
    strSQL = "select count(*) as 类别总数 from 图书类别表"
    Set rs = db.OpenRecordset(strSQL)
    intCountSort = rs.Fields("类别总数")
    rs.Close
    Set rs = Nothing
    strSQL = "select count(*) as 图书总数目 from 图书表"
    Set rs1 = db.OpenRecordset(strSQL)
    intCountBook = rs1.Fields("图书总数目")
    rs1.Close
    Set rs1 = Nothing
    '给数组赋值
    intCount = intCountSort + intCountBook
    ReDim Preserve LendStr(intCount), LendStat(intCount, 3), LendSort(intCountSort)
    Dim i, j, intNo As Integer
    strSQL = "select 图书类别 from 图书类别表"
    Set rs = db.OpenRecordset(strSQL)
    rs.MoveFirst
    intNo = 0
    For i = 1 To intCountSort
        intNo = intNo + 1
        LendSort(i) = intNo
        LendStr(intNo) = rs.Fields("图书类别")
        strSQL = "select count(*) as 图书总数目 from 图书表 where 图书种类='" & LendStr(intNo) & "'"
        Set rs1 = db.OpenRecordset(strSQL)
        intCountBook = rs1.Fields("图书总数目")
        rs1.Close
        Set rs1 = Nothing
        If intCountBook > 0 Then
            strSQL = "select 图书编号,图书总数,现存数量 from 图书表 where 图书种类='" & LendStr(intNo) & "'"
            Set rs1 = db.OpenRecordset(strSQL)
            rs1.MoveFirst
            For j = 1 To intCountBook
                intNo = intNo + 1
                LendStr(intNo) = rs1.Fields("图书编号")
                LendStat(intNo, 1) = rs1.Fields("图书总数")
                LendStat(LendSort(i), 1) = LendStat(LendSort(i), 1) + LendStat(intNo, 1)
                LendStat(intNo, 2) = rs1.Fields("现存数量")
                LendStat(LendSort(i), 2) = LendStat(LendSort(i), 2) + LendStat(intNo, 2)
                LendStat(intNo, 3) = LendStat(intNo, 1) - LendStat(intNo, 2)
                LendStat(LendSort(i), 3) = LendStat(LendSort(i), 3) + LendStat(intNo, 3)
                rs1.MoveNext
            Next j
            rs1.Close
            Set rs1 = Nothing
        End If
        rs.MoveNext
    Next i
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    '将数据写入网格
    MSFlexGrid1.Rows = intCount + 1
    '定义网格列宽
    MSFlexGrid1.ColWidth(0) = 1500: MSFlexGrid1.ColWidth(1) = 1500
    MSFlexGrid1.ColWidth(2) = 1500: MSFlexGrid1.ColWidth(3) = 1500
    '定义网格标题
    MSFlexGrid1.Row = 0
    MSFlexGrid1.Col = 0: MSFlexGrid1.Text = "图书类别"
    MSFlexGrid1.Col = 1: MSFlexGrid1.Text = "图书总数"
    MSFlexGrid1.Col = 2: MSFlexGrid1.Text = "现存数量"
    MSFlexGrid1.Col = 3: MSFlexGrid1.Text = "借出本数"
    '建立网格数据
    For i = 1 To intCount
        MSFlexGrid1.Row = i: MSFlexGrid1.Col = 0
        MSFlexGrid1.RowHeight(i) = 250
        MSFlexGrid1.Text = LendStr(i)
        For j = 1 To 3
            MSFlexGrid1.Col = j
            MSFlexGrid1.Text = LendStat(i, j)
        Next j
    Next i
End Sub