机房组合查询我感觉是在这上面时间最多的一个，不是太难但就是有很多的不容易让人想到的地方。下面我就来总结一下。
1、查询条件strA单独查询，组合关系comboZh1符合的时候strA和StrB一起查询，组合关系comboZh2符合的时候strA、StrB、StrC三行条件一起查询。
2、当字段名是性别的时候操作符中的符号只能是“=”和“< >”并且提示只能输入“男” 或“女”。当字段名为时间或者是日期的时候，txtYc1要有限制只能输入日期或者是时间
3、组合查询ComboZh1为空的时候StrB不能用或者是隐藏。只有单击ComboZh1的时候ComboZh2才能用。否则无效。（这个想着很是费劲，一定要好好的思考。。。）
接下来就是展示一下我的代码。
先应该在新建的模块中写到（这会为你省掉很多的麻烦。。。）
<span style="font-size:14px;">Option Explicit
Public Function FiledName(strFiledName As String) As String
'将字段名转化成数据库表中的字段名
Select Case strFiledName
Case "卡号"
FiledName = "cardno"
Case "学号"
FiledName = "studentNo"
Case "姓名"
FiledName = "studentName"
Case "性别"
FiledName = "sex"
Case "系别"
FiledName = "department"
Case "年级"
FiledName = "grade"
Case "班级"
FiledName = "class"
End Select
End Function
'将操作符转化成数据库表中的字段名
Public Function Operate(strOperate As String)
Select Case strOperate
Case "="
Operate = "="
Case "<"
Operate = "<"
Case ">"
Operate = ">"
Case "<>"
Operate = "<>"
End Select
End Function
'将连接符转化成数据库表中的字段名
Public Function Connect(strConnect As String) As String
Select Case strConnect
Case "或"
Connect = "or"
Case "与"
Connect = "and"
Case ""
Connect = ""
End Select
End Function
Function chkkey(t As String, k As Integer) As Integer '只能输入数字
chkkey = k
If k = 46 And InStr(t, ".") = 0 Then
Exit Function
End If
If k = 8 Then
Exit Function
End If
If k < 48 Or k > 57 Then
chkkey = 0
End If
End Function</span>
<span style="font-size:14px; font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"><span style="white-space:pre"> <span style="font-size:14px; white-space: pre;">之后</span></span>就是要在窗体中写你的代码了。</span>
<span style="font-size:14px;">Option Explicit
Dim nowrow As Integer
Private Sub ComboZd1_Click()
'选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
Select Case ComboZd1.Text
Case "性别"
ComboOpt1.Clear
ComboOpt1.AddItem "="
ComboOpt1.AddItem "<>"
Case "姓名"
ComboOpt1.Clear
ComboOpt1.AddItem "="
ComboOpt1.AddItem "<>"
Case "系别"
ComboOpt1.Clear
ComboOpt1.AddItem "="
ComboOpt1.AddItem "<>"
Case "卡号"
ComboOpt1.Clear
ComboOpt1.AddItem "="
ComboOpt1.AddItem ">"
ComboOpt1.AddItem "<"
ComboOpt1.AddItem "<>"
Case "学号"
ComboOpt1.Clear
ComboOpt1.AddItem "="
ComboOpt1.AddItem ">"
ComboOpt1.AddItem "<"
ComboOpt1.AddItem "<>"
Case "年级"
ComboOpt1.Clear
ComboOpt1.AddItem "="
ComboOpt1.AddItem ">"
ComboOpt1.AddItem "<"
ComboOpt1.AddItem "<>"
Case "班级"
ComboOpt1.Clear
ComboOpt1.AddItem "="
ComboOpt1.AddItem ">"
ComboOpt1.AddItem "<"
ComboOpt1.AddItem "<>"
Case ""
ComboOpt1.Clear
ComboOpt1.AddItem ""
End Select
'性别选择男女
If ComboZd1.Text = "性别" Then
MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ", vbOKOnly + vbExclamation, "提示"
End If
End Sub
Private Sub ComboZd2_Click()
'选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
Select Case ComboZd2.Text
Case "性别"
ComboOpt2.Clear
ComboOpt2.AddItem "="
ComboOpt2.AddItem "<>"
Case "姓名"
ComboOpt2.Clear
ComboOpt2.AddItem "="
ComboOpt2.AddItem "<>"
Case "系别"
ComboOpt2.Clear
ComboOpt2.AddItem "="
ComboOpt2.AddItem "<>"
Case "卡号"
ComboOpt2.Clear
ComboOpt2.AddItem "="
ComboOpt2.AddItem ">"
ComboOpt2.AddItem "<"
ComboOpt2.AddItem "<>"
Case "学号"
ComboOpt2.Clear
ComboOpt2.AddItem "="
ComboOpt2.AddItem ">"
ComboOpt2.AddItem "<"
ComboOpt2.AddItem "<>"
Case "年级"
ComboOpt2.Clear
ComboOpt2.AddItem "="
ComboOpt2.AddItem ">"
ComboOpt2.AddItem "<"
ComboOpt2.AddItem "<>"
Case "班级"
ComboOpt2.Clear
ComboOpt2.AddItem "="
ComboOpt2.AddItem ">"
ComboOpt2.AddItem "<"
ComboOpt2.AddItem "<>"
Case ""
ComboOpt2.Clear
ComboOpt2.AddItem ""
End Select
'性别选择男女
If ComboZd2.Text = "性别" Then
MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ", vbOKOnly + vbExclamation, "提示"
End If
End Sub
Private Sub ComboZd3_Click()
'选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
Select Case ComboZd3.Text
Case "性别"
ComboOpt3.Clear
ComboOpt3.AddItem "="
ComboOpt3.AddItem "<>"
Case "姓名"
ComboOpt3.Clear
ComboOpt3.AddItem "="
ComboOpt3.AddItem "<>"
Case "系别"
ComboOpt3.Clear
ComboOpt3.AddItem "="
ComboOpt3.AddItem "<>"
Case "卡号"
ComboOpt3.Clear
ComboOpt3.AddItem "="
ComboOpt3.AddItem ">"
ComboOpt3.AddItem "<"
ComboOpt3.AddItem "<>"
Case "学号"
ComboOpt3.Clear
ComboOpt3.AddItem "="
ComboOpt3.AddItem ">"
ComboOpt3.AddItem "<"
ComboOpt3.AddItem "<>"
Case "年级"
ComboOpt3.Clear
ComboOpt3.AddItem "="
ComboOpt3.AddItem ">"
ComboOpt3.AddItem "<"
ComboOpt3.AddItem "<>"
Case "班级"
ComboOpt3.Clear
ComboOpt3.AddItem "="
ComboOpt3.AddItem ">"
ComboOpt3.AddItem "<"
ComboOpt3.AddItem "<>"
Case ""
ComboOpt3.Clear
ComboOpt3.AddItem ""
End Select
'性别选择男女
If ComboZd3.Text = "性别" Then
MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ", vbOKOnly + vbExclamation, "提示"
End If
End Sub
Private Sub ComboZh1_Click()
'组合关系是1个时候第2行的选择显示
If ComboZh1.Text <> "" Then
ComboZd2.Enabled = True
ComboOpt2.Enabled = True
txtYc2.Enabled = True
ComboZh2.Enabled = True
Else
ComboZd2.Enabled = False
ComboOpt2.Enabled = False
txtYc2.Enabled = False
ComboZh2.Enabled = False
ComboZd3.Enabled = False
ComboOpt3.Enabled = False
txtYc3.Enabled = False
txtYc2.Text = ""
txtYc3.Text = ""
End If
End Sub
Private Sub ComboZh2_Click()
'组合关系是2个时候第3行的选择显示
If ComboZh2.Text <> "" Then
ComboZd3.Enabled = True
ComboOpt3.Enabled = True
txtYc3.Enabled = True
End If
End Sub
te Sub Command1_Click()
'Command1是清空
ComboOpt1.Text = ""
ComboOpt2.Text = ""
ComboOpt3.Text = ""
ComboZd1.Text = ""
ComboZd2.Text = ""
ComboZd3.Text = ""
ComboZh1.Text = ""
ComboZh2.Text = ""
txtYc1.Text = ""
txtYc2.Text = ""
MSHFGd.Clear
End Sub
Private Sub Command2_Click()
'Command2是查询
Dim txtSQl As String
Dim MsgText As String
Dim mrc As ADODB.Recordset 'mrc是student数据库
Dim strA As String '定义三个保存SQL语句的字符串 代表第一种查询条件
Dim strB As String '代表第二种
Dim strC As String '代表第三种
txtSQl = "select * from student_Info where "
strA = txtSQl & FiledName(ComboZd1.Text) & Operate(ComboOpt1.Text) & "'" & Trim(txtYc1.Text) & "'"
strB = strA & "" & Connect(ComboZh1.Text) & " " & FiledName(ComboZd2.Text) & Operate(ComboOpt2.Text) & "'" & Trim(txtYc2.Text) & "'"
strC = strB & Connect(ComboZh2.Text) & " " & FiledName(ComboZd2.Text) & Operate(ComboOpt3.Text) & "'" & Trim(txtYc3.Text) & "'"
If Trim(ComboZh1.Text) = "" Then '没有组合关系的时候就是一行查询条件
If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Then
MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
txtSQl = strA
Set mrc = ExecuteSQL(txtSQl, MsgText)
End If
End If
If Trim(ComboZh1.Text) <> "" Then '有组合关系的时候是两行和起来的查询条件
If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Or Trim(ComboZd2.Text) = "" Or Trim(ComboOpt2.Text) = "" Or Trim(txtYc2.Text) = "" Then
MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
txtSQl = strB
Set mrc = ExecuteSQL(txtSQl, MsgText)
End If
End If
If Trim(ComboZh2.Text) <> "" Then '两个组合关系时候的查询条件 三行查询条件一起
If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Or Trim(ComboZd2.Text) = "" Or Trim(ComboOpt2.Text) = "" Or Trim(txtYc2.Text) = "" Or Trim(ComboZd3.Text) = "" Or Trim(ComboOpt3.Text) = "" Or Trim(txtYc3.Text) = "" Then
MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
txtSQl = strC
Set mrc = ExecuteSQL(txtSQl, MsgText)
End If
End If
If mrc.EOF Then
MSHFGd.Clear
MsgBox "暂无查询结果", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
With MSHFGd '表示的是设置MSHFlexGrid控件第i行，第j列的文本内容（注意标题算第一行）
.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) = "备注"
.TextMatrix(0, 10) = "类型"
.TextMatrix(0, 11) = "日期"
.TextMatrix(0, 12) = "时间"
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(0))
.TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
.TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))
.TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5))
.TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(6))
.TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(3))
.TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(10))
.TextMatrix(.Rows - 1, 9) = Trim(mrc.Fields(8))
.TextMatrix(.Rows - 1, 10) = Trim(mrc.Fields(14))
.TextMatrix(.Rows - 1, 11) = mrc.Fields(12) & ""
.TextMatrix(.Rows - 1, 12) = mrc.Fields(13) & ""
mrc.MoveNext
Loop
End With
AdjustColWidth frmcxsjbxxwh, MSHFGd '调节列宽
mrc.Close
End Sub
Private Sub Command3_Click()
'Command3_是退出
Unload Me
End Sub
Private Sub Command4_Click()
'修改信息
If MSHFGd.MouseRow = 0 Then
MsgBox "请选择学生", vbOKOnly + vbExclamation, "警告"
Else
frmcxsjbxxwh.Hide
SetParent frmcModifySI.hWnd, frmmain.Picture1.hWnd '‘显示修改学生信息窗体
frmcModifySI.Width = 12180
frmcModifySI.Height = 9255
End If
End Sub
Private Sub Form_Activate()
'把当前不是活动的窗体最小化
Dim a As Form
For Each a In Forms
If a.Name <> frmmain.Name And a.Name <> Me.Name Then
a.WindowState = 1
End If
Next
End Sub
Private Sub Form_Load()
With MSHFGd '表示的是设置MSHFlexGrid控件第i行，第j列的文本内容（注意标题算第一行）
.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) = "备注"
.TextMatrix(0, 10) = "类型"
.TextMatrix(0, 11) = "日期"
.TextMatrix(0, 12) = "时间"
End With
ComboZd1.AddItem "学号" '添加字段名
ComboZd1.AddItem "姓名"
ComboZd1.AddItem "卡号"
ComboZd1.AddItem "系别"
ComboZd1.AddItem "年级"
ComboZd1.AddItem "班级"
ComboZd1.AddItem "性别"
ComboZd1.AddItem ""
ComboZd2.AddItem "学号"
ComboZd2.AddItem "姓名"
ComboZd2.AddItem "卡号"
ComboZd2.AddItem "系别"
ComboZd2.AddItem "年级"
ComboZd2.AddItem "班级"
ComboZd2.AddItem "性别"
ComboZd2.AddItem ""
ComboZd3.AddItem "学号"
ComboZd3.AddItem "姓名"
ComboZd3.AddItem "卡号"
ComboZd3.AddItem "系别"
ComboZd3.AddItem "年级"
ComboZd3.AddItem "班级"
ComboZd3.AddItem "性别"
ComboZd3.AddItem ""
ComboZh1.AddItem "或"
ComboZh1.AddItem "与"
ComboZh1.AddItem ""
ComboZh2.AddItem "或" '添加组合关系
ComboZh2.AddItem "与"
ComboZh2.AddItem ""
ComboZd2.Enabled = False
ComboZd3.Enabled = False
ComboOpt2.Enabled = False
ComboOpt3.Enabled = False
txtYc2.Enabled = False
txtYc3.Enabled = False
ComboZh2.Enabled = False
End Sub
Public Sub MSHFgd_Mousedown(button As Integer, Shift As Integer, X As Single, Y As Single)
With MSHFGd
.Row = .MouseRow '当前活动单元就是鼠标按下的单元
nowrow = .Row '把单元传给nowrow
End With
End Sub
Public Sub MSHFgd_mouseup(button As Integer, Shift As Integer, X As Single, Y As Single)
With MSHFGd
.RowSel = nowrow
End With
End Sub
Private Sub txtYc1_Click()
txtYc1.SetFocus
End Sub
Private Sub txtYc2_Click()
txtYc2.SetFocus
End Sub
Private Sub txtYc3_Click()
txtYc3.SetFocus
End Sub
Private Sub txtYc1_KeyPress(KeyAscii As Integer)
'确定输入的是数字
If (ComboZd1.Text = "卡号") Then
KeyAscii = chkkey(txtYc1, KeyAscii)
Else
If (ComboZd1.Text = "学号") Then
KeyAscii = chkkey(txtYc1, KeyAscii)
End If
End If
End Sub
Private Sub txtYc2_KeyPress(KeyAscii As Integer)
'确定输入的是数字
If (ComboZd2.Text = "卡号") Then
KeyAscii = chkkey(txtYc2, KeyAscii)
Else
If (ComboZd2.Text = "学号") Then
KeyAscii = chkkey(txtYc2, KeyAscii)
End If
End If
End Sub
Private Sub txtYc3_KeyPress(KeyAscii As Integer)
'确定输入的是数字
If (ComboZd3.Text = "卡号") Then
KeyAscii = chkkey(txtYc3, KeyAscii)
Else
If (ComboZd3.Text = "学号") Then
KeyAscii = chkkey(txtYc3, KeyAscii)
End If
End If
End Sub
</span>
好了这就是我的代码了。虽然过程很是艰辛但是结果还是很美好的。