Combobox hangs due to it having to validate entry against 500,000 records. Can it preload? Need a Combobox as I am using an autofill as you type

Question

I am still new at Access and have been struggling for two days. Please see below the code that I have used. It is a bit long winded, and I am not sure if that is causing my problem (that it takes a long time to run, sometimes the Combobox hangs for 2-3 seconds) as it runs through the code as well as the ListParts Query.

What I want to know is, is there a way to get the Combobox to preload the query so that when I start to input the number it doesn't hang? Please note I found some of the code like this but have been adding to it to try and improve the program.

Here is the code for a PartType Combobox that is the first field in the record. Once this is selected also from a drop down then

Private Sub PartType_AfterUpdate()
If Me!PartType.Value = "PARTS" Then
Me!PartNumber.RowSource = "ListPART"
Me!UnitPrice.Locked = True
ElseIf Me!PartType.Value = "LABOUR" Then
Me!PartNumber.RowSource = "ListLABOUR"
Me!UnitPrice.Locked = True
ElseIf Me!PartType.Value = "SUNDRIES" Then
Me!PartNumber.RowSource = "ListSUNDRIES"
Me!UnitPrice.Locked = False
ElseIf Me!PartType.Value = "SUBLET" Then
Me!PartNumber.RowSource = "ListBLANK"
Me!UnitPrice.Locked = False
End If

End Sub

I have also used this code in the Forms Change() Sub

Private Sub Partnumber_Change()
'filter dropbox as you type
    'If Len(PartNumber.Text) > 6 Then
    'Dim rs As Recordset
    'Set rs = CurrentDb.OpenRecordset(RecordSQL & " WHERE Code = '" & PartNumber.Text & "' ORDER BY [Code]")
     
    'If (rs.BOF And rs.EOF) Then 'only requery on no exact match
     '   PartNumber.RowSource = RecordSQL & " WHERE Code Like '*' & PartNumber.text & '*' ORDER BY [Code]"
      '  PartNumber.Dropdown
    'End If
    'End If
End Sub

The added kicker is that I have the following code running as I need to check and see if the part has been superceded. How it works is that it opens another form that populates with the data and then sees if there is an "S" is the supercede Textbox. There can sometimes be that a part has been superceded three or four times.

Private Sub PartNumber_AfterUpdate()

If Me!PartType.Value = "PARTS" Then
'FilterComboAsYouType Me.PartNumber, "SELECT * FROM ListParts", "Code"
'PartNumber.LimitToList = False
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
'DoCmd.Requery "ListParts"
'PartNumber.LimitToList = True
'DoCmd.GoToRecord , , acNext
'DoCmd.GoToRecord , , acPrevious
DoCmd.Close acForm, "PartPricesSUB"
DoCmd.OpenForm "PartPricesSUB"
Forms!PartPricesSUB.Visible = False
'supercede 1
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
        'supercede 2
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
    End If
        
    'supercede 3
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
    End If
        
    'Supercede 4
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
    End If
            
    'Supercede 5
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
    End If
            
 Else
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    Me!Description.Value = Forms!PartPricesSUB!Description.Value
    Me!UnitPrice.Value = Forms!PartPricesSUB!UnitPrice.Value
    Me!DscCode.Value = Forms!PartPricesSUB!DscCode.Value
    Me!Qty.Value = "1"
    DoCmd.GoToControl "Qty"
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value *           Me!UnitPrice.Value) * Me![Discount])
    End If

ElseIf Me!PartType.Value = "LABOUR" Then
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
DoCmd.Close acForm, "PartPricesSUB"
DoCmd.OpenForm "PartPricesSUB"
Forms!PartPricesSUB.Visible = False
Me!Description.Value = Forms!PartPricesSUB!Description.Value
Me!UnitPrice.Value = Forms!PartPricesSUB!UnitPrice.Value
Me!DscCode.Value = Forms!PartPricesSUB!DscCode.Value
Me!Qty.Value = "1"
DoCmd.GoToControl "Qty"
Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
        
    
ElseIf Me!PartType.Value = "SUNDRIES" Then
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
DoCmd.Close acForm, "PartPricesSUB"
DoCmd.OpenForm "PartPricesSUB"
Forms!PartPricesSUB.Visible = False
Me!Description.Value = Forms!PartPricesSUB!Description.Value
Me!UnitPrice.Value = Forms!PartPricesSUB!UnitPrice.Value
Me!DscCode.Value = Forms!PartPricesSUB!DscCode.Value
Me!Qty.Value = "1"
DoCmd.GoToControl "Qty"
Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])

    
ElseIf Me!PartType.Value = "SUBLET" Then
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
DoCmd.Close acForm, "PartPricesSUB"
DoCmd.OpenForm "PartPricesSUB"
Forms!PartPricesSUB.Visible = False
Me!Description.Value = Forms!PartPricesSUB!Description.Value
Me!UnitPrice.Value = Forms!PartPricesSUB!UnitPrice.Value
Me!DscCode.Value = Forms!PartPricesSUB!DscCode.Value
Me!Qty.Value = "1"
DoCmd.GoToControl "Description"
Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])

Else

End If
DoCmd.GoToControl "Qty"
'PartNumber.RowSource = RecordSQL
End Sub

Answer 1

A combo box is good for about 1000 rows, maybe 5,000 rows, and that's about it.

So, keep in mind that if you allow record navigation on a form (and often you should not), then when you move to the next record, then for Access to display/update/maintain the combo box, then it has to fetch from the data source for the combo box the ONE record for the combo box.

Of course, if you open (drop) the combo box, then it needs to load and display 500,000 rows - that design simply not going to work with that many rows. As I noted, even 5,000 rows are probably too many.

The fact that you ONLY seeing a 3-5 second delay is testament to the astounding speeds we enjoy today in terms of computer processing. However, your setup is likely running 100% on your computer, and if you ever plan to have multiple users, then a copy of the data base likely will not be on the computer, but in a shared folder, or even better, you be using say SQL server for the back-end database. Either way, once you introduce a network between your form and the data source, then such a design will run MANY times slower than what you have now (as much as 100x slower). That means the 3-5 second delay will morph into a 400 second delay (6 minutes, or even longer!!!).

So, that 500,000 row combo box simply has to go, and it not going to be workable at all - simply not a viable choice from UI point of view, and not a viable choice from a technology point of view.

I mean, how can a user scroll through and choose one row out of 500,000 rows? It simply not going to be a workable solution.

So, some solutions are:

If this is an invoice number, quote number, product id etc., then have the user just type in that value. If some type searching is required, then of course you need to pop up some type of search form for the user to use, and they can search and find the one product row ID by means of a nice form that allows such searching. Upon close, then the one row PK id (primary key) value can then be saved into your table.

Now, when you load the form, or navigate records, you’re dealing with an 500,000-row load and search that the combo box is faced with.

So, you need to build a nice (but really nice) form that allows the user to search and find the product - something far more then what a combo box can provide, but more important does not pull 500,000 rows and THEN presents a list of choices.

So, say I have on my form a city selection option. My city table has about 150,000 rows. So, as noted, a combo box not going to work.

So, we build a UI like this:

Note how while we have 150,000 rows, it is only a few mouse clicks to select a city.

So, in place of a combo box, you need some search form, and a REALLY good one that allows the user to select that product. Perhaps you have a few categories like the product type, inventory type, location or whatever. By adding a FEW simple filter and drill down categories, then such a search becomes easy for the user, but is also database friendly in that very few records are to be pulled from the database.

Now, above example was not Access, but in this access example, in place of a combo box to select the customer, the customer database is quite large (a few 100,000 rows). Once again, I had to replace the combo box with a pop up search form. This let's me filter by last name, and first name. So, even with a few 100,000 rows, the resulting filter list is only about 100-200 records tops, and that's going to be fast.

In some cases, I'll make the button look like a combo box "down arrow", but in fact I'm using a button. All in all, the concept is the same, we launch a form that allows some kind of filtering, and thus we not pulling in the whole table.

Think of any system you used in the past. When you use Google, it does not download the whole internet and you then use ctrl-f to find the record.

When you use some accounting system, you search for customer name, or invoice number, and a list of results is displayed for you to choose. So, be such software web based, or desktop based?

Think of any application you have used in the past - some type of searching form and means to narrow down what you looking to choose is provided.