前言:举例操作员查询案例,组合查询中因为涉及到很多的控件,可以设置控件组,并且需要考虑到组合过程中哪些控件不可以动以及不可以输入哪些字符。
设置代码
- 将组合语句屏蔽掉:
If cboConnect1.Text <> Trim("") Then
cboField2.Enabled = True
cboOpe2.Enabled = True
cboConnect2.Enabled = True
txtInquiry2.Enabled = True
DTPicker2.Enabled = True
end if
- from中显示cbo的内容,并且添加不能添加的条件:
'如果组合查询框为空(不是"与"也不会"或"),不能添加下面的查询条件
If cboConnect1.Text = "请选择" Then
cboField2.Enabled = False
cboField3.Enabled = False
cboOpe2.Enabled = False
cboOpe3.Enabled = False
txtInquiry2.Enabled = False
txtInquiry3.Enabled = False
DTPicker2.Enabled = False
DTPicker3.Enabled = False
cboConnect2.Enabled = False
End If
If cboConnect2.Text = "请选择" Then
cboField3.Enabled = False
cboOpe3.Enabled = False
txtInquiry3.Enabled = False
DTPicker3.Enabled = False
End If
- 删除所有的文本框,以TXT文件为例:
Dim ctl As Control '定义一个变量,把任何控件装载到里面
For Each ctl In Controls
If TypeOf ctl Is TextBox Then ctl.Text = ""
Next ctl
- 每个cbo中都要添加各自相应显示的内容:
cboOpe1.Clear '避免cboOpe1重复添加操作符
Select Case cboField1.Text
Case "教师"
cboOpe1.AddItem "="
cboOpe1.AddItem "<>"
txtInquiry1.Locked = False '选择教师的时候,可以用txtInquiry1,DTPicker1不可见
DTPicker1.Visible = False
查询
- 实现第一行为例:
Select Case cboField1.Text
Case "教师"
strcboField1 = "UserID"
Case "注册日期"
strcboField1 = "LoginDate"
Case "注册时间"
strcboField1 = "LoginTime"
Case "注销日期"
strcboField1 = "LogoutDate"
Case "注销时间"
strcboField1 = "LogoutTime"
Case "机器名"
strcboField1 = "computer"
End Select
If Trim(cboField1.Text) = "" Or Trim(cboOpe1.Text) = "" Or (Trim(txtInquiry1.Text) = "" And Trim(DTPicker1.Value = "")) Then
MsgBox "请将选项内容填写完整!", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
'在worklog_info这张表中获得整行记录
If DTPicker1.Visible = True Then
txtSQL = "select * from worklog_Info where " & strcboField1 & cboOpe1.Text & "'" & DTPicker1.Value & "'"
Else
txtSQL = "select * from worklog_Info where " & strcboField1 & cboOpe1.Text & "'" & txtInquiry1.Text & "'"
End If
Set mrc = ExecuteSQL(txtSQL, MsgText)
End If
总结
组合查询应该是最难之一了,因为涉及到的控件比较多,所以很多的逻辑需要强调,以及每个什么时候该出现和不该出现,还要运用到其中同一个地方要用到两个控件,这个比较繁琐,所以看到好多人的博客已写所以我就不多写了,总结完毕,轻松收场!