变量的使用使以不变应万变成为可能（组合窗体的实现）

          以前只是听老师说，变量是个很神奇的东西，可以节省很多的经历来经营代码。而且，用得好的话可以起到一劳永逸，以不变应万变的效果。但是纸上得来终觉浅，一直没能深入的理解，直到最近我遇到了组合查询这个问题，尝试着第一次将变量及变量数组应用到了我的思想中来，让这句话有了灵魂。

       在这之前让我们来梳理一下组合查询的思路。所谓组合查询就是通过两个或两个以上条件的组合对数据库中的信息进行查找和提取的过程（至少我是这样认为的）。拿我们机房系统来举个例子：

       首先是有一个具有卡号、学号、姓名、性别等等内容的一个集合，通过条件集合：=、<、>、<>进行判断最后将结果进行：或和与的整合得到我们要的结果集。

       整体思路好像并不难，分析下来也还是挺清晰，只是我们要清楚，这里面的卡号，学号等内容非常多，加上要对其进行”=“等的判断就有点乱了，所以当时我就想到了能否用一个变量或者变量数组来替代这些内容，答案是肯定的。下面就是实现的过程：
    Dim objCn As New ADODB.Connection, objRs As New ADODB.Recordset
    Dim strSql1 As String
    Dim i As Integer
    Dim pickUp As String
   
    Dim a(5) As String                          '定义一个用来存放表中字段的数组
    Dim b(4) As String                          '定义一个用来存放<,>,=,<>的数组
    Dim s(3) As String                          '定义一个用来存放变量的数组
    Dim t(3) As String                          '定义一个用来存放变量的数组
   
    t(0) = 0                                         ’给数组变量赋初值
    t(1) = 0
    t(2) = 0
    s(0) = 0
    s(1) = 0
    s(2) = 0
    a(0) = "cardno"                            ’给变量赋值数据表中的字段名
    a(1) = "studentName"
    a(2) = "ondate"
    a(3) = "ontime"
    a(4) = "computer"
    b(0) = "="                                     ’给变量赋值判断条件
    b(1) = "<"
    b(2) = ">"
    b(3) = "<>"
    
    '制作表头
    MSHFlexGrid1.TextMatrix(0, 0) = "卡号"
    MSHFlexGrid1.TextMatrix(0, 1) = "姓名"
    MSHFlexGrid1.TextMatrix(0, 2) = "上机日期"
    MSHFlexGrid1.TextMatrix(0, 3) = "上机时间"
    MSHFlexGrid1.TextMatrix(0, 4) = "机房号"
   
    '连接数据库
    objCn.ConnectionString = "Driver={SQL Server};Server=DANGHUICHAO-PC;Database=charge_sys;userID=admin;PWD=admin"
    objCn.Open
    

       '进行条件判断
       '对ImageCombo中的各个变量赋值
       
        If ImageCombo1.Text = "卡号" Then
            s(0) = a(0)
        ElseIf ImageCombo1.Text = "姓名" Then
            s(0) = a(1)
        ElseIf ImageCombo1.Text = "上机日期" Then
            s(0) = a(2)
        ElseIf ImageCombo1.Text = "上机时间" Then
            s(0) = a(3)
        ElseIf ImageCombo1.Text = "机房号" Then
            s(0) = a(4)
        End If
       
        If ImageCombo5.Text = "卡号" Then
            s(0) = a(0)
        ElseIf ImageCombo5.Text = "姓名" Then
            s(0) = a(1)
        ElseIf ImageCombo5.Text = "上机日期" Then
            s(0) = a(2)
        ElseIf ImageCombo5.Text = "上机时间" Then
            s(0) = a(3)
        ElseIf ImageCombo5.Text = "机房号" Then
            s(0) = a(4)
        End If
       
         If ImageCombo10.Text = "卡号" Then
            s(0) = a(0)
        ElseIf ImageCombo10.Text = "姓名" Then
            s(0) = a(1)
        ElseIf ImageCombo10.Text = "上机日期" Then
            s(0) = a(2)
        ElseIf ImageCombo10.Text = "上机时间" Then
            s(0) = a(3)
        ElseIf ImageCombo10.Text = "机房号" Then
            s(0) = a(4)
        End If
       
        '对条件进行判断
       
        If ImageCombo2.Text = "=" Then
            t(0) = b(0)
        ElseIf ImageCombo2.Text = "<" Then
            t(0) = b(1)
        ElseIf ImageCombo2.Text = ">" Then
            t(0) = b(2)
         ElseIf ImageCombo2.Text = "<>" Then
            t(0) = b(3)
        End If
        If ImageCombo6.Text = "=" Then
            t(1) = b(0)
        ElseIf ImageCombo6.Text = "<" Then
            t(1) = b(1)
        ElseIf ImageCombo6.Text = ">" Then
            t(1) = b(2)
        ElseIf ImageCombo6.Text = "<>" Then
            t(1) = b(3)
        End If
       
        If ImageCombo10.Text = "=" Then
            t(2) = b(0)
        ElseIf ImageCombo10.Text = "<" Then
            t(2) = b(1)
        ElseIf ImageCombo10.Text = ">" Then
            t(2) = b(2)
        ElseIf ImageCombo10.Text = "<>" Then
            t(2) = b(3)
        End If
       
        '判断“与”“或”的条件
        If ImageCombo4.Text = "" Then
        

        strSql1 = "select * from OnLine_Info where " & s(0) & "" & t(0) & " '" & Text1.Text & "'"
        Set objRs.ActiveConnection = objCn
            objRs.Open (strSql1), objCn, 3                         '定义指针类型为3
            pickUp = objRs.RecordCount                            '提取记录集中的行数
           
                If objRs.EOF = True Then
                    MsgBox "记录为空！"
                End If
                   
                    MSHFlexGrid1.Rows = 1                             '赋初值给msh行数，使它每次ImageCombo1_Click事件都能重新动态安排行数，否则行数也会越来越多。
                    For i = 1 To pickUp                               '循环的过程，实现动态添加信息到msh中
                      
                        MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1
       
                        MSHFlexGrid1.TextMatrix(i, 0) = Trim(objRs.Fields(0))
                        MSHFlexGrid1.TextMatrix(i, 1) = Trim(objRs.Fields(3))
                        MSHFlexGrid1.TextMatrix(i, 2) = Trim(objRs.Fields(4))
                        MSHFlexGrid1.TextMatrix(i, 3) = Trim(objRs.Fields(5))
                        MSHFlexGrid1.TextMatrix(i, 4) = Trim(objRs.Fields(6))
                       
                        objRs.MoveNext
                   
                    Next i
                    pickUp = 0                                         '重新赋初值给变量pickUp为0，这样可以避免循环变的越来越来多
                   
                    Exit Sub
            End If
        objCn.Close

最后将这些判断的结果做”与“或”或“的判断就实现组合查询的功能。

If ImageCombo4.Text = "或" And ImageCombo8.Text = "" Then
   
        objCn.ConnectionString = "Driver={SQL Server};Server=DANGHUICHAO-   PC;Database=charge_sys;userID=admin;PWD=admin"
        objCn.Open
        strSql1 = "select * from OnLine_Info where " & s(0) & "" & t(0) & " '" & Text1.Text & "' Or " & s(1) & "" & t(1) & " '" & Text2.Text & "'"
       

这样就实现了任你如何变幻，我只要对变量进行设定，就能满足你的所有变幻，做到以不变应万变。