vba 使用文本框过滤连续表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19962805/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
filter continuous form using textbox
提问by CodeMed
I need to let users filter a continuous form using values the user enters into a textbox. And the continuous form is also nested within a couple levels of navigation subforms. This sounds easy enough, but all the examples I find on the web use macros instead of vba.
我需要让用户使用用户在文本框中输入的值过滤连续表单。连续表单也嵌套在几个级别的导航子表单中。这听起来很简单，但是我在网上找到的所有示例都使用宏而不是 vba。
I set up the structure and wrote an AfterUpdate procedure for a textbox txtFilter as follows:
我设置了结构并为文本框 txtFilter 编写了一个 AfterUpdate 过程，如下所示：
Private Sub txtFilter_AfterUpdate()
Dim filterval As String
filterval = txtFilter.Value
With Forms!Main!NavigationSubform.Form!NavigationSubform.Form
.Filter = "LastName Like " & filterval
.FilterOn = True
End With
End Sub
I have played with different syntax, but none of it seems to work properly. Here is a link to download the relevant parts of the database from a file sharing site: http://jmp.sh/v/HGctZ4Ru74vDAjzN43Wq
我玩过不同的语法，但似乎没有一个能正常工作。这是从文件共享站点下载数据库相关部分的链接：http: //jmp.sh/v/HGctZ4Ru74vDAjzN43Wq
Can anyone show me how to alter this so that users can use the textbox to filter the continuous form?
谁能告诉我如何更改它以便用户可以使用文本框来过滤连续表单？
回答by craig.white
I got it to work using this: .Filter = "LastName Like """ & filterval & """"
我用这个让它工作： .Filter = "LastName Like """ & filterval & """"
Need those annoying String Identifiers even for strings sometimes.
有时甚至对于字符串也需要那些烦人的字符串标识符。
Okay, To get the form to open with no records and then pull up just the records you (or the user) specifies is easiest with a bit of re-work.
(I'd recommend you working with a copy and not your original)
1:On your Continuous Form, remove the Recordsource; we're going to use Late Binding (Kinda)
2:Then delete the code under the txtFilter box, then delete the box itself.
3:Add a comboBox with something like this as the recordsource:
SELECT DISTINCT myTable.LastName FROM myTable ORDER BY myTable.LastName;(This will get you a unique list of last names so knowing how to spell the name will not be necessary, plus it assures at least one match)
4:In the After Update event of that combobox, add code like this:
好的，要在没有记录的情况下打开表单，然后仅提取您（或用户）指定的记录，最简单的方法是进行一些返工。（我建议您使用副本而不是原件） 1：在您的连续表单上，删除记录源；我们将使用Late Binding (Kinda) 2：然后删除txtFilter 框下的代码，然后删除框本身。3：添加一个类似这样的组合框作为记录源：（
SELECT DISTINCT myTable.LastName FROM myTable ORDER BY myTable.LastName;这将为您提供一个唯一的姓氏列表，因此不需要知道如何拼写名字，而且它可以确保至少匹配） 4：在更新后该组合框的事件，添加如下代码：
Dim strSource As String
strSource = "SELECT mt.IntakeNumber, mt.ClientNumber, " & _
"mt.LastName, mt.FirstName, mt.ConsultationDate " & _
" FROM myTable mt " & _
"WHERE (mt.LastName)= '" & Me.cboFilter.Value & "'"
Me.RecordSource = strSource
Me.Requery
Obviously you'll need to change the table and field names as necessary, but hopefully you get the idea.
显然，您需要根据需要更改表名和字段名，但希望您能理解。
回答by HansUp
Option Compare Database
Option Explicit '<- always include this!!!!!
Private Sub txtFilter_AfterUpdate()
Dim strFilter As String
' only set Filter when text box contains something
' to search for ==> don't filter Null, empty string,
' or spaces
If Len(Trim(Me.txtFilter.Value & vbNullString)) > 0 Then
strFilter = "LastName Like '*" & _
Replace(Me.txtFilter.Value, "'", "''") & _
"*'"
' that Replace() prevents the procedure from breaking
' when the user enters a name with an apostrophe
' into the text box (O'Malley)
Debug.Print strFilter ' see what we built, Ctrl+g
Me.Filter = strFilter
Me.FilterOn = True
Else
' what should happen here?
' maybe just switch off the filter ...
Me.FilterOn = False
End If
End Sub
