在敲《机房收费系统》的时候,我们遇到了一个组合查询,经过不懈努力,终于做出来了,现把思路和代码总结如下:
首先是组合框列表项的载入,在Form_Load中添加如下代码:
For intIndex = 0 To 2 cboFields(intIndex).AddItem "教师" cboFields(intIndex).AddItem "注册日期" cboFields(intIndex).AddItem "注册时间" cboFields(intIndex).AddItem "注销日期" cboFields(intIndex).AddItem "注销时间" cboFields(intIndex).AddItem "机器名" cboFields(intIndex).ListIndex = 0 cboOperator(intIndex).AddItem "=" cboOperator(intIndex).AddItem "<" cboOperator(intIndex).AddItem ">" cboOperator(intIndex).AddItem "<>" cboOperator(intIndex).ListIndex = 0 Next intIndex For intIndex = 0 To 1 cboRelation(intIndex).AddItem "" cboRelation(intIndex).AddItem "或" cboRelation(intIndex).AddItem "与" cboRelation(intIndex).ListIndex = 0 Next intIndex这里运用了控件数组,载入列表项时再用for循环,大大减少代码量。因为这些列表在查询的时候必然会选择一项内容,所以通过ListIndex属性让组合框默认显示一项,这样既可以节省部分用户时间,也避免了对未选择列表项的处理。
Private Sub cmdInquiry_Click() Dim txtsql As String Dim MsgText As String Dim mrc As ADODB.Recordset '字段和关系 Dim strFields(0 To 2) As String Dim strRelation(0 To 1) As String '当选择不同的字段时,赋给变量相应的数据库字段名 For intIndex = 0 To 2 Select Case cboFields(intIndex).Text Case "教师" strFields(intIndex) = "UserID" Case "注册日期" strFields(intIndex) = "LoginDate" Case "注册时间" strFields(intIndex) = "LoginTime" Case "注销日期" strFields(intIndex) = "logoutDate" Case "注销时间" strFields(intIndex) = "logoutTime" Case "机器名" strFields(intIndex) = "computer" End Select Next intIndex '关系 For intIndex = 0 To 1 Select Case cboRelation(intIndex).Text Case "或" strRelation(intIndex) = "or" Case "与" strRelation(intIndex) = "and" End Select Next intIndex '防止查询条件为空 If txtContent1.Text = "" Then MsgBox "请输入要查询的内容!",vbOKOnly + vbInformation,"提示" txtContent1.SetFocus Exit Sub End If '如果只有一个查询条件 If cboRelation(0).Text = "" And cboRelation(1).Text = "" Then txtsql = "select * from worklog_Info where " & strFields(0) & cboOperator(0).Text & "'" & Trim(txtContent1.Text) & "'" Set mrc = Executesql(txtsql,MsgText) End If '如果为一二组合查询 If cboRelation(0).Text <> "" And cboRelation(1).Text = "" Then '防止查询条件为空 If txtContent2.Text = "" Then MsgBox "请输入要查询的内容!","提示" txtContent2.SetFocus Exit Sub End If txtsql = "select * from worklog_Info where " & strFields(0) & cboOperator(0).Text & "'" & Trim(txtContent1.Text) & "'" _ & " " & strRelation(0) & " " & strFields(1) & cboOperator(1).Text & "'" & Trim(txtContent2.Text) & "'" Set mrc = Executesql(txtsql,MsgText) End If '如果为一二三组合查询 If cboRelation(0).Text <> "" And cboRelation(1).Text <> "" Then '防止查询条件为空 If txtContent2.Text = "" Then MsgBox "请输入要查询的内容!","提示" txtContent2.SetFocus Exit Sub End If If txtContent3.Text = "" Then MsgBox "请输入要查询的内容!","提示" txtContent3.SetFocus Exit Sub End If txtsql = "select * from worklog_Info where " & strFields(0) & cboOperator(0).Text & "'" & Trim(txtContent1.Text) & "'" _ & " " & strRelation(0) & " " & strFields(1) & cboOperator(1).Text & "'" & Trim(txtContent2.Text) & "'" _ & " " & strRelation(1) & " " & strFields(2) & cboOperator(2).Text & "'" & Trim(txtContent3.Text) & "'" Set mrc = Executesql(txtsql,MsgText) End If '如果用户选择了组合关系2没选组合关系1,提示用户 If cboRelation(0).Text = "" And cboRelation(1).Text <> "" Then MsgBox "请选择组合关系!","提示" cboRelation(0).SetFocus Exit Sub End If With MSHFlexGrid1 .Rows = 1 .Cols = 8 .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) = "机器名" 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 If IsNull(mrc.Fields("logoutDate")) Then .TextMatrix(.Rows - 1,5) = "" Else .TextMatrix(.Rows - 1,5) = mrc!logoutDate End If If IsNull(mrc.Fields("logoutTime")) Then .TextMatrix(.Rows - 1,6) = "" Else .TextMatrix(.Rows - 1,6) = mrc!logoutTime End If .TextMatrix(.Rows - 1,7) = mrc!computer mrc.MoveNext Loop End With mrc.Close End Sub这里只是实现了查询的功能,但是假如用户不按顺序,直接在最后一行输入查询内容,不选组合方式,程序不会得到查询结果,也不会进行任何提示,这里仅仅展示查询的逻辑过程,细节方面还需要读者自行完善。
初学者,不当之处敬请批评指正!
欢迎光临我的网易博客:http://blog.163.com/liu_xiaochun/