胡根得廊坊师范学院信息技术提高班十二期

先附上我的组合查询设计图一张：

控件讲解：在查询内容处放置两个重叠控件，DTpicker和text文本框；字段名列、操作符列、组合关系列combo控件全部禁止输入内容（为用户考虑，只要能选择就不要输入，而且能有效避免处理SQL注入、输入无效内容等处理工作）。

程序事件：

form_load:加载combo控件内容，隐藏dtpicker控件，mdi子窗体居中，控制myFlexGrid控件行数为1。不想写了；下面是整篇代码，加注释：

</pre><pre name="code" class="html">'****************************************************************************************************************************'2015 年 8 月 2 日'今日收获：想要完美的使用一种控件，就要首先了解其各种属性、方法、事件。达到有的放矢，知彼而百战不殆。'对combo.clear方法小感''on error resume next : 此语句后的代码，如果产生错误也不提示，继续执行后面的语句。' 请和 on error goto 语句作比较''DTPicker控件显示时间是从系统获取的，所以改变系统时间格式，控件时间格式也会跟着改变''keyascii码值是可以限制是否输入汉字的''***************************************************************************************************************************Option ExplicitDim X As Integer, Y As Integer, z As IntegerPrivate Sub cmdCha_Click()Dim mrc As ADODB.RecordsetDim txtSQL As String, MsgText As String, Ziduan1 As String, Ziduan2 As String, Ziduan3 As String, Zuhe1 As String, Zuhe2 As String, TXT As StringmyFlexGrid.ClearmyFlexGrid.Rows = 1On Error GoTo a: '空值验证If Trim(txtZi1.Text) = "" Or Trim(txtCao1.Text) = "" Or Trim(txtCha1.Text) = "" Then '清空输入DTP1.Visible = FalseDTP2.Visible = FalseDTP3.Visible = FalsetxtZi1.CleartxtZi1.AddItem "教师"txtZi1.AddItem "注册日期"txtZi1.AddItem "注册时间"txtZi1.AddItem "注销日期"txtZi1.AddItem "注销时间"txtZi1.AddItem "机器名"txtZi2.CleartxtZi2.AddItem "教师"txtZi2.AddItem "注册日期"txtZi2.AddItem "注册时间"txtZi2.AddItem "注销日期"txtZi2.AddItem "注销时间"txtZi2.AddItem "机器名"txtZi3.CleartxtZi3.AddItem "教师"txtZi3.AddItem "注册日期"txtZi3.AddItem "注册时间"txtZi3.AddItem "注销日期"txtZi3.AddItem "注销时间"txtZi3.AddItem "机器名"txtCao1.CleartxtCao1.AddItem "="txtCao1.AddItem "<"txtCao1.AddItem ">"txtCao1.AddItem "<>"txtCao2.CleartxtCao2.AddItem "="txtCao2.AddItem "<"txtCao2.AddItem ">"txtCao2.AddItem "<>"txtCao3.CleartxtCao3.AddItem "="txtCao3.AddItem "<"txtCao3.AddItem ">"txtCao3.AddItem "<>"txtZu1.CleartxtZu1.AddItem "与"txtZu1.AddItem "或"txtZu2.CleartxtZu2.AddItem "与"txtZu2.AddItem "或"txtCha1.Text = ""txtCha2.Text = ""txtCha3.Text = ""myFlexGrid.ClearmyFlexGrid.Rows = 2MsgBox "第一行查询条件不能为空！", vbInformation, "温馨提示"txtZi1.SetFocusExit SubEnd If '长度验证If Len(txtCha1.Text) > 10 ThenMsgBox "查询内容字符长度请小于10位！", vbOKOnly + vbExclamation, "温馨提示" '长度验证txtCha1.Text = ""txtCha1.SetFocusExit SubEnd IfIf Len(txtCha2.Text) > 10 ThenMsgBox "查询内容字符长度请小于10位！", vbOKOnly + vbExclamation, "温馨提示" '长度验证txtCha2.Text = ""txtCha2.SetFocusExit SubEnd IfIf Len(txtCha3.Text) > 10 ThenMsgBox "查询内容字符长度请小于10位！", vbOKOnly + vbExclamation, "温馨提示" '长度验证txtCha3.Text = ""txtCha3.SetFocusExit SubEnd If'字段选择Select Case txtZi1.TextCase "教师"Ziduan1 = "UserID"Case "注册日期"Ziduan1 = "LoginDate"Case "注册时间"Ziduan1 = "LoginTime"Case "注销日期"Ziduan1 = "LogoutDate"Case "注销时间"Ziduan1 = "LogoutTime"Case "机器名"Ziduan1 = "computer"End SelectSelect Case txtZi2.TextCase "教师"Ziduan2 = "UserID"Case "注册日期"Ziduan2 = "LoginDate"Case "注册时间"Ziduan2 = "LoginTime"Case "注销日期"Ziduan2 = "LogoutDate"Case "注销时间"Ziduan2 = "LogoutTime"Case "机器名"Ziduan2 = "computer"End SelectSelect Case txtZi3.TextCase "教师"Ziduan3 = "UserID"Case "注册日期"Ziduan3 = "LoginDate"Case "注册时间"Ziduan3 = "LoginTime"Case "注销日期"Ziduan3 = "LogoutDate"Case "注销时间"Ziduan3 = "LogoutTime"Case "机器名"Ziduan3 = "computer"End Select'选择组合关系Select Case txtZu1.TextCase "与"Zuhe1 = "and"Case "或"Zuhe1 = "or"Case ElsetxtZu1.CleartxtZu1.AddItem "与"txtZu1.AddItem "或"End SelectSelect Case txtZu2.TextCase "与"Zuhe2 = "and"Case "或"Zuhe2 = "or"Case ElsetxtZu2.CleartxtZu2.AddItem "与"txtZu2.AddItem "或"End Select'仅有一个查询条件,即不使用组合查询时If txtZu1.Text = "" And txtZu2.Text = "" Then'清空冗余的输入txtZi2.CleartxtZi2.AddItem "教师"txtZi2.AddItem "注册日期"txtZi2.AddItem "注册时间"txtZi2.AddItem "注销日期"txtZi2.AddItem "注销时间"txtZi2.AddItem "机器名"txtZi3.CleartxtZi3.AddItem "教师"txtZi3.AddItem "注册日期"txtZi3.AddItem "注册时间"txtZi3.AddItem "注销日期"txtZi3.AddItem "注销时间"txtZi3.AddItem "机器名"txtCao2.CleartxtCao2.AddItem "="txtCao2.AddItem "<"txtCao2.AddItem ">"txtCao2.AddItem "<>"txtCao3.CleartxtCao3.AddItem "="txtCao3.AddItem "<"txtCao3.AddItem ">"txtCao3.AddItem "<>"'在Line表中检索信息txtSQL = "select * from worklog_Info where " & Trim(Ziduan1) & " " & Trim(txtCao1.Text) & " '" & Trim(txtCha1.Text) & "'"Set mrc = ExecuteSQL(txtSQL, MsgText)If mrc.EOF ThenmyFlexGrid.ClearmyFlexGrid.Rows = 2MsgBox "没有满足此条件的记录!", vbOKOnly + vbExclamation, "温馨提示"txtZi1.SetFocusExit SubEnd If '查询符合两个条件的目录ElseIf txtZu1.Text <> "" And txtZu2.Text = "" Then '清除冗余输入txtZi3.CleartxtZi3.AddItem "教师"txtZi3.AddItem "注册日期"txtZi3.AddItem "注册时间"txtZi3.AddItem "注销日期"txtZi3.AddItem "注销时间"txtZi3.AddItem "机器名"txtCao3.CleartxtCao3.AddItem "="txtCao3.AddItem "<"txtCao3.AddItem ">"txtCao3.AddItem "<>"'双组合查询第二行内容不能为空If Trim(txtZi2.Text) = "" Or Trim(txtCao2.Text) = "" Or Trim(txtCha2.Text) = "" ThenMsgBox "您选择了双组合查询，请完善第二行查询条件", vbInformation, "温馨提示"txtZi2.SetFocusExit SubEnd If '检索lion表中符合条件的信息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 ThenmyFlexGrid.ClearmyFlexGrid.Rows = 2MsgBox "没有满足此条件的记录!", vbOKOnly + vbExclamation, "温馨提示"Exit SubEnd If'查询并显示符合三个条件的记录ElseIf txtZu1.Text <> "" And txtZu2.Text <> "" Then'第二行查询信息不能为空If Trim(txtZi2.Text) = "" Or Trim(txtCao2.Text) = "" Or Trim(txtCha2.Text) = "" ThenMsgBox "您选择了三组合查询，请完善第二行查询条件", vbInformation, "温馨提示"Exit SubElse'第三行查询信息不能为空If Trim(txtZi3.Text) = "" Or Trim(txtCao3.Text) = "" Or Trim(txtCha3.Text) = "" ThenMsgBox "您选择了三组合查询，，请完善第三行查询条件", vbInformation, "温馨提示"Exit SubEnd IfEnd If 'lion表中查询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 ThenmyFlexGrid.ClearmyFlexGrid.Rows = 2MsgBox "没有满足此条件的记录!", vbOKOnly + vbExclamation, "温馨提示"Exit SubtxtZi1.SetFocusEnd IfElse '如果首先点了第二个组合关系MsgBox "请首先填写第一个组合关系！", , "温馨提示"Exit SubEnd IfWith 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!LogoutTimemrc.MoveNextLoopEnd WithExit Submrc.Close '调整myFlexGrid控件单元格长度依字符串长度变化，防止单元格内容显示不全Call AdjustColWidth(frmGongZuoJiLu, myFlexGrid)a:MsgBox "查询内容包括正在值班的操作员！", , "温馨提示"Exit SubEnd Sub'导出为Excel文件Private Sub cmdExcel_Click()Dim xlApp As Excel.ApplicationDim xlBook As Excel.WorkbookDim xlSheet As Excel.WorksheetDim i As IntegerDim j As Integer '空值验证If myFlexGrid.Rows – 1 <= 0 ThenMsgBox "没有有效学生信息，请勿导出空数据！", vbOKOnly + vbExclamation, "温馨提示"Exit SubEnd IfSet xlApp = CreateObject("Excel.Application") '实例化对象xlAppxlApp.Visible = TrueSet xlBook = xlApp.Workbooks.AddSet xlSheet = xlBook.Worksheets(1)For i = 0 To myFlexGrid.Rows – 1For j = 0 To myFlexGrid.Cols – 1myFlexGrid.Row = imyFlexGrid.Col = jxlSheet.Cells(i + 1, j + 1) = Trim(myFlexGrid.Text)NextNextEnd SubPrivate Sub Command1_Click() '隐藏三个日期控件DTP1.Visible = FalseDTP2.Visible = FalseDTP3.Visible = FalsetxtZi1.CleartxtZi1.AddItem "教师"txtZi1.AddItem "注册日期"txtZi1.AddItem "注册时间"txtZi1.AddItem "注销日期"txtZi1.AddItem "注销时间"txtZi1.AddItem "机器名"txtZi2.CleartxtZi2.AddItem "教师"txtZi2.AddItem "注册日期"txtZi2.AddItem "注册时间"txtZi2.AddItem "注销日期"txtZi2.AddItem "注销时间"txtZi2.AddItem "机器名"txtZi3.CleartxtZi3.AddItem "教师"txtZi3.AddItem "注册日期"txtZi3.AddItem "注册时间"txtZi3.AddItem "注销日期"txtZi3.AddItem "注销时间"txtZi3.AddItem "机器名"txtCao1.CleartxtCao1.AddItem "="txtCao1.AddItem "<"txtCao1.AddItem ">"txtCao1.AddItem "<>"txtCao2.CleartxtCao2.AddItem "="txtCao2.AddItem "<"txtCao2.AddItem ">"txtCao2.AddItem "<>"txtCao3.CleartxtCao3.AddItem "="txtCao3.AddItem "<"txtCao3.AddItem ">"txtCao3.AddItem "<>"txtZu1.CleartxtZu1.AddItem "与"txtZu1.AddItem "或"txtZu2.CleartxtZu2.AddItem "与"txtZu2.AddItem "或"txtCha1.Text = ""txtCha2.Text = ""txtCha3.Text = ""myFlexGrid.ClearmyFlexGrid.Rows = 1txtZi1.SetFocusEnd SubPrivate Sub DTP1_Change()'查询内容文本框显示DTPicker控件的日期值txtCha1.Text = DTP1.ValueEnd SubPrivate Sub DTP1_LostFocus()txtCha1.Text = DTP1.ValueDTP1.Visible = FalseEnd SubPrivate Sub DTP2_Change()'查询内容文本框显示DTPicker控件的日期值txtCha2.Text = DTP1.ValueEnd SubPrivate Sub DTP2_LostFocus()txtCha2.Text = DTP2.ValueDTP2.Visible = FalseEnd SubPrivate Sub DTP3_Change() '查询内容文本框显示DTPicker控件的日期值txtCha2.Text = DTP1.ValueEnd SubPrivate Sub DTP3_LostFocus()txtCha3.Text = DTP3.ValueDTP3.Visible = FalseEnd SubPrivate Sub Form_Activate()Dim a As FormFor Each a In Forms If a.Name <> frmMain.Name And a.Name <> Me.Name Thena.WindowState = 1a.ZOrder 1End IfNextEnd SubPrivate Sub Form_Load() '隐藏三个日期控件DTP1.Visible = FalseDTP2.Visible = FalseDTP3.Visible = FalsetxtZi1.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'MDI子窗体居中显示Me.Top = (Screen.Height – Me.Height) / 3 – Me.TopMe.Left = (Screen.Width – Me.Width) / 2 – Me.LeftEnd SubPrivate Sub txtCha1_GotFocus() '如果需要显示上、下机日期If Trim(txtZi1.Text) = "注册日期" Or Trim(txtZi1.Text) = "注销日期" Then'DTPicker控件可见DTP1.Visible = TruetxtCha1.Text = DTP1.ValueElseIf Trim(txtZi1.Text) = "注册时间" Or Trim(txtZi1.Text) = "注销时间" Then'DTPicker控件可见DTP1.Format = dtpTimeDTP1.Value = TimetxtCha1.Text = DTP1.ValueElseDTP1.Visible = FalseEnd IfEnd SubPrivate Sub txtCha1_KeyPress(KeyAscii As Integer)'防止SQL注入Select Case KeyAsciiCase 48 To 57 '只能输入数字Case 65 To 90 '只能输入大写字母Case 97 To 122 '只能输入小写字母Case 8 '只能输入退格Case -20319 To -3652 '只能输入中文Case ElseKeyAscii = 0End SelectEnd SubPrivate Sub txtCha2_GotFocus() '如果需要显示上、下机日期If Trim(txtZi2.Text) = "注册日期" Or Trim(txtZi2.Text) = "注销日期" Then'DTPicker控件可见DTP2.Visible = TruetxtCha2.Text = DTP2.ValueElseIf Trim(txtZi2.Text) = "注册时间" Or Trim(txtZi2.Text) = "注销时间" Then'DTPicker控件可见DTP2.Format = dtpTimeDTP2.Value = TimetxtCha2.Text = DTP2.ValueElseDTP2.Visible = FalseEnd IfEnd SubPrivate Sub txtCha2_KeyPress(KeyAscii As Integer)'防止SQL注入 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 SelectEnd SubPrivate Sub txtCha3_GotFocus()'如果需要显示上、下机日期If Trim(txtZi3.Text) = "注册日期" Or Trim(txtZi3.Text) = "注销日期" Then 'DTPicker控件可见DTP3.Visible = TruetxtCha3.Text = DTP3.ValueElseIf Trim(txtZi3.Text) = "注册时间" Or Trim(txtZi3.Text) = "注销时间" Then'DTPicker控件可见DTP3.Format = dtpTimeDTP3.Value = TimetxtCha3.Text = DTP3.ValueElseDTP3.Visible = FalseEnd IfEnd SubPrivate Sub txtCha3_KeyPress(KeyAscii As Integer)'防止SQL注入 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 SelectEnd SubPrivate Sub txtZi1_Click() '因为教师和机器名没有大小比较的功能，去掉操作符中的 < 和 >txtCha1.Text = ""If Trim(txtZi1.Text) = "教师" Or Trim(txtZi1.Text) = "机器名" ThentxtCao1.CleartxtCao1.AddItem "="txtCao1.AddItem "<>"ElsetxtCao1.CleartxtCao1.AddItem "="txtCao1.AddItem "<"txtCao1.AddItem ">"txtCao1.AddItem "<>"End IfEnd SubPrivate Sub txtZi2_Click()txtCha2.Text = ""If Trim(txtZi2.Text) = "教师" Or Trim(txtZi2.Text) = "机器名" ThentxtCao2.CleartxtCao2.AddItem "="txtCao2.AddItem "<>"ElsetxtCao2.CleartxtCao2.AddItem "="txtCao2.AddItem "<"txtCao2.AddItem ">"txtCao2.AddItem "<>"End IfEnd SubPrivate Sub txtZi3_Click()txtCha3.Text = ""If Trim(txtZi3.Text) = "教师" Or Trim(txtZi3.Text) = "机器名" ThentxtCao3.CleartxtCao3.AddItem "="txtCao3.AddItem "<>"ElsetxtCao3.CleartxtCao3.AddItem "="txtCao3.AddItem "<"txtCao3.AddItem ">"txtCao3.AddItem "<>"End IfEnd Sub

做对的事情比把事情做对重要。

相关文章：

你感兴趣的文章：

标签云：