从VBA中的表填充列表框 [英] Populate list box from a table in vba

问题描述

我正在为员工数据库开发一个vba表单，因为有一个针对用户ID的搜索条件，带有用户ID的员工姓名应显示在来自单个表的列表框控件中

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

我需要根据充当搜索框的文本框中的值(例如:userid)，使用表中的值填充列表框

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)

请帮助我该怎么做

推荐答案

您的问题很难回答，因为它取决于某些内容，例如搜索字段的数据类型等.因此，对于这些问题，答案会很模糊点...

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.

您可以通过设置列表框的RowSource属性来执行此操作.这里是一个列表框的示例行源，该列表框在表单上寻找其过滤值的文本框...

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...

SELECT tblAgencies.AgencyID, tblAgencies.OrganizationName
FROM tblAgencies
WHERE (((tblAgencies.OrganizationName) 
          Like "*" & nz([Forms]![frmMainMenu2]![txtSearchAgencies],"") & "*"))
ORDER BY tblAgencies.OrganizationName;

关键部分是赞..."行.关于它的几件事...请注意，查询将在表单中查找某些条件.您在查询的[Forms]！[frmMainMenu2]！[txtSearchAgencies]部分中看到了这一点.因此，在frmMainMenu2上有一个搜索文本框，称为txtSearchAgencies.

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.

还请注意，我正在使用NZ函数来确保对该文本框的预览至少返回一个空字符串.最后请注意，它使用两端都带有通配符的Like运算符，以便用户可以键入部分字符串.

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.

最后...在搜索框旁边，放一个命令按钮以执行过滤器/搜索.它所要做的就是像这样查询列表框...

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.重新查询.

Me.lstAgencies.Requery.

您还可以尝试在OnChange事件中重新查询，该事件将在键入时进行过滤.但是，如果您的查询缓慢，则可能无法正常工作.

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.

塞思

这篇关于从VBA中的表填充列表框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！