[前言]
机房收费管理系统中有三个窗体用到了组合查询，今天小编以学生基本信息维护这个窗体为例，向大家介绍一下“组合查询的那些事”。
一.思路
二.代码实现
<span style="color:#333333;">Private Sub cmdInq_Click()
'定义数据集对象
Dim mrc As ADODB.Recordset
'定义字符串变量，表示查询语句
Dim txtSQL As String
'定义字符串变量，返回查询信息
Dim Msgtext As String
txtSQL = "select * from student_Info where "
'判断查询条件是否为空，用到了函数
isempty0 = Testtxt(Combo1(0).Text) Or Testtxt(Combo2(0).Text) Or Testtxt(Text1(0).Text)
If isempty0 = False Then
MsgBox "请输入查询条件", 0 + 48, "提示"
Exit Sub
Else
'一个条件的查询
txtSQL = txtSQL & "" & fieldname(Combo1(0).Text) & "" & Combo2(0).Text & "'" & Trim(Text1(0).Text) & "'"
End If
If Combo3(0).Text <> "" Then
isempty1 = isempty0 And Testtxt(Combo1(1).Text) And Testtxt(Combo2(1).Text) And Testtxt(Text1(1).Text)
If isempty1 = False Then
MsgBox "请输入查询条件", 0 + 48, "提示"
Exit Sub
Else
'添加第二个条件的查询
txtSQL = txtSQL & " " & fieldname1(Combo3(0).Text) & " " & fieldname(Combo1(1).Text) & Combo2(1).Text & "'" & Trim(Text1(1).Text) & "'"
End If
End If
If Combo3(1).Text <> "" Then
isempty2 = isempty0 And isempty1 And Testtxt(Combo1(2).Text) And Testtxt(Combo2(2).Text) And Testtxt(Text1(2).Text)
If isempty2 = False Then
MsgBox "请输入查询条件", 0 + 48, "提示"
Exit Sub
Else
'添加第三个条件的查询
txtSQL = txtSQL & "" & fieldname1(Combo3(1).Text) & " " & fieldname(Combo1(2).Text) & Combo2(2).Text & "'" & Trim(Text1(2).Text) & "'"
End If
End If
Set mrc = ExecuteSQL(txtSQL, Msgtext)
If mrc.EOF = True Then
MsgBox "没有查询到结果，可能会你输入的信息不存在，或者信息矛盾"
Else
MSHFlexGrid1.AllowUserResizing = 3 '用户可以用鼠标来重新调整行和列的大小
With MSHFlexGrid1
.Rows = 1
.CellAlignment = 4 '单元内容居中对齐
.ColAlignment = 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 mrc.EOF = False
.Rows = .Rows + 1
.CellAlignment = 4 '单元内容居中对齐
.ColAlignment = 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) = Trim(mrc.Fields(12))
.TextMatrix(.Rows - 1, 12) = Trim(mrc.Fields(13))
mrc.MoveNext
Loop
End With
End If
End Sub
Private Sub cmdMod_Click()
'通过判断卡号这列是否为数字，来判断是否选中了记录
If Not IsNumeric(Trim(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, 2))) Then
frmModStuInfo.Show
Else
MsgBox "没有选定要修改的内容", vbOKOnly, "提示！"
Exit Sub
End If
'修改窗体弹出
' frmModifyinformation.Show
'setparent frmModifyinformation.hWnd, frmMain.hWnd
'卸载本窗体
Unload Me
End Sub
Private Sub MSHFlexGrid1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
With MSHFlexGrid1
.Row = .MouseRow '把选中行的值给.row
nowrow = .Row '把鼠标指定行给nowrow
.Col = 0
.ColSel = .Cols - 1
End With
End Sub
Private Sub MSHFlexGrid1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
With MSHFlexGrid1
If nowrow > 0 Then
.RowSel = nowrow '把行值给单元格内的起始终止行
.ColSel = .Cols - 1
End If
End With
End Sub
'定义一个检查文本框是否为空的函数
Public Function Testtxt(strTxt As String) As Boolean
'判断是否为空
If strTxt = "" Then
Testtxt = False
Else
Testtxt = True
End If
End Function
Private Function fieldname(strfieldname As String) As String
Select Case strfieldname
Case "卡号"
fieldname = "cardno"
Case "学号"
fieldname = "studentno"
Case "姓名"
fieldname = "studentname"
Case "性别"
fieldname = "sex"
Case "系别"
fieldname = "department"
Case "年级"
fieldname = "grade"
Case "班级"
fieldname = "class"
End Select
End Function
Private Function fieldname1(strfieldname As String) As String
Select Case strfieldname
Case "与"
fieldname1 = "and"
Case "或"
fieldname1 = "or"
End Select
End Function</span><span style="color:#ff0000;">
</span>
【小结】
~~思路很重要，理清思路可以为后期的实现节省很多时间。
~~遇到问题，在解决无果后，可以暂且放放，回头再看，如果在此基础上还解决不了的话，就去请教"大神” 啦。
~~ 同一功能有多种代码实现方式，简化的代码可以帮我们省去很多空间和时间 ，这需要我们的探索。