机房收费系统之组合查询

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

这个组合查询在操作员和一般用户中多次用到，之前在学生也碰到过选择查询，但是用的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 SelectEnd 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

如有遗失，后果自负。

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