Retrieve 65K records listed in combo box only from 155K records table
I have a table with 155k records. I can only scroll 65k records in a form combo box with query or sql records source by selecting three fields from that table. Why doesn't it list all 155K records even the query I'm using as the record source shows all the records outside the form.
The simplest approach is to assign the combo box to Rowsource only after you type some characters into it. You would do this in the OnChange combo box:
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 in a combo box, nothing happens until you type 3 characters (or 2 or 4 or whatever to filter the dropdown down to the number of human-usable records), at which time the rowource ( and if you like, you can display the list at this point to make it more user-friendly). From now on, it will work just like any regular combo unit.
If you need to use the same combo box to display data in records that have not been entered, you can make the default combo box Rowsource like this:
PARAMETERS [Forms]![MyForm]![MyField] Text ( 255 );
SELECT MyID, MyField FROM MyTable WHERE MyField = [Forms]![MyForm]![MyField];"
Then, in your OnChange event, you do the following:
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 do the following:
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 means is to make sure that all the values that have already been assigned to the field in which the combo box will be bound will be displayed. If you have an empty RowSource it won't work, so a reference to the current form value for that field and a UNION with a filtered list.
source to share
Typically, when you are faced with a hard constraint on a common and widely used component like a combobox, it's time to rethink your approach. The combobox is not intended for your use case and is not sufficient. Take a few steps back from your project and reevaluate your interface. Perhaps a ListView or a filtered data grid would be more appropriate.
source to share
65536 is indeed the limit for the number of items in a combo box.
I would suggest using a textbox with a search button to find someone from the list, rather than using a combo box in this case.
Alternatively, you can load items dynamically dependent on the inserted text.
You can also try to "prefilter" your combobox through the previously entered value in your form to get the number of entries returned below the maximum allowed. For example, if your table had 100,000 titles and addresses, you would have to enter a user (before using the combobox) zipcode or a state that will be used to filter the number of records your RecordBoS query returns.
@Stephen A. Lowe and amdfan: do you know what combobox is and why is it called combo-box? This text box is combined with a list box. You enter your text and the list displays the first entries that match your text.
So, I guess this is a legitimate question.
source to share
It is not difficult to create a combination of text boxes and lists, in which the list will be placed on the text box change event, gradually narrowing down the selected items. This will mean that your users can still see the list of names, rather than guessing, but a much shorter and more relevant list.
Here is an example SQL for a RowSource of such a list:
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 on the textarea, this will ensure that the current content of the textarea is used, but it also means that the textarea cannot be re-sent unless the txtSurname has focus, which is ok as the request only if txtSurname changes.
source to share
If you have a massive list, I would replace it with a table (or a form) inside a subform object. Combo and List blocks are limited to 65,536 objects and are slow to load compared to a table, query, or subform. I have 100,000 records in one table and it loads instantly. It scrolls very easily, much faster than a list, which means you don't have to narrow down your choices. I usually have a simple search box under my subform.
It's a little tricky because you have to reference the child form from the parent form and vice versa, but it's pretty easy to convert all of the combo / list code to subform code. for example if you want to find matching record in main form when you click record in subform: -
Private Sub POLICY_NO_Click()
Set Me.Parent.Recordset = CurrentDb.OpenRecordset("qryHPolicy")
Me.Parent.Recordset.FindFirst "[POLICY_NO]=" & Me.POLICY_NO & ""
End Sub
or set the subform to the same record source as the main form: -
Forms(Me_FormTo).Form.RecordSource = Me_RecSource
Forms(Me_FormTo).Combo6.Form.RecordSource = Me_RecSource
You can create a new kind of subform and work exactly like a list, so it is definitely worth it to have over 64K records.
source to share