get 65K records only listed in combo box from a table of 155K records.

Question

I have a table of 155K records. I can scroll only 65K records in combo box of a form having recordsource with query or sql, selecting three fields from that table. Why it does not list all 155K records even the query, which I am using as recordsource, shows all records outside of the form.

Answer 1

perhaps the item index is a 16-bit integer

a better question would be, do you really expect the user to scroll through 155 thousand items in a combobox?

Answer 2

Another person in agreement that this would be an insane way to give the user access to this data. No one will ever want to parse through 100k + records at once. I would provide a way to filter the records beforehand so that they are only presented with the ones that are of interest at the time.

Answer 3

65536 is indeed the limit for the number of combobox items.

I would suggest using a text box with a search button to find someone from the list rather than using a combo box in this case.

As alternative you could load the items dynamically dependent from the text inserted.

You could also try to "prefilter" your combobox via a previously entered value on your form to get the number of records returned below the maximum allowed. For example, if your table had 100,000 worth of names and addresses, You would have the user enter (before using the combobox) a zipcode, or state that would be used to filter the number of records your combobox query RecordSource returns.

---

@ Steven A. Lowe and amdfan: do you know what a combobox is and why it is called combo-box? It's a textbox combined with a listbox. You type a text and the listbox show the first entries matching your text.

So, I guess that's a legitimate question.

Answer 4

So what you're really looking for is more an "Auto-complete" functionality, like what's seen in internet browsers? When you say combo-box, everybody pictures a drop-down where the user selects an item - you may want to clarify your question.

Answer 5

As a general rule, when you run into a hardcoded limit on a general and widely used component like a combobox, it's time to reconsider your approach. The combobox isn't designed for, and isn't sufficient for, your use case. Take a couple steps back from your project and re-evaluate your UI. Perhaps a ListView or a filtered data grid would be more appropriate.

Answer 6

It is not too difficult to create a textbox and listbox combination where the listbox is requeried in the change event of the textbox, progressively narrowing the items selected. This would mean that your users can still see a list of names, rather than guessing, but a much shorter and more relevant list.

Here is sample SQL for the RowSource of such a listbox:

SELECT tp.PersonKey, 
   tp.Surname & ", " & tp.Forename AS PersonName
FROM tblPersons tp
WHERE tp.Surname 
LIKE Forms!MyForm!txtSurname.Text
ORDER BY  tp.Surname, tp.Forename

Note the use of the Text property of the textbox, this ensures that the current contents of the textbox are used, but it also means that the textbox cannot be requeried except when txtSurname has focus, which is fine, because the requery should only be done in the change event of txtSurname.

Answer 7

It difficult to use in the real world, combobox store 155k records i think if change it to [...] browse button or other control I easy to use more than this way

Answer 8

Try something like incrementally filled combobox. e.g. the way tag search work in Stack Overflow, or the way Google Suggest works. 65k is good enough a limit for combobox. A normal user will find difficulty in selecting from even 65 things

Answer 9

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:

  Dim strRowSource As String

  strRowSource = "SELECT MyID, MyField FROM MyTable"
  If Len(Me!cmbMyCombo.Text)=3 Then
     strRowSource = strRowSource & " WHERE MyField Like '" 
     strRowSource = strRowSource & Me!cmbMyCombo.Text
     strRowSource = strRowSource & "*'"
     Me!cmbMyCombo.RowSource = strRowSource
     Me!cmbMyCombo.DropDown
  End If

When you type into the combo box, nothing happens until you've typed 3 characters (or 2 or 4 or whatever is appropriate to filter your dropdown list down to a human-usable number of records), at which time the rowsource is assigned (and, if you like, you can dropdown the list at that point, to make it more user-friendly). From then on, it will work just like any normal combo box.

If you need to use the same combo box to display data in records that aren't entered, then you could make the combo box's default Rowsource be:

  PARAMETERS [Forms]![MyForm]![MyField] Text ( 255 );
  SELECT MyID, MyField FROM MyTable WHERE MyField = [Forms]![MyForm]![MyField];"

Then in your OnChange event, you'd do this:

  Dim strRowSource As String

  strRowSource = "PARAMETERS [Forms]![MyForm]![MyField] Long; "
  strRowSource = strRowSource & "SELECT MyID, MyField FROM MyTable "
  strRowSource = strRowSource & "WHERE MyField = [Forms]![MyForm]![MyID]"
  strRowSource = strRowSource & "UNION SELECT MyID, MyField FROM MyTable"
  If Len(Me!cmbMyCombo.Text)=3 Then
     strRowSource = strRowSource & " WHERE MyField Like '" 
     strRowSource = strRowSource & Me!cmbMyCombo.Text
     strRowSource = strRowSource & "*'"
     Me!cmbMyCombo.RowSource = strRowSource
     Me!cmbMyCombo.DropDown
  End If

Then in the OnCurrent event of the form, you'd do this:

  Dim strRowSource As String

  strRowSource = "PARAMETERS [Forms]![MyForm]![MyField] Text ( 255 ); "
  strRowSource = strRowSource & "SELECT MyID, MyField FROM MyTable "
  strRowSource = strRowSource & "WHERE MyField = [Forms]![MyForm]![MyField]"
  Me!cmbMyCombo.RowSource = strRowSource

What this accomplishes is to make sure that any value already assigned to the field the combo box is bound to will still display. If you have a blank RowSource, that won't work, so the reference to the form's current value for that field and UNION it with the filtered list.

--
David W. Fenton
David Fenton Associates