先附上我的组合查询设计图一张:
1
控件讲解:在查询内容处放置两个重叠控件,DTpicker和text文本框;字段名列、操作符列、组合关系列combo控件全部禁止输入内容(为用户考虑,只要能选择就不要输入,而且能有效避免处理SQL注入、输入无效内容等处理工作)。
程序事件:
form_load:加载combo控件内容,隐藏dtpicker控件,mdi子窗体居中,控制myFlexGrid控件行数为1。不想写了;下面是整篇代码,加注释:
</pre><pre name="code" class="html"><span style="color:#ff0000;">'****************************************************************************************************************************
'2015 年 8 月 2 日
'今日收获:想要完美的使用一种控件,就要首先了解其各种属性、方法、事件。达到有的放矢,知彼而百战不殆。
' 对combo.clear方法小感
'
'on error resume next : 此语句后的代码,如果产生错误也不提示,继续执行后面的语句。
' 请和 on error goto 语句作比较
'
'DTPicker控件显示时间是从系统获取的,所以改变系统时间格式,控件时间格式也会跟着改变
'
'keyascii码值是可以限制是否输入汉字的
'
'***************************************************************************************************************************
</span>
Option Explicit
Dim X As Integer, Y As Integer, z As Integer
Private Sub cmdCha_Click()
Dim mrc As ADODB.Recordset
Dim txtSQL As String, MsgText As String, Ziduan1 As String, Ziduan2 As String, Ziduan3 As String, Zuhe1 As String, Zuhe2 As String, TXT As String
myFlexGrid.Clear
myFlexGrid.Rows = 1
On Error GoTo a:
<span style="color:#ff0000;"> '空值验证</span>
If Trim(txtZi1.Text) = "" Or Trim(txtCao1.Text) = "" Or Trim(txtCha1.Text) = "" Then
<span style="color:#ff0000;"> '清空输入</span>
DTP1.Visible = False
DTP2.Visible = False
DTP3.Visible = False
txtZi1.Clear
txtZi1.AddItem "教师"
txtZi1.AddItem "注册日期"
txtZi1.AddItem "注册时间"
txtZi1.AddItem "注销日期"
txtZi1.AddItem "注销时间"
txtZi1.AddItem "机器名"
txtZi2.Clear
txtZi2.AddItem "教师"
txtZi2.AddItem "注册日期"
txtZi2.AddItem "注册时间"
txtZi2.AddItem "注销日期"
txtZi2.AddItem "注销时间"
txtZi2.AddItem "机器名"
txtZi3.Clear
txtZi3.AddItem "教师"
txtZi3.AddItem "注册日期"
txtZi3.AddItem "注册时间"
txtZi3.AddItem "注销日期"
txtZi3.AddItem "注销时间"
txtZi3.AddItem "机器名"
txtCao1.Clear
txtCao1.AddItem "="
txtCao1.AddItem "<"
txtCao1.AddItem ">"
txtCao1.AddItem "<>"
txtCao2.Clear
txtCao2.AddItem "="
txtCao2.AddItem "<"
txtCao2.AddItem ">"
txtCao2.AddItem "<>"
txtCao3.Clear
txtCao3.AddItem "="
txtCao3.AddItem "<"
txtCao3.AddItem ">"
txtCao3.AddItem "<>"
txtZu1.Clear
txtZu1.AddItem "与"
txtZu1.AddItem "或"
txtZu2.Clear
txtZu2.AddItem "与"
txtZu2.AddItem "或"
txtCha1.Text = ""
txtCha2.Text = ""
txtCha3.Text = ""
myFlexGrid.Clear
myFlexGrid.Rows = 2
MsgBox "第一行查询条件不能为空!", vbInformation, "温馨提示"
txtZi1.SetFocus
Exit Sub
End If
<span style="color:#ff0000;"> '长度验证</span>
If Len(txtCha1.Text) > 10 Then
MsgBox "查询内容字符长度请小于10位!", vbOKOnly + vbExclamation, "温馨提示" '长度验证
txtCha1.Text = ""
txtCha1.SetFocus
Exit Sub
End If
If Len(txtCha2.Text) > 10 Then
MsgBox "查询内容字符长度请小于10位!", vbOKOnly + vbExclamation, "温馨提示" '长度验证
txtCha2.Text = ""
txtCha2.SetFocus
Exit Sub
End If
If Len(txtCha3.Text) > 10 Then
MsgBox "查询内容字符长度请小于10位!", vbOKOnly + vbExclamation, "温馨提示" '长度验证
txtCha3.Text = ""
txtCha3.SetFocus
Exit Sub
End If
<span style="color:#ff0000;">'字段选择</span>
Select Case txtZi1.Text
Case "教师"
Ziduan1 = "UserID"
Case "注册日期"
Ziduan1 = "LoginDate"
Case "注册时间"
Ziduan1 = "LoginTime"
Case "注销日期"
Ziduan1 = "LogoutDate"
Case "注销时间"
Ziduan1 = "LogoutTime"
Case "机器名"
Ziduan1 = "computer"
End Select
Select Case txtZi2.Text
Case "教师"
Ziduan2 = "UserID"
Case "注册日期"
Ziduan2 = "LoginDate"
Case "注册时间"
Ziduan2 = "LoginTime"
Case "注销日期"
Ziduan2 = "LogoutDate"
Case "注销时间"
Ziduan2 = "LogoutTime"
Case "机器名"
Ziduan2 = "computer"
End Select
Select Case txtZi3.Text
Case "教师"
Ziduan3 = "UserID"
Case "注册日期"
Ziduan3 = "LoginDate"
Case "注册时间"
Ziduan3 = "LoginTime"
Case "注销日期"
Ziduan3 = "LogoutDate"
Case "注销时间"
Ziduan3 = "LogoutTime"
Case "机器名"
Ziduan3 = "computer"
End Select
<span style="color:#ff0000;">'选择组合关系</span>
Select Case txtZu1.Text
Case "与"
Zuhe1 = "and"
Case "或"
Zuhe1 = "or"
Case Else
txtZu1.Clear
txtZu1.AddItem "与"
txtZu1.AddItem "或"
End Select
Select Case txtZu2.Text
Case "与"
Zuhe2 = "and"
Case "或"
Zuhe2 = "or"
Case Else
txtZu2.Clear
txtZu2.AddItem "与"
txtZu2.AddItem "或"
End Select
<span style="color:#ff0000;">'仅有一个查询条件,即不使用组合查询时</span>
If txtZu1.Text = "" And txtZu2.Text = "" Then
'清空冗余的输入
txtZi2.Clear
txtZi2.AddItem "教师"
txtZi2.AddItem "注册日期"
txtZi2.AddItem "注册时间"
txtZi2.AddItem "注销日期"
txtZi2.AddItem "注销时间"
txtZi2.AddItem "机器名"
txtZi3.Clear
txtZi3.AddItem "教师"
txtZi3.AddItem "注册日期"
txtZi3.AddItem "注册时间"
txtZi3.AddItem "注销日期"
txtZi3.AddItem "注销时间"
txtZi3.AddItem "机器名"
txtCao2.Clear
txtCao2.AddItem "="
txtCao2.AddItem "<"
txtCao2.AddItem ">"
txtCao2.AddItem "<>"
txtCao3.Clear
txtCao3.AddItem "="
txtCao3.AddItem "<"
txtCao3.AddItem ">"
txtCao3.AddItem "<>"
<span style="color:#ff0000;">'在Line表中检索信息</span>
txtSQL = "select * from worklog_Info where " & Trim(Ziduan1) & " " & Trim(txtCao1.Text) & " '" & Trim(txtCha1.Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
myFlexGrid.Clear
myFlexGrid.Rows = 2
MsgBox "没有满足此条件的记录!", vbOKOnly + vbExclamation, "温馨提示"
txtZi1.SetFocus
Exit Sub
End If
<span style="color:#ff0000;"> '查询符合两个条件的目录</span>
ElseIf txtZu1.Text <> "" And txtZu2.Text = "" Then
<span style="color:#ff0000;"> '清除冗余输入</span>
txtZi3.Clear
txtZi3.AddItem "教师"
txtZi3.AddItem "注册日期"
txtZi3.AddItem "注册时间"
txtZi3.AddItem "注销日期"
txtZi3.AddItem "注销时间"
txtZi3.AddItem "机器名"
txtCao3.Clear
txtCao3.AddItem "="
txtCao3.AddItem "<"
txtCao3.AddItem ">"
txtCao3.AddItem "<>"
<span style="color:#ff0000;">'双组合查询第二行内容不能为空</span>
If Trim(txtZi2.Text) = "" Or Trim(txtCao2.Text) = "" Or Trim(txtCha2.Text) = "" Then
MsgBox "您选择了双组合查询,请完善第二行查询条件", vbInformation, "温馨提示"
txtZi2.SetFocus
Exit Sub
End If
<span style="color:#ff0000;"> '检索lion表中符合条件的信息</span>
txtSQL = "select * from worklog_Info where " & Trim(Ziduan1) & " " & Trim(txtCao1.Text) & " '" & Trim(txtCha1.Text) & "'" & " " & Zuhe1 & " " & Ziduan2 & " " & Trim(txtCao2.Text) & " " & "'" & Trim(txtCha2.Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
myFlexGrid.Clear
myFlexGrid.Rows = 2
MsgBox "没有满足此条件的记录!", vbOKOnly + vbExclamation, "温馨提示"
Exit Sub
End If
<span style="color:#ff0000;">'查询并显示符合三个条件的记录</span>
ElseIf txtZu1.Text <> "" And txtZu2.Text <> "" Then
<span style="color:#ff0000;">'第二行查询信息不能为空</span>
If Trim(txtZi2.Text) = "" Or Trim(txtCao2.Text) = "" Or Trim(txtCha2.Text) = "" Then
MsgBox "您选择了三组合查询,请完善第二行查询条件", vbInformation, "温馨提示"
Exit Sub
Else
<span style="color:#ff0000;">'第三行查询信息不能为空</span>
If Trim(txtZi3.Text) = "" Or Trim(txtCao3.Text) = "" Or Trim(txtCha3.Text) = "" Then
MsgBox "您选择了三组合查询,请完善第三行查询条件", vbInformation, "温馨提示"
Exit Sub
End If
End If
<span style="color:#ff0000;"> 'lion表中查询</span>
txtSQL = "select * from worklog_Info where " & Ziduan1 & " " & Trim(txtCao1.Text) & "'" & Trim(txtCha1.Text) & "'" & " " & Zuhe1 & " " & Ziduan2 & " " & Trim(txtCao2.Text) & " " & "'" & Trim(txtCha2.Text) & "'" & Zuhe2 & " " & Ziduan3 & " " & Trim(txtCao3.Text) & "'" & Trim(txtCha3.Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
myFlexGrid.Clear
myFlexGrid.Rows = 2
MsgBox "没有满足此条件的记录!", vbOKOnly + vbExclamation, "温馨提示"
Exit Sub
txtZi1.SetFocus
End If
Else
<span style="color:#ff0000;"> '如果首先点了第二个组合关系</span>
MsgBox "请首先填写第一个组合关系!", , "温馨提示"
Exit Sub
End If
With myFlexGrid
.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 mrc.EOF)
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = mrc!Serial
.TextMatrix(.Rows - 1, 1) = mrc!userid
.TextMatrix(.Rows - 1, 2) = mrc!level
.TextMatrix(.Rows - 1, 3) = mrc!LoginDate
.TextMatrix(.Rows - 1, 4) = mrc!LoginTime
.TextMatrix(.Rows - 1, 7) = mrc!computer
.TextMatrix(.Rows - 1, 8) = mrc!Status
.TextMatrix(.Rows - 1, 5) = mrc!LogoutDate
.TextMatrix(.Rows - 1, 6) = mrc!LogoutTime
mrc.MoveNext
Loop
End With
Exit Sub
mrc.Close
<span style="color:#ff0000;"> '调整<span style="font-family: Arial, Helvetica, sans-serif; font-size: 12px;">myFlexGrid控件单元格长度依字符串长度变化,防止单元格内容显示不全</span></span>
Call AdjustColWidth(frmGongZuoJiLu, myFlexGrid)
a:
MsgBox "查询内容包括正在值班的操作员!", , "温馨提示"
Exit Sub
End Sub
<span style="color:#ff0000;">'导出为Excel文件</span>
Private Sub cmdExcel_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim i As Integer
Dim j As Integer
<span style="color:#ff0000;"> '空值验证</span>
If myFlexGrid.Rows - 1 <= 0 Then
MsgBox "没有有效学生信息,请勿导出空数据!", vbOKOnly + vbExclamation, "温馨提示"
Exit Sub
End If
Set xlApp = CreateObject("Excel.Application") '实例化对象xlApp
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
For i = 0 To myFlexGrid.Rows - 1
For j = 0 To myFlexGrid.Cols - 1
myFlexGrid.Row = i
myFlexGrid.Col = j
xlSheet.Cells(i + 1, j + 1) = Trim(myFlexGrid.Text)
Next
Next
End Sub
Private Sub Command1_Click()
<span style="color:#ff0000;"> '隐藏三个日期控件</span>
DTP1.Visible = False
DTP2.Visible = False
DTP3.Visible = False
txtZi1.Clear
txtZi1.AddItem "教师"
txtZi1.AddItem "注册日期"
txtZi1.AddItem "注册时间"
txtZi1.AddItem "注销日期"
txtZi1.AddItem "注销时间"
txtZi1.AddItem "机器名"
txtZi2.Clear
txtZi2.AddItem "教师"
txtZi2.AddItem "注册日期"
txtZi2.AddItem "注册时间"
txtZi2.AddItem "注销日期"
txtZi2.AddItem "注销时间"
txtZi2.AddItem "机器名"
txtZi3.Clear
txtZi3.AddItem "教师"
txtZi3.AddItem "注册日期"
txtZi3.AddItem "注册时间"
txtZi3.AddItem "注销日期"
txtZi3.AddItem "注销时间"
txtZi3.AddItem "机器名"
txtCao1.Clear
txtCao1.AddItem "="
txtCao1.AddItem "<"
txtCao1.AddItem ">"
txtCao1.AddItem "<>"
txtCao2.Clear
txtCao2.AddItem "="
txtCao2.AddItem "<"
txtCao2.AddItem ">"
txtCao2.AddItem "<>"
txtCao3.Clear
txtCao3.AddItem "="
txtCao3.AddItem "<"
txtCao3.AddItem ">"
txtCao3.AddItem "<>"
txtZu1.Clear
txtZu1.AddItem "与"
txtZu1.AddItem "或"
txtZu2.Clear
txtZu2.AddItem "与"
txtZu2.AddItem "或"
txtCha1.Text = ""
txtCha2.Text = ""
txtCha3.Text = ""
myFlexGrid.Clear
myFlexGrid.Rows = 1
txtZi1.SetFocus
End Sub
Private Sub DTP1_Change()
<span style="color:#ff0000;">'查询内容文本框显示DTPicker控件的日期值</span>
txtCha1.Text = DTP1.Value
End Sub
Private Sub DTP1_LostFocus()
txtCha1.Text = DTP1.Value
DTP1.Visible = False
End Sub
Private Sub DTP2_Change()
<span style="color:#ff0000;">'查询内容文本框显示DTPicker控件的日期值</span>
txtCha2.Text = DTP1.Value
End Sub
Private Sub DTP2_LostFocus()
txtCha2.Text = DTP2.Value
DTP2.Visible = False
End Sub
Private Sub DTP3_Change()
<span style="color:#ff0000;"> '查询内容文本框显示DTPicker控件的日期值</span>
txtCha2.Text = DTP1.Value
End Sub
Private Sub DTP3_LostFocus()
txtCha3.Text = DTP3.Value
DTP3.Visible = False
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
a.ZOrder 1
End If
Next
End Sub
Private Sub Form_Load()
<span style="color:#ff0000;"> '隐藏三个日期控件</span>
DTP1.Visible = False
DTP2.Visible = False
DTP3.Visible = False
txtZi1.AddItem "教师"
txtZi1.AddItem "注册日期"
txtZi1.AddItem "注册时间"
txtZi1.AddItem "注销日期"
txtZi1.AddItem "注销时间"
txtZi1.AddItem "机器名"
txtZi2.AddItem "教师"
txtZi2.AddItem "注册日期"
txtZi2.AddItem "注册时间"
txtZi2.AddItem "注销日期"
txtZi2.AddItem "注销时间"
txtZi2.AddItem "机器名"
txtZi3.AddItem "教师"
txtZi3.AddItem "注册日期"
txtZi3.AddItem "注册时间"
txtZi3.AddItem "注销日期"
txtZi3.AddItem "注销时间"
txtZi3.AddItem "机器名"
txtCao1.AddItem "="
txtCao1.AddItem "<"
txtCao1.AddItem ">"
txtCao1.AddItem "<>"
txtCao2.AddItem "="
txtCao2.AddItem "<"
txtCao2.AddItem ">"
txtCao2.AddItem "<>"
txtCao3.AddItem "="
txtCao3.AddItem "<"
txtCao3.AddItem ">"
txtCao3.AddItem "<>"
txtZu1.AddItem "与"
txtZu1.AddItem "或"
txtZu2.AddItem "与"
txtZu2.AddItem "或"
myFlexGrid.Rows = 1
<span style="color:#ff0000;">'MDI子窗体居中显示</span>
Me.Top = (Screen.Height - Me.Height) / 3 - Me.Top
Me.Left = (Screen.Width - Me.Width) / 2 - Me.Left
End Sub
Private Sub txtCha1_GotFocus()
<span style="color:#ff0000;"> '如果需要显示上、下机日期</span>
If Trim(txtZi1.Text) = "注册日期" Or Trim(txtZi1.Text) = "注销日期" Then
'DTPicker控件可见
DTP1.Visible = True
txtCha1.Text = DTP1.Value
ElseIf Trim(txtZi1.Text) = "注册时间" Or Trim(txtZi1.Text) = "注销时间" Then
'DTPicker控件可见
DTP1.Format = dtpTime
DTP1.Value = Time
txtCha1.Text = DTP1.Value
Else
DTP1.Visible = False
End If
End Sub
Private Sub txtCha1_KeyPress(KeyAscii As Integer)
<span style="color:#ff0000;">'防止SQL注入</span>
Select Case KeyAscii
Case 48 To 57 '只能输入数字
Case 65 To 90 '只能输入大写字母
Case 97 To 122 '只能输入小写字母
Case 8 '只能输入退格
Case -20319 To -3652 '只能输入中文
Case Else
KeyAscii = 0
End Select
End Sub
Private Sub txtCha2_GotFocus()
<span style="color:#ff0000;"> '如果需要显示上、下机日期</span>
If Trim(txtZi2.Text) = "注册日期" Or Trim(txtZi2.Text) = "注销日期" Then
'DTPicker控件可见
DTP2.Visible = True
txtCha2.Text = DTP2.Value
ElseIf Trim(txtZi2.Text) = "注册时间" Or Trim(txtZi2.Text) = "注销时间" Then
'DTPicker控件可见
DTP2.Format = dtpTime
DTP2.Value = Time
txtCha2.Text = DTP2.Value
Else
DTP2.Visible = False
End If
End Sub
Private Sub txtCha2_KeyPress(KeyAscii As Integer)
<span style="color:#ff0000;">'防止SQL注入</span>
Select Case KeyAscii
Case 48 To 57 '只能输入数字
Case 65 To 90 '只能输入大写字母
Case 97 To 122 '只能输入小写字母
Case 8 '只能输入退格
Case -20319 To -3652 '只能输入中文
Case Else
KeyAscii = 0
End Select
End Sub
Private Sub txtCha3_GotFocus()
<span style="color:#ff0000;">'如果需要显示上、下机日期</span>
If Trim(txtZi3.Text) = "注册日期" Or Trim(txtZi3.Text) = "注销日期" Then
<span style="color:#ff0000;"> 'DTPicker控件可见</span>
DTP3.Visible = True
txtCha3.Text = DTP3.Value
ElseIf Trim(txtZi3.Text) = "注册时间" Or Trim(txtZi3.Text) = "注销时间" Then
<span style="color:#ff0000;">'DTPicker控件可见</span>
DTP3.Format = dtpTime
DTP3.Value = Time
txtCha3.Text = DTP3.Value
Else
DTP3.Visible = False
End If
End Sub
Private Sub txtCha3_KeyPress(KeyAscii As Integer)
<span style="color:#ff0000;">'防止SQL注入</span>
Select Case KeyAscii
Case 48 To 57 '只能输入数字
Case 65 To 90 '只能输入大写字母
Case 97 To 122 '只能输入小写字母
Case 8 '只能输入退格
Case -20319 To -3652 '只能输入中文
Case Else
KeyAscii = 0
End Select
End Sub
Private Sub txtZi1_Click()
<span style="color:#ff0000;"> '因为教师和机器名没有大小比较的功能,去掉操作符中的 < 和 ></span>
txtCha1.Text = ""
If Trim(txtZi1.Text) = "教师" Or Trim(txtZi1.Text) = "机器名" Then
txtCao1.Clear
txtCao1.AddItem "="
txtCao1.AddItem "<>"
Else
txtCao1.Clear
txtCao1.AddItem "="
txtCao1.AddItem "<"
txtCao1.AddItem ">"
txtCao1.AddItem "<>"
End If
End Sub
Private Sub txtZi2_Click()
txtCha2.Text = ""
If Trim(txtZi2.Text) = "教师" Or Trim(txtZi2.Text) = "机器名" Then
txtCao2.Clear
txtCao2.AddItem "="
txtCao2.AddItem "<>"
Else
txtCao2.Clear
txtCao2.AddItem "="
txtCao2.AddItem "<"
txtCao2.AddItem ">"
txtCao2.AddItem "<>"
End If
End Sub
Private Sub txtZi3_Click()
txtCha3.Text = ""
If Trim(txtZi3.Text) = "教师" Or Trim(txtZi3.Text) = "机器名" Then
txtCao3.Clear
txtCao3.AddItem "="
txtCao3.AddItem "<>"
Else
txtCao3.Clear
txtCao3.AddItem "="
txtCao3.AddItem "<"
txtCao3.AddItem ">"
txtCao3.AddItem "<>"
End If
End Sub