I have an Access table that has about 500,000 records. I want to trim down the results as I type text into the ComboBox. Basically, I need a dynamic ComboBox that displays records based on each character as I type into the ComboBox.
我有一个Access表,有大约500,000条记录。当我在ComboBox中输入文本时,我想减少结果。基本上,我需要一个动态的ComboBox,当我在ComboBox中输入时,它会根据每个字符显示记录。
The problem is, the underlying table for the ComboBox has about 500,000 records. And the ComboBox object can only display 65,000 records before it reaches it's limit, and won't preview the rest of the records. Nobody looks at 65,000 records at the same time but when any number in the middle of the entire record set is typed, the records won't display at all. Because that particular number is beyond the 65,000 limit. So i need a dynamic combobox for the whole table.
问题是,ComboBox的基础表有大约500,000条记录。并且ComboBox对象在达到其限制之前只能显示65,000条记录,并且不会预览其余记录。没有人同时查看65,000条记录,但是当输入整个记录集中间的任何数字时,记录根本不会显示。因为该特定数字超出了65,000的限制。所以我需要一个动态组合框用于整个表格。
For example, if you are looking at a bunch of SSNs, instead of ComboBox displaying all the records which it can't because too many SSNs (more than 65,000 which is ComboBox limit) this is what I need.
例如,如果您正在查看一堆SSN,而不是ComboBox显示它不能的所有记录,因为太多SSN(超过65,000这是ComboBox限制)这是我需要的。
If you type 5 in the ComboBox. The list would only display the 5-series of numbers. If you type 51 it limits the list values are the 51-series of numbers. If you type 512 it limits the list values to the 512-series of numbers and so on. FOR THE WHOLE TABLE THOUGH, not just the first 65K.
如果在ComboBox中键入5。该列表仅显示5系列数字。如果键入51,则限制列表值为51系列数字。如果键入512,则会将列表值限制为512系列数字,依此类推。对于整个表格,不仅仅是第一个65K。
WHAT I HAVE TRIED SO FAR is implementing a second unbound text box, however, this makes querying a two step process. I am wondering if I can just do it all together.
我做了什么所以我们实现了第二个未绑定的文本框,但是,这使得查询分为两个步骤。我想知道我是否可以一起完成这一切。
1 个解决方案
#1
2
David W Fenton already provided a fairly detailed answer to this problem here. The gist:
David W Fenton已经在这里提供了一个相当详细的答案。要旨:
The simplest approach is to assign the combo box's Rowsource only after you type some characters in it. You'd do that in the combo box's OnChange event
最简单的方法是只在您输入组合框中的某些字符后才分配组合框的Rowsource。你可以在组合框的OnChange事件中做到这一点
Allen Browne's Combos with Tens of Thousands of Records has some other bells and whistles (abstracting the logic into a function, preventing recalculation of the RowSource if the value hasn't changed), but the basic idea is the same:
Allen Browne与成千上万的记录的组合有一些其他的花里胡哨(将逻辑抽象为函数,如果值没有改变则阻止重新计算RowSource),但基本思想是相同的:
- Leave the combo's RowSource property blank.
将组合的RowSource属性留空。
- Create a function that assigns the RowSource after a minimum number of characters has been typed. Only entries matching these initial characters are loaded, so the combo's RowSource never contains more than a few hundred records.
创建一个函数,在键入最少数量的字符后分配RowSource。只加载与这些初始字符匹配的条目,因此组合的RowSource永远不会包含超过几百条记录。
- Call this function in the combo's Change event, and the form's Current event.
在组合的Change事件和表单的Current事件中调用此函数。
If you really want to successively filter the RowSource as each character is typed, you could adapt these answers to do that. For example, you could change the test condition in the David W Fenton solution to If Len(Me!cmbMyCombo.Text) >= 1. For the Allen Browne solution, you could set the constant to 1 and change the line that sets sNewStub to: sNewStub = Nz(sSuburb, "").
如果你真的想在键入每个字符时连续过滤RowSource,你可以调整这些答案来做到这一点。例如,您可以将David W Fenton解决方案中的测试条件更改为If Len(Me!cmbMyCombo.Text)> = 1.对于Allen Browne解决方案,您可以将常量设置为1并更改将sNewStub设置为的行:sNewStub = Nz(sSuburb,“”)。
However, until your RowSource query is returning a chunk of numbers that is less than the row limit, you're not going to see any benefit. This is why both solutions linked above begin filtering after 2 (or 3 or 4) initial characters have been typed.
但是,在您的RowSource查询返回一小于行限制的数字之前,您不会看到任何好处。这就是为什么上面链接的两个解决方案在键入2(或3或4)个初始字符后开始过滤的原因。
#1
2
David W Fenton already provided a fairly detailed answer to this problem here. The gist:
David W Fenton已经在这里提供了一个相当详细的答案。要旨:
The simplest approach is to assign the combo box's Rowsource only after you type some characters in it. You'd do that in the combo box's OnChange event
最简单的方法是只在您输入组合框中的某些字符后才分配组合框的Rowsource。你可以在组合框的OnChange事件中做到这一点
Allen Browne's Combos with Tens of Thousands of Records has some other bells and whistles (abstracting the logic into a function, preventing recalculation of the RowSource if the value hasn't changed), but the basic idea is the same:
Allen Browne与成千上万的记录的组合有一些其他的花里胡哨(将逻辑抽象为函数,如果值没有改变则阻止重新计算RowSource),但基本思想是相同的:
- Leave the combo's RowSource property blank.
将组合的RowSource属性留空。
- Create a function that assigns the RowSource after a minimum number of characters has been typed. Only entries matching these initial characters are loaded, so the combo's RowSource never contains more than a few hundred records.
创建一个函数,在键入最少数量的字符后分配RowSource。只加载与这些初始字符匹配的条目,因此组合的RowSource永远不会包含超过几百条记录。
- Call this function in the combo's Change event, and the form's Current event.
在组合的Change事件和表单的Current事件中调用此函数。
If you really want to successively filter the RowSource as each character is typed, you could adapt these answers to do that. For example, you could change the test condition in the David W Fenton solution to If Len(Me!cmbMyCombo.Text) >= 1. For the Allen Browne solution, you could set the constant to 1 and change the line that sets sNewStub to: sNewStub = Nz(sSuburb, "").
如果你真的想在键入每个字符时连续过滤RowSource,你可以调整这些答案来做到这一点。例如,您可以将David W Fenton解决方案中的测试条件更改为If Len(Me!cmbMyCombo.Text)> = 1.对于Allen Browne解决方案,您可以将常量设置为1并更改将sNewStub设置为的行:sNewStub = Nz(sSuburb,“”)。
However, until your RowSource query is returning a chunk of numbers that is less than the row limit, you're not going to see any benefit. This is why both solutions linked above begin filtering after 2 (or 3 or 4) initial characters have been typed.
但是,在您的RowSource查询返回一小于行限制的数字之前,您不会看到任何好处。这就是为什么上面链接的两个解决方案在键入2(或3或4)个初始字符后开始过滤的原因。