机房收费系统之组合查询

组合查询是机房中比较复杂的一部分，要考虑到选择内容，输入内容以及组合关系这三方面，很是麻烦，我们可以将这些分为三层查询，第一层是第一行要查询内容；第二层是前两行查询内容及第一个组合关系；第三层是所有查询内容以及两个组合关系，这样一层层查询就方便多了。

'有一层查询
        If Combo1(0).Text = "" Or Combo2(0).Text = "" Or txtinquire1.Text = "" Then
            MsgBox "请把条件填写完整！", vbOKOnly + vbExclamation, "警告"
        Else
        txtSQL = "select * from line_info where " & name1(Combo1(0).Text) & Combo2(0).Text & "'" & Trim(txtinquire1.Text) & "'"

        End If



  '有两层查询
    If Combo3(0).Text <> "" Then
        If Combo1(0).Text = "" Or Combo2(0).Text = "" Or txtinquire1.Text = "" Or _
            Combo1(1).Text = "" Or Combo2(1).Text = "" Or txtinquire2.Text = "" Then
            MsgBox "请把条件填写完整！", vbOKOnly + vbExclamation, "警告"
        Else
            If Combo3(0).Text = "与" Then
                txtSQL = "select * from line_info where " & name1(Combo1(0).Text) & Combo2(0).Text & "'" & txtinquire1.Text & "' " & " " & "and" & " " & _
                name1(Combo1(1).Text) & Combo2(1).Text & "'" & txtinquire2.Text & "'"

          Else      '两层或与关系（2种）
               If Combo3(0).Text = "或" Then
                    txtSQL = "select * from line_info where " & name1(Combo1(0).Text) & Combo2(0).Text & "'" & txtinquire1.Text & "' " & " " & "or" & " " & _
                    name1(Combo1(1).Text) & Combo2(1).Text & "'" & txtinquire2.Text & "'"

            End If
        End If
     End If

'有三层查询
    If Combo3(0).Text <> "" And Combo3(1).Text <> "" Then
        If Combo1(0).Text = "" Or Combo2(0).Text = "" Or txtinquire1.Text = "" Or _
            Combo1(1).Text = "" Or Combo2(1).Text = "" Or txtinquire2.Text = "" Or _
            Combo1(2).Text = "" Or Combo2(2).Text = "" Or txtinquire3.Text = "" Then
            MsgBox "请把条件填写完整！", vbOKOnly + vbExclamation, "警告"
        Else                                                                    '三层的或与关系（4种）
            '与与关系
            If Combo3(0).Text = "与" And Combo3(1).Text = "与" Then
                txtSQL = "select * from line_info where " & name1(Combo1(0).Text) & Combo2(0).Text & "'" & txtinquire1.Text & "'" & " " & "And" & " " & _
                name1(Combo1(1).Text) & Combo2(1).Text & "'" & txtinquire2.Text & "'" & " " & "And" & " " & _
                name1(Combo1(2).Text) & Combo2(2).Text & "'" & txtinquire3.Text & "' "

            End If

            '与或关系
            If Combo3(0).Text = "与" And Combo3(1).Text = "或" Then
                txtSQL = "select * from line_info where " & name1(Combo1(0).Text) & Combo2(0).Text & "'" & txtinquire1.Text & "'" & " " & "And" & " " & _
                name1(Combo1(1).Text) & Combo2(1).Text & "'" & txtinquire2.Text & "'" & " " & "Or" & " " & _
                name1(Combo1(2).Text) & Combo2(2).Text & "'" & txtinquire3.Text & "' "

            End If

            '或或关系
            If Combo3(0).Text = "或" And Combo3(1).Text = "或" Then
                txtSQL = "select * from line_info where " & name1(Combo1(0).Text) & Combo2(0).Text & "'" & txtinquire1.Text & "'" & " " & "or" & " " & _
                name1(Combo1(1).Text) & Combo2(1).Text & "'" & txtinquire2.Text & "'" & " " & "Or" & " " & _
                name1(Combo1(2).Text) & Combo2(2).Text & "'" & txtinquire3.Text & "' "

            End If

            '或与关系
            If Combo3(0).Text = "或" And Combo3(1).Text = "与" Then
                txtSQL = "select * from line_info where " & name1(Combo1(0).Text) & Combo2(0).Text & "'" & txtinquire1.Text & "'" & " " & "or" & " " & _
                name1(Combo1(1).Text) & Combo2(1).Text & "'" & txtinquire2.Text & "'" & " " & "and" & " " & _
                name1(Combo1(2).Text) & Combo2(2).Text & "'" & txtinquire3.Text & "' "

            End If
        End If
     End If
  End If

在组合查询中还有一个难点就是选中某一行就行修改，那这个怎么实现呢？又怎么让选中的内容显示在另一个窗体中呢？
首先我们先判断行数，行数为0就提示选择要修改的内容，行数大于0说明查询到了信息，用myflexgrid.TextMatrix(.RowSel,0)来确定选中的那一行（myflexgrid是我用的名字，具体名字参看自己设定）
然后我用了一个笨办法，设定了几个全局变量，然后在下一个窗体中调用出来
选定那一行的信息在进行修改。更好的方法需要小伙伴们自己去探索了。

With myflexgrid
        If .RowSel = 0 Then        '行数为0
            MsgBox "请选择要修改的学生信息", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        Else
            If .RowSel > 0 Then   '行数不为0
                txtSQL = "select * from student_Info where cardno='" & Trim(.TextMatrix(.RowSel, 0)) & "'"
                Set mrc = ExecuteSQL(txtSQL, MsgText)
            End If
        End If
    End With

Private Sub myflexgrid_Click()
             '将数据存储到几个全局变量中
 a = myflexgrid.TextMatrix(myflexgrid.Row, 1)
 b = myflexgrid.TextMatrix(myflexgrid.Row, 2)
 c = myflexgrid.TextMatrix(myflexgrid.Row, 3)
 d = myflexgrid.TextMatrix(myflexgrid.Row, 4)
 e = myflexgrid.TextMatrix(myflexgrid.Row, 5)
 f = myflexgrid.TextMatrix(myflexgrid.Row, 6)
 g = myflexgrid.TextMatrix(myflexgrid.Row, 7)
 h = myflexgrid.TextMatrix(myflexgrid.Row, 8)
 i = myflexgrid.TextMatrix(myflexgrid.Row, 9)
 j = myflexgrid.TextMatrix(myflexgrid.Row, 10)
 k = myflexgrid.TextMatrix(myflexgrid.Row, 11)
 l = myflexgrid.TextMatrix(myflexgrid.Row, 12)
 m = myflexgrid.TextMatrix(myflexgrid.Row, 15)
 n = myflexgrid.TextMatrix(myflexgrid.Row, 12)
 o = myflexgrid.TextMatrix(myflexgrid.Row, 13)
End Sub