机房收费系统之组合查询

由开始的头脑无措，到现在的玩转。确实是需要经历的。

这个组合查询在操作员和一般用户中多次用到，之前在学生也碰到过选择查询，但是用的check控件，查询也比较单一。而在机房用到的组合查询是用combo控件进行的多行条件查询，也是学习到一个新技能。

下面是关于组合查询的流程图：

一：流程图

1学生信息组合查询：

2上机统计信息组合查询流程图：

3收取金额记录组合查询流程图：

二：过程

这里拿学生查询记录组合查询为例：

1首先要对combo进行设置，可选择项，代码如下：

 Dim i, j, k
    For i = 0 To 2
        With Combofieldname1(i)
            .AddItem "卡号"
            .AddItem "学号"
            .AddItem "姓名"
            .AddItem "系别"
            .AddItem "性别"
            .AddItem "上机日期"
            .AddItem "上机时间"
            
        End With
     
    Next i
    
    For j = 0 To 2
        With Combooperation1(j)
            .AddItem "="
            .AddItem "<"
            .AddItem ">"
            .AddItem "<>"
        End With
    Next j
    
    For k = 0 To 1
        With Combo7(k)
            .AddItem "与"
            .AddItem "或"
        End With
    Next k

2这里要对后两行进行输入限制，只有当选择了组合查询之后，才可以进行多行选择，否则只查询第一行。

 '判断如果组合查询没有选择，则下面一排的控件不可选
    
    If Combo7(0).Text = "" Then
        Combofieldname1(1).Enabled = False
        Combofieldname1(2).Enabled = False
        Combooperation1(1).Enabled = False
        Combooperation1(2).Enabled = False
        txtinquire1(1).Enabled = False
        txtinquire1(2).Enabled = False
        Combo7(1).Enabled = False
    Else
    
        If Combo7(1).Text = "" Then
            Combofieldname1(2).Enabled = False
            Combooperation1(2).Enabled = False
        Else
    
    '若组合查询选择了，则激活下面一排控件的可选权
       End If

3这里选择了组合查询，也就是组合查询combo的单击事件。所以对组合查询控件进行代码限制。

 If Combo7(0).Text <> Trim("") Then
        Combofieldname1(1).Enabled = True
        Combooperation1(1).Enabled = True
        Combo7(1).Enabled = True
        txtinquire1(1).Enabled = True
    End If
    
    If Combo7(1).Text <> Trim("") Then
        Combofieldname1(2).Enabled = True
        Combooperation1(2).Enabled = True
        txtinquire1(2).Enabled = True
    End If

4我们这里对combo设置的语言是汉字，但是计算机是看不懂Chinese的。所以要进行语言的“翻译”

Public Function field(qq As String) As String
    Select Case qq
        Case "卡号"
            field = "cardno"
        Case "学号"
            field = "studentno"
        Case "姓名"
            field = "studentname"
        Case "系别"
            field = "department"
        Case "性别"
            field = "sex"
        Case "上机日期"
            field = "ondate"
        Case "上机时间"
            field = "ontime"
        Case "与"
            field = "and"
        Case "或"
            field = "or"
    End Select
End Function

5准备工作做好了，下面就要进行查询。和以往一样，先对其进行判空。

If Combofieldname1(0).Text = "" Then
        MsgBox "请选择字段名！", vbOKOnly + vbExclamation, "警告"
        Combofieldname1(0).SetFocus
    Else
        
        If Combooperation1(0).Text = "" Then
            MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"
            Combooperation1(0).SetFocus
        Else
       
            If txtinquire1(0).Text = "" Then
                MsgBox "请输入查询内容！", vbOKOnly + vbExclamation, "警告"
                txtinquire1(0).SetFocus
            Else


'第二行
                
                    If Combofieldname1(1).Text = "" Then
                        MsgBox "请选择第二行字段名！", 48, "提示"
                        Combofieldname1(1).SetFocus
                        Exit Sub
                    ElseIf Combooperation1(1).Text = "" Then
                        MsgBox "请选择第二行操作符！", 48, "提示"
                        Combooperation1(1).SetFocus
                        Exit Sub
                    ElseIf txtinquire1(1).Text = "" Then
                        MsgBox "请输入第二行要查询的内容！", 48, "提示"
                        txtinquire1(1).SetFocus
                        Exit Sub
                    Else

6大家可以看到，如果是三行的话，重复度相当高。并且感觉很笨重。所以可以对其有一句简单的命令：

'查询第一行
    
    '判空
    
    If Trim(Combo1(0).Text) = "" Or Trim(Combo2(0).Text) = "" Or Trim(Text1(0).Text) = "" Then
        MsgBox "请选择您所查询的信息", 48, "提示"
        Exit Sub
    Else

'查询第二行
                
                    If Trim(Combo1(1).Text) = "" Or Trim(Combo2(1).Text) = "" Or Trim(Text1(1).Text) = "" Then
                        MsgBox "请选择您所查的信息", 48, "提示”"
                        Exit Sub
                    Else

7若不为空，则进行查询判断，是否存在：

txtSQL = "select * from line_Info where " & field(Combofieldname1(0).Text) & Trim(Combooperation1(0).Text) & "'" & Trim(txtinquire1(0).Text) & "'"
            Set mrc = ExecuteSQL(txtSQL, MsgText)
            
            If mrc.EOF Then
                MsgBox "该条件的数据不存在", 48, "提示"
                'Exit Sub
            Else
            
                If Combo7(0).Text = "" Then
                    GoTo case1
                Else

8这里的goto case1算是对信息显示的一个地点标识粗鲁理解为：如果存在，则去case1：

case1:
                
            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) = "消费金额"
                
                Do While Not mrc.EOF '循环
                    .Rows = .Rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1))
                    .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(2))
                    .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(3))
                    .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(4))
                    .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(5))
                    .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(6))
                    .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(7))
                    .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(8))
                    .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(9))
                    .TextMatrix(.Rows - 1, 9) = Trim(mrc.Fields(11))
                    mrc.MoveNext
                Loop
            End With
            End If

9这里要注意一句：mrc.movenext

如有遗失，后果自负。

组合查询总结到此结束：一定要缕清你的脑子哦