vba 从vba中的表格填充列表框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1123616/
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
Populate list box from a table in vba
提问by SmartestVEGA
I am developing a vba form for employee database, in that there is a search criteria for userid and employees name with the userid should be displayed in a list box control which comes from a single table
我正在为员工数据库开发一个 vba 表单，因为用户 ID 和员工姓名的搜索条件应该显示在来自单个表的列表框控件中
I need to populate a list box with value from a table according to a value in a text box which act as a search box (eg: userid)
我需要根据充当搜索框的文本框中的值（例如：userid）用表中的值填充列表框
Please help me how to do this
请帮助我如何做到这一点
回答by Seth Spearman
You question is hard to answer because it depends on some things like data types of the search field etc. So this answer is going to be vague on some of those points...
你的问题很难回答，因为它取决于一些事情，比如搜索字段的数据类型等。所以这个答案在其中一些点上会很模糊......
First off you need to create your listbox with search criteria that will look on the form for the search value and filter accordingly.
首先，您需要使用搜索条件创建列表框，这些条件将在表单上查找搜索值并相应地进行过滤。
You do this by setting the the RowSource property of the listbox. HEre is an example rowsource for a a listbox that looks for a textbox on a form for its filter value...
您可以通过设置列表框的 RowSource 属性来完成此操作。这是一个列表框的示例行源，该列表框在表单上查找文本框以获取其过滤器值...
SELECT tblAgencies.AgencyID, tblAgencies.OrganizationName
FROM tblAgencies
WHERE (((tblAgencies.OrganizationName)
Like "*" & nz([Forms]![frmMainMenu2]![txtSearchAgencies],"") & "*"))
ORDER BY tblAgencies.OrganizationName;
The key part is the Like... line. A couple of things about it...notice that the query looks to the form for some criteria. You see that in the [Forms]![frmMainMenu2]![txtSearchAgencies] part of the query. So there is a search textbox on frmMainMenu2 that is called txtSearchAgencies.
关键部分是 Like... 行。关于它的一些事情...请注意，查询会根据某些条件查看表单。您可以在查询的 [Forms]![frmMainMenu2]![txtSearchAgencies] 部分看到这一点。所以在 frmMainMenu2 上有一个名为 txtSearchAgencies 的搜索文本框。
Notice also that I am using NZ function to ensure that the peek onto that textbox returns at least an empty string. Finally notice that is uses the Like operator with wild cards at both ends so that the user can type a partial string.
另请注意，我正在使用 NZ 函数来确保对该文本框的查看至少返回一个空字符串。最后请注意，它使用了两端带有通配符的 Like 运算符，以便用户可以键入部分字符串。
Finally...next to your search box put a command button to execute the filter/search. All that it has to do is REQUERY the listbox like this...
最后...在您的搜索框旁边放一个命令按钮来执行过滤器/搜索。它所要做的就是像这样请求列表框......
Me.lstAgencies.Requery.
Me.lstAgencies.Requery。
You could also try to Requery at the OnChange event which would filter as they type. But if your query is slow this may not work well.
您还可以尝试在 OnChange 事件中重新查询，这将在他们键入时进行过滤。但是如果您的查询速度很慢，这可能无法正常工作。
Seth
赛斯
回答by mik
Let's say you have a table TABLE1 that has fields userid, employee. You should create a form that has a combobox(named boxid) and textbox(named EdtEmployee). Define a rowsource value of combobox like
假设您有一个表 TABLE1，其中包含字段 userid、employee。您应该创建一个具有组合框（名为 boxid）和文本框（名为 EdtEmployee）的表单。定义组合框的行源值，如
SELECT table1.userid FROM table1 WHERE employee like EdtEmployee & "*";
Then define a lostfocus event of a textbox like this
然后像这样定义一个文本框的 lostfocus 事件
Private Sub EdtEmployee_LostFocus()
BoxId.Requery
End Sub
I hope, this works for you
我希望，这对你有用
回答by Patrick Honorez
I agree with mik. I just would use an AfterUpdate event instead of the LostFocus which I've neber used.
我同意米克。我只会使用 AfterUpdate 事件而不是我使用过的 LostFocus。
