机房收费系统之组合查询

           

    在没有开始敲组合查询的时候就已经了解了很多天了，查了很多资料，又看了很多人的文章，终于自己敲出来了，下面以  学生基本信息维护  为例

                                                

                                              

                   字段名为Combo1、Combo2、Combo3

               操作符为Combo4、Combo5、Combo6

               要查询的内容Text1、Text2、Text3

               组合关系为Combo7、Combo8

Private Sub Command2_Click()

'单行查询

     If Combo1.Text = "" Or Combo4.Text = "" Or Text1.Text = "" Then
       MsgBox "请保证查询信息完整！", vbOKOnly + vbExclamation, "提示"
       Exit Sub
     End If


     txtSQL = "select * from student_Info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'"
     Set mrc = ExecuteSQL(txtSQL, MsgText)

     If mrc.EOF = True Then
        MsgBox "该条件的数据不存在！", vbOKOnly + vbExclamation, "提示"
        Call Cancel
        Combo1.SetFocus
        Exit Sub
     Else
       Call Add
     End If

'两行查询
 If Not Combo7.Text = "" Then
    If Combo1.Text = "" Or Combo2.Text = "" Or Combo4.Text = "" Or Combo5.Text = "" Or Text1.Text = "" Or Text2.Text = "" Then
       MsgBox "请保证查询信息完整！", vbOKOnly + vbExclamation, "提示"
       Exit Sub
    Else
      Select Case Combo7
        Case "与"
        a = "and"
        Case "或"
        a = "or"
      End Select

       txtSQL = txtSQL & a & " " & Trim(field(Combo2.Text)) & Trim(Combo5.Text) & "'" & Trim(Text2.Text) & "'"
       Set mrc = ExecuteSQL(txtSQL, MsgText)

       If mrc.EOF = True Then
         MsgBox "该条件的数据不存在！", vbOKOnly + vbExclamation, "提示"
         Call Cancel
         Combo1.SetFocus
         Exit Sub
       Else
         Call Add
       End If
    End If

 End If

'三行查询
 If Not Combo8.Text = "" Then
  If Combo1.Text = "" Or Combo2.Text = "" Or Combo3.Text = "" Or Combo4.Text = "" Or Combo5.Text = "" Or Combo6.Text = "" Or Text1.Text = "" Or Text2.Text = "" Or Text3.Text = "" Then
       MsgBox "请保证查询信息完整！", vbOKOnly + vbExclamation, "提示"
       Exit Sub

    Else
    Select Case Combo8
        Case "与"
        b = "and"
        Case "或"
        b = "or"
    End Select
       txtSQL = txtSQL & b & " " & Trim(field(Combo3.Text)) & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"
       Set mrc = ExecuteSQL(txtSQL, MsgText)
       MsgBox MsgText


       If mrc.EOF = True Then
         MsgBox "该条件的数据不存在！", vbOKOnly + vbExclamation, "提示"
         Call Cancel
         Combo1.SetFocus
         Exit Sub
       Else
         Call Add
       End If
    End If
 End If

End Sub

    Cancel是我自己定义的一个清除过程，因为要反复用到，用的时候调用一下就好了，可以省事很多。

  

Public Function Cancel()
    Combo1.Text = ""
    Combo2.Text = ""
    Combo3.Text = ""
    Combo4.Text = ""
    Combo5.Text = ""
    Combo6.Text = ""
    Combo7.Text = ""
    Combo8.Text = ""
    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""


    With MSHFlexGrid1
     .Rows = 2
     .CellAlignment = 4
     .TextMatrix(0, 0) = ""
     .TextMatrix(0, 1) = ""
     .TextMatrix(0, 2) = ""
     .TextMatrix(0, 3) = ""
     .TextMatrix(0, 4) = ""
     .TextMatrix(0, 5) = ""
     .TextMatrix(0, 6) = ""
     .TextMatrix(0, 7) = ""
     .TextMatrix(0, 8) = ""
     .TextMatrix(0, 9) = ""
     .TextMatrix(0, 10) = ""
     .TextMatrix(0, 11) = ""
     .TextMatrix(0, 12) = ""

     .TextMatrix(1, 0) = ""
     .TextMatrix(1, 1) = ""
     .TextMatrix(1, 2) = ""
     .TextMatrix(1, 3) = ""
     .TextMatrix(1, 4) = ""
     .TextMatrix(1, 5) = ""
     .TextMatrix(1, 6) = ""
     .TextMatrix(1, 7) = ""
     .TextMatrix(1, 8) = ""
     .TextMatrix(1, 9) = ""
     .TextMatrix(1, 10) = ""
     .TextMatrix(1, 11) = ""
     .TextMatrix(1, 12) = ""


    End With
End Function

   

   Add是我定义的一个添加过程，同样也是为了能够方便反复调用

Public Function Add()
  With MSHFlexGrid1
            .Rows = 1
            .CellAlignment = 4
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "姓名"
            .TextMatrix(0, 2) = "卡号"
            .TextMatrix(0, 3) = "金额"
            .TextMatrix(0, 4) = "系别"
            .TextMatrix(0, 5) = "年级"
            .TextMatrix(0, 6) = "班级"
            .TextMatrix(0, 7) = "性别"
            .TextMatrix(0, 8) = "状态"
            .TextMatrix(0, 9) = "备注"
            .TextMatrix(0, 10) = "类型"
            .TextMatrix(0, 11) = "日期"
            .TextMatrix(0, 12) = "时间"

          Do While Not mrc.EOF
             .Rows = .Rows + 1
             .TextMatrix(.Rows - 1, 0) = mrc.Fields(1)
             .TextMatrix(.Rows - 1, 1) = mrc.Fields(2)                      
             .TextMatrix(.Rows - 1, 2) = mrc.Fields(0)
             .TextMatrix(.Rows - 1, 3) = mrc.Fields(7)
             .TextMatrix(.Rows - 1, 4) = mrc.Fields(4)
             .TextMatrix(.Rows - 1, 5) = mrc.Fields(5)
             .TextMatrix(.Rows - 1, 6) = mrc.Fields(6)
             .TextMatrix(.Rows - 1, 7) = mrc.Fields(3)
             .TextMatrix(.Rows - 1, 8) = mrc.Fields(10)
             .TextMatrix(.Rows - 1, 9) = mrc.Fields(8)
             .TextMatrix(.Rows - 1, 10) = mrc.Fields(14)
             .TextMatrix(.Rows - 1, 11) = Format(mrc.Fields(12), "yyyy-mm-dd")
             .TextMatrix(.Rows - 1, 12) = Format(mrc.Fields(13), "hh:mm:ss")

            mrc.MoveNext
          Loop


        End With

        mrc.Close
End Function

     

    field 是为了把汉字转换成数据库中的字段名，为了方便查询数据库而定义的一个过程。

Public Function field(combox As String) As String
   Select Case combox
      Case "卡号"
        field = "cardno"
      Case "姓名"
        field = "studentName"
      Case "学号"
        field = "studentNo"
      Case "金额"
        field = "cash"
      Case "系别"
        field = "department"
      Case "年级"
        field = "grade"
      Case "班级"
        field = "class"
      Case "性别"
        field = "sex"
      Case "状态"
        field = "status"
      Case "备注"
        field = "Ischeck"
      Case "类型"
        field = "type"
      Case "日期"
        field = "date"
      Case "时间"
        field = "time"
   End Select
End Function

我的整体思路是： 如果只进行单行查询，则第二三行不可用。

                如果进行两行组合查询，则第三行不可用。

                只有当进行三行查询时，三行均可使用。

   

所以还有几行代码

Private Sub Combo7_Click()
   If Combo7.Text <> Trim("") Then
      Combo2.Enabled = True
      Combo5.Enabled = True
      Combo8.Enabled = True
      Text2.Enabled = True
   End If


End Sub


Private Sub Combo8_Click()
   If Combo8.Text <> Trim("") Then
       Combo3.Enabled = True
       Combo6.Enabled = True
       Text3.Enabled = True
   End If

End Sub

Private Sub Form_Load()  If Combo7.Text = Trim("") Then     Combo2.Enabled = False     Combo3.Enabled = False     Combo5.Enabled = False     Combo6.Enabled = False     Combo8.Enabled = False     Text2.Enabled = False     Text3.Enabled = False  End If   If Combo8.Text = Trim("") Then    Combo3.Enabled = False    Combo6.Enabled = False    Text3.Enabled = False  End If

    其他几个组合查询的窗体就都大同小异了，也不是很难吧