frmborrow.frm

本人自己编写的!!!!!看看这样做的怎么样

Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public jyflag As Integer

Private Sub cmd_Click()
    Dim smeg As String
    Dim mrcc As ADODB.Recordset
    Dim msgtext As String
    Dim ssh As String
    Dim jssj As Date
    Dim cnn As ADODB.Connection
    Dim cmddele As New ADODB.Command
    Select Case brflagadd
         Case 1                              '借书
               If Trim(Combo1.Text & "") = "" Then
                   smeg = "书籍的ISBN编号"
                   MsgBox smeg, vbOKOnly + vbExclamation, "警告"
               End If
               If Trim(Combo2.Text & "") = "" Then
                   smeg = "借书证号不能为空！"
                   MsgBox smeg, vbOKOnly + vbExclamation, "警告"
               End If
               If Trim(txtItem(10).Text & "") = "" Then
                   smeg = "索书号不能为空！"
                   MsgBox smeg, vbOKOnly + vbExclamation, "警告"
                   Exit Sub
               Else
                   txtsql = "select* from jy where 索书号='" & Trim(txtItem(10).Text) & "'"
                   Set mrcc = ExecuteSQL(txtsql, msgtext)
                   If mrcc.EOF = False Then
                       MsgBox "此索书号的书已借出！", vbOKOnly + vbExclamation, "警告"
                       mrcc.Close
                       Exit Sub
                    End If
               End If
               isbn = Combo1.Text
               jszh = Combo2.Text
               ssh = Trim(txtItem(10).Text)
               jssj = Date
               Set cnn = New ADODB.Connection
               cnn.Open ConnectString
               Set cmddele.ActiveConnection = cnn
               cmddele.CommandText = "jy_insert"
               cmddele.CommandType = adCmdStoredProc
               cmddele.Parameters.Append cmddele.CreateParameter("jszh", adChar, adParamInput, 16, jszh)
               cmddele.Parameters.Append cmddele.CreateParameter("isbn", adChar, adParamInput, 16, isbn)
               cmddele.Parameters.Append cmddele.CreateParameter("ssh", adChar, adParamInput, 10, ssh)
               cmddele.Parameters.Append cmddele.CreateParameter("jssj", adChar, adParamInput, 10, [jssj])
               cmddele.Parameters.Append cmddele.CreateParameter("flag", adInteger, adParamOutput)
               cmddele.Execute
               If cmddele("flag") > 0 Then
                   MsgBox "借书成功！", vbOKOnly + vbExclamation, "提示"
                   isbn = Combo1.Text
                   txtsql = "select * from book where isbn='" & isbn & "'"
                   Set mrcc = ExecuteSQL(txtsql, msgtext)
                   If Not mrcc.EOF Then
                    txtItem(0) = mrcc.Fields(1)
                    txtItem(1) = mrcc.Fields(2)
                    txtItem(2) = mrcc.Fields(3)
                    txtItem(3) = CDbl(mrcc.Fields(4))
                    txtItem(4) = CInt(mrcc.Fields(5))
                    txtItem(5) = CInt(mrcc.Fields(6))
                End If
               mrcc.Close
               jszh = Combo2.Text
               txtsql = "select* from xs where 借书证号='" & jszh & "'"
               Set mrcc = ExecuteSQL(txtsql, msgtext)
               If Not mrcc.EOF Then
                     txtItem(6).Text = mrcc.Fields(1)
                     txtItem(7).Text = mrcc.Fields(2)
                     txtItem(8).Text = mrcc.Fields(4)
                     txtItem(9).Text = CStr(mrcc.Fields(5))
                     If mrcc.Fields(3) Then
                         Option1(1).Value = True
                     Else
                         Option1(0).Value = True
                     End If
               End If
               mrcc.Close
               
               txtItem(10).Text = ""
               txtItem(10).SetFocus
             Else
                 MsgBox "无可借的书！", vbOKOnly + vbExclamation, "提示"
             End If
             cnn.Close
          Case 2                           '还书
                ssh = Trim(Combo3.Text)
                Set cnn = New ADODB.Connection
                cnn.Open ConnectString
                Set cmddele.ActiveConnection = cnn
                cmddele.CommandText = "jy_delete"
                cmddele.CommandType = adCmdStoredProc
                cmddele.Parameters.Append cmddele.CreateParameter("ssh", adChar, adParamInput, 10, ssh)
                cmddele.Parameters.Append cmddele.CreateParameter("flag", adInteger, adParamOutput)
                cmddele.Execute
                If cmddele("flag") > 0 Then
                   MsgBox "还书成功！", vbOKOnly + vbExclamation, "提示"
                   For icount = 0 To Combo3.ListCount - 1
                         If Combo3.List(icount) = Combo3.Text And Combo3.Text <> "" Then
                            Combo3.Text = ""
                         End If
                   Next icount
                   For icount = 0 To 9
                        txtItem(icount).Text = ""
                   Next icount
                   Text1.Text = ""
                   Text2.Text = ""
               Else
                   MsgBox "还书失败！", vbOKOnly + vbExclamation, "提示"
               End If
               cnn.Close
            End Select
End Sub

Private Sub cmdExit_Click()
   Unload Me
End Sub

Private Sub Combo1_Click()
   Dim msgtext As String
   strtext = Combo1.Text
   isbn = Combo1.Text
   txtsql = "select* from book where isbn='" & isbn & "'"
   Set mrc = ExecuteSQL(txtsql, msgtext)
   If Not mrc.EOF Then
          txtItem(0).Text = mrc.Fields(1)
          txtItem(1).Text = mrc.Fields(2)
          txtItem(2).Text = mrc.Fields(3)
          txtItem(3).Text = CDbl(mrc.Fields(4))
          txtItem(4).Text = CInt(mrc.Fields(5))
          txtItem(5).Text = CInt(mrc.Fields(6))
    End If
      mrc.Close
    txtItem(10).Text = ""
    txtItem(10).SetFocus
End Sub

Private Sub Combo2_Click()
   Dim msgtext As String
   jszh = Combo2.Text
   txtsql = "select* from xs where 借书证号='" & jszh & "'"
   Set mrc = ExecuteSQL(txtsql, msgtext)
   If Not mrc.EOF Then
         txtItem(6).Text = mrc.Fields(1)
         txtItem(7).Text = mrc.Fields(2)
         txtItem(8).Text = mrc.Fields(4)
         txtItem(9).Text = CStr(mrc.Fields(5))
         If mrc.Fields(3) Then
               Option1(1).Value = True
        Else
              Option1(0).Value = True
        End If
    End If
    mrc.Close
    txtItem(10).SetFocus
End Sub

Private Sub Combo3_Click()
 Dim ssh As String
 Dim msgtext As String
 If brflagadd = 2 Then
     ssh = Trim(Combo3.Text)
     txtsql = "select book.ISBN,书名,作者,出版社,价格,复本量,库存量,xs.借书证号,姓名,专业名,性别,出生时间,借书数,索书号 from jy, book, xs where jy.借书证号 = xs.借书证号 And jy.isbn = book.isbn and 索书号='" & Combo3.Text & "'"
  
        Set mrc = ExecuteSQL(txtsql, msgtext)
      ftag = 0
      Do While Not mrc.EOF
            If ftag = 0 Then
            ftag = 1
            Text1.Text = mrc.Fields(0)
            txtItem(0).Text = mrc.Fields(1)
            txtItem(1).Text = mrc.Fields(2)
            txtItem(2).Text = mrc.Fields(3)
            txtItem(3).Text = CDbl(mrc.Fields(4))
            txtItem(4).Text = CInt(mrc.Fields(5))
            txtItem(5).Text = CInt(mrc.Fields(6))
            Text2.Text = mrc.Fields(7)
            txtItem(6).Text = mrc.Fields(8)
            txtItem(7).Text = mrc.Fields(9)
            txtItem(8).Text = mrc.Fields(11)
            txtItem(9).Text = CStr(mrc.Fields(12))
            If mrc.Fields(10) Then
               Option1(1).Value = True
            Else
               Option1(0).Value = True
            End If
          End If
          mrc.MoveNext
        Loop
        mrc.Close
    End If
    
End Sub

Private Sub Form_Activate()
  Dim msgtext As String
  Dim ftag As Integer
  Dim jszh1 As String
  Dim isbn1 As String
  If jyflag = 0 Then
     jyflag = 1
     Select Case brflagadd
             Case 1
                  Combo1.Visible = True
                  Combo2.Visible = True
                  Text1.Visible = False
                  Text2.Visible = False
                  txtItem(10).Visible = True
                  txtItem(10).Text = ""
                  txtsql = "select* from book"
                  Set mrc = ExecuteSQL(txtsql, msgtext)
                  ftag = 0
                  Do While Not mrc.EOF
                     Combo1.AddItem Trim(mrc.Fields(0))
                     If ftag = 0 Then
                        ftag = 1
                        Combo1.Text = mrc.Fields(0)
                        isbn = Combo1.Text
                        txtItem(0).Text = mrc.Fields(1)
                        txtItem(1).Text = mrc.Fields(2)
                        txtItem(2).Text = mrc.Fields(3)
                        txtItem(3).Text = CDbl(mrc.Fields(4))
                        txtItem(4).Text = CInt(mrc.Fields(5))
                        txtItem(0).Text = CInt(mrc.Fields(6))
                    End If
                    mrc.MoveNext
                 Loop
                 mrc.Close
                 txtsql = "select * from xs"
                 Set mrc = ExecuteSQL(txtsql, msgtext)
                 ftag = 0
                 If Not mrc.EOF Then
                 Do While Not mrc.EOF
                       Combo2.AddItem Trim(mrc.Fields(0))
                       If ftag = 0 Then
                          ftag = 1
                          Combo2.Text = mrc.Fields(0)
                          jszh = Combo1.Text
                          txtItem(6).Text = mrc.Fields(1)
                          txtItem(7).Text = mrc.Fields(2)
                          txtItem(8).Text = mrc.Fields(4)
                          txtItem(9).Text = CStr(mrc.Fields(5))
                          If mrc.Fields(3) Then
                                 Option1(1).Value = True
                          Else
                                 Option1(0).Value = True
                          End If
                       End If
                       mrc.MoveNext
                 Loop
             End If
             mrc.Close
             Combo3.Visible = False
             txtItem(10).SetFocus
             Case 2
                  Combo1.Visible = False
                  Combo2.Visible = False
                  Text1.Visible = True
                  Text2.Visible = True
                  txtItem(10).Visible = False
                  Combo3.Visible = True
                  txtsql = "select book.ISBN,书名,作者,出版社,价格,复本量,库存量,xs.借书证号,姓名,专业名,性别,出生时间,借书数,索书号  from jy,book,xs where jy.借书证号=xs.借书证号 and jy.ISBN=book.ISBN"
                  Set mrc = ExecuteSQL(txtsql, msgtext)
                  ftag = 0
                  Do While Not mrc.EOF
                       Combo3.AddItem Trim(mrc.Fields(13))
                       If ftag = 0 Then
                          ftag = 1
                          Combo3.Text = Trim(mrc.Fields(13))
                          Text1.Text = mrc.Fields(0)
                          txtItem(0).Text = mrc.Fields(1)
                          txtItem(1).Text = mrc.Fields(2)
                          txtItem(2).Text = mrc.Fields(3)
                          txtItem(3).Text = CDbl(mrc.Fields(4))
                          txtItem(4).Text = CInt(mrc.Fields(5))
                          txtItem(5).Text = CInt(mrc.Fields(6))
                          Text2.Text = mrc.Fields(7)
                          txtItem(6).Text = mrc.Fields(8)
                          txtItem(7).Text = mrc.Fields(9)
                          txtItem(8).Text = mrc.Fields(11)
                          txtItem(9).Text = CStr(mrc.Fields(12))
                          If mrc.Fields(10) Then
                              Option1(1).Value = True
                          Else
                              Option1(0).Value = True
                          End If
                        End If
                        mrc.MoveNext
                    Loop
                    mrc.Close
                End Select
            End If
End Sub

Private Sub Form_Load()
   jyflag = 0
   Select Case brflagadd
          Case 1
               Me.Caption = Me.Caption & "借  书"
               cmd.Visible = True
               cmd.Enabled = True
               cmd.Caption = "借  书(&B)"
          Case 2
               Me.Caption = Me.Caption & "借  书"
               cmd.Visible = True
               cmd.Enabled = True
               cmd.Caption = "还  书(&R)"
    End Select
    
End Sub