机房收费系统@组合查询

     组合查询真的让我研究了很长时间，从开始没有什么思路到阅读大量的博客，之后形成自己的代码，之后一步步的优化，终于成就了一个自己认为还可以的代码。若有好的建议欢迎指出，下面是要实现的功能界面：

    '定义变量
    Dim txtSQL As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset


Private Sub cmdExit_Click()
    Unload Me
End Sub

'将所选择的组合框里的内容统一用数组表示，从而减少代码量
Private Sub cmdQuery_Click()

    For i = 0 To 2 '字段名的添加
        With cmbzdm(i)
            .AddItem "卡号", 0
            .AddItem "姓名", 1
            .AddItem "上机日期", 2
            .AddItem "上机时间", 3
            .AddItem "机房号", 4

            '判断选择的索引值进而转化为数据库中可识别的字段
            Select Case Index
                Case 0
                    changezdm(i) = "cardno"
                Case 1
                    changezdm(i) = "studentname"
                Case 2
                    changezdm(i) = "ondate"
                Case 3
                    changezdm(i) = "ontime"
                Case 4
                    changezdm(i) = "computer"
            End Select

        End With

        With cmbczf(i) '操作符的添加、这些字段在数据库中也是通用的，所以可以不用转换
            .AddItem "=", 0
            .AddItem ">", 1
            .AddItem "<", 2
            .AddItem "<>", 3

        End With

    Next i

    For j = 0 To 1 '需要将界面中的文字转换为数据库中可识别的字段
        With cmbzhgx(j)
            .AddItem "或", 0
            .AddItem "与", 1

            Select Case Index
                Case 0
                    changegx(j) = "or"
                Case 1
                    changegx(j) = "and"
            End Select
       End With
    Next j

   '执行查询

   '判断查询条件是否为空
    If TestTxt(cmbzdm(0).Text) Then
        MsgBox "请输入要查询的内容！", vbOKOnly + vbInformation, "提示！"
        cmbzdm(0).SetFocus
    End If

   '若只有一个查询条件
   If cmbzhgx(0) = "" And cmbzhgx(1) = "" Then

        txtSQL = "select cardno .studentno,ondate,ontime,computer from online_info where " & cmbzdm(0).Text & _
                  cmbczf(0).Text & "'" & Trim(txtCxnr(0)) & "'"
        Set mrc = executeSQL(txtSQL, msgtesxt)

   Else '若有两个查询条件（此时用if嵌套语句会有效减少代码量）

        If cmbzhgx(0) <> "" And cmbzhgx(1) = "" Then
            '判断条件是否为空
            If TestTxt(cmbzdm(1)) Then MsgBox "请输入要查询的内容！", vbOKOnly + vbInformation, "提示！" And cmbzdm(1).SetFocus   '判断查询条件是否为空

            txtSQL = txtSQL & cmbzdm(1).Text & cmbczf(1).Text & " & Trim(txtCxnr(1)) & "

        Else '若有三个查询条件

            If cmbzhgx(0) <> "" And cmbzhgx(1) <> "" Then
                '判断条件是否为空
                If TestTxt(cmbzdm(2)) Then MsgBox "请输入要查询的内容！", vbOKOnly + vbInformation, "提示！" And cmbzdm(2).SetFocus

                txtSQL = txtSQL And cmbzdm(2).Text & cmbczf(2).Text & "& trim(txtcxnr(2)) &"
            Else '若组合关系越级选择则给出提示

                If cmbzhgx(0) = "" And cmbzhgx(1) <> "" Then
                    MsgBox "请选择上一个组合关系之后再继续", vbOKOnly + vbInformation, "提示！"
                    cmbzhgx(0).SetFocus
                End If
            End If
        End If
   End If


   '显示记录数据
   mshf.Rows = mrc.RecordCount + 1

    With mshf
        While mrc.EOF = False
            .Rows = .Rows + 1
            .TextMatrix(.Row - 1, 0) = mrc!cardno
            .TextMatrix(.Row - 1, 1) = mrc!studentname
            .TextMatrix(.Row - 1, 2) = mrc!ondate
            .TextMatrix(.Row - 1, 3) = mrc!OnTime
            .TextMatrix(.Row - 1, 4) = mrc!computer
            mrc.MoveNext
        Wend
    End With
    mrc.Close
End Sub

Private Sub Form_Load()

    With mshf
        .Rows = 2
        .Cols = 5
        .CellAlignment = 4
        .TextMatrix(1, 0) = "卡号"
        .TextMatrix(1, 1) = "姓名"
        .TextMatrix(1, 2) = "上机日期"
        .TextMatrix(1, 3) = "上机时间"
        .TextMatrix(1, 4) = "机房号"
     End With

End Sub