frmbooks.frm

vb与access数据库的操作实例

      LabelEdit       =   1
      Sorted          =   -1  'True
      Style           =   7
      HotTracking     =   -1  'True
      SingleSel       =   -1  'True
      ImageList       =   "ImageList1"
      BorderStyle     =   1
      Appearance      =   1
      MousePointer    =   99
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "宋体"
         Size            =   9
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
End
Attribute VB_Name = "FrmBooks"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public nTypeId As Integer   '分类编号
Public bHaveSon As Boolean  '是否有子节点
Public Focuskey As String
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub Cmd_Add_Click()
  '只能在根节点下面添加书籍，判断是否为根节点
  If Focuskey = "a0" Then '根结点
    MsgBox ("只能在最下级添加图书")
    Exit Sub
  End If
  If bHaveSon Then
    MsgBox ("只能在最下级添加图书")
    Exit Sub
  End If
  '将Modify变量设置为False，表示当前状态为插入新记录
  FrmBookEdit.Modify = False
  '初始化FrmBookEdit信息
  FrmBookEdit.txtBookNo = ""
  FrmBookEdit.txtBookName = ""
  FrmBookEdit.txtAuthor = ""
  FrmBookEdit.txtPublisher = ""
  FrmBookEdit.txtLocation = ""
  FrmBookEdit.txtPrice = "0"
  FrmBookEdit.txtDescription = ""
  FrmBookEdit.nBookTypeId = CurBookType.TypeId
  FrmBookEdit.txtSubType = CurBookType.TypeName
  FrmBookEdit.txtType = CurBookType.GetUpperName(CurBookType.TypeId)
  '启动编辑窗体
  FrmBookEdit.Show 1
  '刷新DataGrid表格的内容
  GridRefresh
End Sub

Private Sub Cmd_Back_Click()
  Unload Me
End Sub

Private Sub Cmd_Borrow_Click()
  '使用Adodc1.Recordset.EOF判断当前是否选择了记录
  If Adodc1.Recordset.EOF = True Then
    MsgBox "请选择记录"
    Exit Sub
  End If
  '通过Adodc1.Recordset.Fields()读取Adodc1记录源中的当前数据
  FrmRecordView.nType = 2 '设置类型为借阅归还记录查看
  FrmRecordView.TmpBNo = Trim(Adodc1.Recordset.Fields(0))
  FrmRecordView.Caption = "图书借阅归还记录"
  FrmRecordView.txtBookNo = Trim(Adodc1.Recordset.Fields(0))
  FrmRecordView.txtBookName = Trim(Adodc1.Recordset.Fields(1))
  '设置借阅状态为"全部"
  FrmRecordView.ComboFlag.Visible = True
  FrmRecordView.ComboFlag.ListIndex = 0
  '显示窗体
  FrmRecordView.Show 1
End Sub

Private Sub Cmd_Close_Click()
  Unload Me
End Sub

'单击“删除”按钮
Private Sub Cmd_Del_Click()
  Dim TmpNo As String
  '使用Adodc1.Recordset.EOF判断当前是否选择了记录
  If Adodc1.Recordset.EOF = True Then
    MsgBox "请选择记录"
    Exit Sub
  End If
  '通过Adodc1.Recordset.Fields()读取Adodc1记录源中的当前数据
  TmpNo = Trim(Adodc1.Recordset.Fields(0))
  '询问用户是否确认删除
  If MsgBox("是否删除当前行?", vbYesNo, "确认") = vbYes Then
    Call MyBookInfo.Delete(TmpNo)
    MsgBox "删除成功"
  End If
  '刷新DataGrid表格的内容
  GridRefresh
End Sub

Private Sub Cmd_In_Click()
  '查看入库记录
  Call Sleep(100) '以毫秒为单位
  '使用Adodc1.Recordset.EOF判断当前是否选择了记录
  If Adodc1.Recordset.EOF = True Then
    MsgBox "请选择记录"
    Exit Sub
  End If
  '通过Adodc1.Recordset.Fields()读取Adodc1记录源中的当前数据
  FrmRecordView.nType = 1 '设置类型为入库记录查看
  FrmRecordView.TmpBNo = Trim(Adodc1.Recordset.Fields(0))
  FrmRecordView.Caption = "图书入库记录"
  FrmRecordView.txtBookNo = Trim(Adodc1.Recordset.Fields(0))
  FrmRecordView.txtBookName = Trim(Adodc1.Recordset.Fields(1))
  '设置ComboFlag为不可见
  FrmRecordView.Label1.Visible = False
  FrmRecordView.ComboFlag.Visible = False
  '显示窗体
  FrmRecordView.Show 1
End Sub

'单击“修改”按钮
Private Sub Cmd_Modi_Click()
  If Adodc1.Recordset.EOF = True Then
    MsgBox "请选择记录"
    Exit Sub
  End If
  '将Modify变量设置为True，表示当前状态为修改新记录
  FrmBookEdit.Modify = True
  '初始化FrmBookEdit信息
  '设置当前编辑的图书编号
  FrmBookEdit.OriNo = Adodc1.Recordset.Fields(0)
  FrmBookEdit.txtBookNo = Adodc1.Recordset.Fields(0)
  '图书编号不能修改
  FrmBookEdit.txtBookNo.Enabled = False
  '图书名
  FrmBookEdit.txtBookName = Trim(Adodc1.Recordset.Fields(1))
  '图书分类，根据图书编号取得分类编号，而后取得分类名称和对应的上级分类名称
  FrmBookEdit.txtType = MyBookType.GetUpperName((MyBookInfo.GetTypeId(Adodc1.Recordset.Fields(0))))
  FrmBookEdit.txtSubType = MyBookType.GetName((MyBookInfo.GetTypeId(Adodc1.Recordset.Fields(0))))
  '作者
  FrmBookEdit.txtAuthor = Trim(Adodc1.Recordset.Fields(2))
  '出版社
  FrmBookEdit.txtPublisher = Trim(Adodc1.Recordset.Fields(3))
  '图书位置
  FrmBookEdit.txtLocation = Trim(Adodc1.Recordset.Fields(4))
  '价格
  FrmBookEdit.txtPrice = Trim(Adodc1.Recordset.Fields(5))
  '内容简介
  FrmBookEdit.txtDescription = Trim(Adodc1.Recordset.Fields(7))
  '启动编辑窗体
  FrmBookEdit.Show 1
  '刷新DataGrid表格的内容
  Adodc1.Refresh
End Sub

Private Sub Cmd_Store_Click()
  '查看盘点记录
  '使用Adodc1.Recordset.EOF判断当前是否选择了记录
  If Adodc1.Recordset.EOF = True Then
    MsgBox "请选择记录"
    Exit Sub
  End If
  '通过Adodc1.Recordset.Fields()读取Adodc1记录源中的当前数据
  FrmRecordView.nType = 3 '设置类型为盘点记录查看
  FrmRecordView.TmpBNo = Trim(Adodc1.Recordset.Fields(0))
  FrmRecordView.Caption = "图书盘点记录"
  FrmRecordView.txtBookNo = Trim(Adodc1.Recordset.Fields(0))
  FrmRecordView.txtBookName = Trim(Adodc1.Recordset.Fields(1))
  '设置ComboFlag为不可见
  FrmRecordView.Label1.Visible = False
  FrmRecordView.ComboFlag.Visible = False
  '显示窗体
  FrmRecordView.Show 1
End Sub


Private Sub Form_Load()
  Dim TmpNode As Node
  '设置当前的焦点分类
  FocusClassName = "分类名称"
  Focuskey = "a0"
  
  '设置根结点
  Set TmpNode = TreeView1.Nodes.Add(, , "a0", "图书分类", 1, 3)
  TmpNode.Selected = True
  TmpNode.ExpandedImage = 2
  '调用函数将所有图书分类添加到TreeView1中
  Call AddtoTree(TreeView1, "a0")
  '读取当前节点的信息
  TreeView1_Click
End Sub

Private Sub TreeView1_Click()
  '根据关键字获取图书分类信息
  Focuskey = TreeView1.SelectedItem.Key
  CurBookType.TypeName = TreeView1.SelectedItem.Text
  CurBookType.TypeId = Val(Right(TreeView1.SelectedItem.Key, Len(TreeView1.SelectedItem.Key) - 1))
  
  If Focuskey = "a0" Then '根结点
    nTypeId = -1
    Exit Sub
  End If
  '如果包含子结点，则设置图像格式
  bHaveSon = CurBookType.HaveSon(CurBookType.TypeId)
  If bHaveSon = False Then
    TreeView1.SelectedItem.ExpandedImage = 4
    TreeView1.SelectedItem.Image = 4
    TreeView1.SelectedItem.SelectedImage = 5
  End If
  nTypeId = CurBookType.TypeId
  '读取CurBookType中的所有图书分类信息
  CurBookType.GetInfo (CurBookType.TypeId)
  GridRefresh
End Sub

Private Sub GridRefresh()
  If nTypeId = -1 Then  '表示为根目录
    strSearch = ""
  ElseIf bHaveSon = True Then  '表示为有子节点的分类，显示此分类下的所有图书
    strSearch = " ,BookType t WHERE b.TypeId=t.TypeId AND t.UpperId=" + Trim(Str(nTypeId))
  Else  '表示为根节点
    strSearch = " WHERE b.TypeId=" + Trim(Str(nTypeId))
  End If
  '设置ADO控件的连接字符串
  Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
          + App.Path + "\Books.mdb;Persist Security Info=False"
  '刷新DataGrid记录
  Adodc1.RecordSource = "SELECT b.BookNo AS 书号,b.BookName AS 书名,b.Author AS 作者," & _
            "b.Publisher AS 出版社,b.Location AS 书架位置,b.Price AS 单价,b.Total AS 总数量 " & _
            ",b.Description AS 内容介绍 FROM BookInfo b " + strSearch
'  MsgBox (Adodc1.RecordSource)
  Adodc1.Refresh
  DataGrid1.Columns(0).Width = 1000
  DataGrid1.Columns(1).Width = 2000
  DataGrid1.Columns(2).Width = 1200
  DataGrid1.Columns(3).Width = 1600
  DataGrid1.Columns(4).Width = 1000
  DataGrid1.Columns(5).Width = 800
  DataGrid1.Columns(6).Width = 1000
  DataGrid1.Columns(7).Width = 2000
End Sub