机房收费系统之中的组合查询也是非常有意思的一个环节,这11个小控件的逻辑关系如果没有弄清思路,是很容易被他们玩的团团转的。下面就说说我的组合查询的思路。
第一次看到这个窗体的感觉就是,我去,怎么这么多的控件,然后就是点点点,各种尝试,找找里面的关系,那么关系是什么呢?如图,我先创建了界面,这样比较容易解释一点。
首先,因为这么多查询不知道从哪里入手,那么就从最简单的第一行开始做,在combo1,combo2,text1的内容不完整的情况下,我们要做的就是让用户把内容补充完整,代码很简单,如下:
</p><p></p><pre name="code" class="html">
If Combo1.Text = "" Then
MsgBox "请选择字段!", vbOKOnly + vbInformation, "提示信息"
Combo1.SetFocus
ElseIf Combo2.Text = "" Then
MsgBox "请选择操作符!", vbOKOnly + vbInformation, "提示信息"
Combo2.SetFocus
ElseIf Text1.Text = "" Then
MsgBox "请输入要查询的内容!", vbOKOnly + vbInformation, "提示信息"
Text1.SetFocus
Else
....
End If
我的提示比较繁琐,但是我觉得这样更加精确一点,当内容全部满足了以后,我们要做的就是进行满足这三个控件的条件的查询,
txtSQL1 = "select * from line_Info where "
txtSQL1 = txtSQL1 & " " & Field(Combo1.Text) & " " & Combo2.Text & "'" & Trim(Text1.Text) & "'"
Set mrc1 = ExecuteSQL(txtSQL1, Msgtext1)
If mrc1.EOF Then
MsgBox "查询无信息,请重新选择查询条件!", vbOKOnly + vbInformation, "提示信息"
Else
With MSFlexGrid1
.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) = "备注"
Do While Not mrc1.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc1.Fields(1))
.TextMatrix(.Rows - 1, 1) = Trim(mrc1.Fields(3))
.TextMatrix(.Rows - 1, 2) = Trim(mrc1.Fields(6))
.TextMatrix(.Rows - 1, 3) = Trim(mrc1.Fields(7))
.TextMatrix(.Rows - 1, 4) = Trim(mrc1.Fields(8))
.TextMatrix(.Rows - 1, 5) = Trim(mrc1.Fields(9))
.TextMatrix(.Rows - 1, 6) = Trim(mrc1.Fields(11))
.TextMatrix(.Rows - 1, 7) = Trim(mrc1.Fields(12))
.TextMatrix(.Rows - 1, 8) = Trim(mrc1.Fields(13))
mrc1.MoveNext
Loop
End With
End if
第一次的查询可以这样,那么第二次,第三次就可以在第一次的基础上修改添加内容,跟第一次查询类似,只不过我这里多加了一个关系,完整的代码如下:
<span style="white-space:pre"> </span>
If Combo7.Text = "与" Or Combo7.Text = "或" Then
If Trim(Combo7.Text) = "与" Then
a = " and "
Else
a = " or "
End If
If Combo3.Text = "" Then
MsgBox "请选择字段!", vbOKOnly + vbInformation, "提示信息"
Combo3.SetFocus
ElseIf Combo4.Text = "" Then
MsgBox "请选择操作符!", vbOKOnly + vbInformation, "提示信息"
Combo4.SetFocus
ElseIf Text2.Text = "" Then
MsgBox "请输入要查询的内容!", vbOKOnly + vbInformation, "提示信息"
Text2.SetFocus
Else
txtSQL1 = txtSQL1 & " " & a & " " & Field(Combo3.Text) & " " & Combo4.Text & "'" & Trim(Text2.Text) & "'"
If Combo7.Text <> "" And Combo3.Text <> "" And Combo4.Text <> "" And Text2.Text <> "" Then
'第二次查询可以满足查询时,第三次查询不可用
If Combo8.Text = "与" Or Combo8.Text = "或" Then
If Trim(Combo8.Text) = "与" Then
b = " and "
Else
b = " or "
End If
If Combo5.Text = "" Then
MsgBox "请选择字段!", vbOKOnly + vbInformation, "提示信息"
Combo5.SetFocus
ElseIf Combo6.Text = "" Then
MsgBox "请选择操作符!", vbOKOnly + vbInformation, "提示信息"
Combo6.SetFocus
ElseIf Text3.Text = "" Then
MsgBox "请输入要查询的字段!", vbOKOnly + vbInformation, "提示信息"
Text3.SetFocus
Else
txtSQL1 = txtSQL1 & " " & b & " " & Field(Combo5.Text) & "" & Combo6.Text & "'" & Trim(Text3.Text) & "'"
End If
End If
End If
End If
End If
Set mrc1 = ExecuteSQL(txtSQL1, Msgtext1)
If mrc1.EOF Then
MsgBox "查询无信息,请重新选择查询条件!", vbOKOnly + vbInformation, "提示信息"
Else
With MSFlexGrid1
.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) = "备注"
Do While Not mrc1.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc1.Fields(1))
.TextMatrix(.Rows - 1, 1) = Trim(mrc1.Fields(3))
.TextMatrix(.Rows - 1, 2) = Trim(mrc1.Fields(6))
.TextMatrix(.Rows - 1, 3) = Trim(mrc1.Fields(7))
.TextMatrix(.Rows - 1, 4) = Trim(mrc1.Fields(8))
.TextMatrix(.Rows - 1, 5) = Trim(mrc1.Fields(9))
.TextMatrix(.Rows - 1, 6) = Trim(mrc1.Fields(11))
.TextMatrix(.Rows - 1, 7) = Trim(mrc1.Fields(12))
.TextMatrix(.Rows - 1, 8) = Trim(mrc1.Fields(13))
mrc1.MoveNext
Loop
End With
End if
组合查询到这里就基本上结束了,但是需要注意的是,我的窗体之中一开始只有combo1,combo2,text1,combo7可以使用,只有当combo7中的关系被确定以后,那么下一次的查询才可以继续使用,如果不是这个思路的话,那么代码还是需要稍微修改一下的。
小结:组合查询看起来挺吓人的,但是认真思考了以后,你就会发现其实也没有想象中那么吓人,当不知道从哪里开始入手的时候,就随便找个地方开始入手,做着做着就会发现有了方向;当不知道如何实现这么多查询的时候,就从最简单的第一步查询开始做,满足第一次查询以后,往第一次查询之中添加第二次,第三次的查询,没有做不到的事情,只有没有去做。’