点击下载此附件
看了轻风的查询有感,写了个刷新(清除)条件的模块。
在窗体的查询条件中加入前缀,根据前缀判断是否为条件,然后进行查询和刷新。
一、查询模块
Public Function MyWhere(tForm As Form) As String
'函数作用:遍历控件,获取筛选条件。控件名称需按一定规则命名。
'参数: tForm:当前窗体
'返回: 筛选条件
'调用示例:Me.子窗体名称.Form.Filter = MyWhere(Me)
Dim ctl As Object
Dim ctlName As String
Dim strWhere As String
For Each ctl In tForm.Controls '遍历控件
ctlName = Mid(ctl.Name, 5) '提取去掉前缀后的控件名,亦为字段名。
Select Case Left(ctl.Name, 4) '判断控件名前缀,“w”控件是条件,后三位代表类型,WBX文本型,BRX布尔型,SZ加1或2为数值型,RQ加1或2为日期型。1为下限,2为上限。
'bet为前缀的可选框用来激活设置条件上限输入框,在窗体控件的更新后事件中:Private Sub bet首订日期_AfterUpdate()
'Me.wDa2首订日期.Enabled = Me.bet首订日期
'End Sub
Case "wWB1":
If Nz(ctl) <> "" Then strWhere = strWhere & "(" & ctlName & " like '*" & ctl & "*') And " '文本型支持通配符*、!等。
Case "wWB2":
If Nz(ctl) <> "" Then strWhere = strWhere & "(" & ctlName & " like '" & ctl & "') And " '文本型不支持通配符。
' Case "wBRX":
' If Nz(Ctl) <> "" Then strWhere = strWhere & "(" & ctlName & " = " & Ctl & ") And "
Case "wSZ1":
If Nz(ctl) <> "" Then strWhere = strWhere & "(" & ctlName & " >= " & ctl & ") And " '如果激活设置条件下限,则运算符为>=下限
Case "wSZ2":
If Nz(ctl) <> "" Then strWhere = strWhere & "(" & ctlName & " <= " & ctl & ") And " '如果激活设置条件上限,则运算符为<=上限
Case "wRQ1":
If Nz(ctl) <> "" Then strWhere = strWhere & "(" & ctlName & " >= #" & ctl & "#) And"
Case "wRQ2":
If Nz(ctl) <> "" Then strWhere = strWhere & "(" & ctlName & " <= #" & ctl & "#) And "
End Select
Next
If Len(strWhere) > 0 Then '如果条件不为空
strWhere = Left(strWhere, Len(strWhere) - 5) '去掉后面多余字符 或用 strWhere = Left(strWhere, Len(strWhere) - Len(tLogic) - 2)
Else '否则
strWhere = True '设置条件为真,即所有记录。
End If
MyWhere = strWhere
End Function
二、刷新模块
Public Function QCTJ(tForm As Form) As String
'调用:在控件的单击事件中引用 QCTJ Me
Dim ctl As Object
For Each ctl In tForm.Controls '遍历控件
Select Case Left(ctl.Name, 1) '取控件名第1位
Case "w": '"w"代表控件是条件,
If ctl.Locked = False Then ctl.Value = Null '清空条件
End Select
Next
End Function