combo box to search over 100k records (1 Viewer)

[dj59]

I have a database with over 100k records and I want to use a combo box to search for records by typing in the name. I found code to do this at: http://allenbrowne.com/ser-32.html.

This is working, however i have many fields on the form that I want to populate from the table after the search. Right now only the 3 fields from my rowsource combo box populate.

In my select statement do I need to SELECT * (all) and then populate the fields that way, or is there a way to populate from the table based on the ID of the record that is found?

Thanks.

 

[gemma-the-husky]

100K records is an enormous number for a combo box. Can you not pre-filter it in some way.

ie 3 cascading combo boxes of 50 choices each gives you 125K possibilities with none of the hassle of a single mega combobox

100 each gives a million possibilities.

 

[dj59]

This is a database has two tables. Person and Address. It holds addresses for over 100k people. When searching these records the user only has a name, so cascading combos won't work.

The code at http://allenbrowne.com/ser-32.html works for looking up by last name, first name, personID by entering the first 3 characters....BUT then I want it to display all the rest of the fields from the person table on the form.

Right now, I can display only the columns in the combo box. There must be a way to link the id from the combo box to the person table and display all the fields....

Plus I only want to display the fields, not save them. Right now it trys to save them and gives a message that you can not save duplicates.

It works fine if you use the rowsource as the columns in the table you want to use for look up, however that way, it won't retrieve the names if they are too far down the line, like row item 50k, etc.

Any ideas would be welcomed.
Thanks.

 

[dj59]

I would like to offer the solution I found for anyone else who might need it. Allen Browne's code is excellent for finding a record, however if you need to edit the record once you find it, you need to change the following code:

Use Allen Browne's code then.....

In the combo box on change event:

    Dim cbo As ComboBox         ' LAST_NAME combo.
    Dim sText As String         ' Text property of combo.
    
 [B][COLOR=red]   Set cbo = Me.cboLAST_NAME
    cbo.SetFocus
    sText = cbo.Text
[/COLOR][/B]    
    Select Case sText
    Case " "                    ' Remove initial space
        cbo = Null
    Case "MT "                  ' Change "Mt " to "Mount ".
        cbo = "MOUNT "
        cbo.SelStart = 6
        Call ReloadLAST_NAME(sText)
    Case Else                   ' Reload RowSource data.
        Call ReloadLAST_NAME(sText)
    End Select
    Set cbo = Nothing

In the combo box after update event replace Allen's code with the following:

[B][COLOR=red]    With Me.LAST_NAME
        If Not IsNull(.Value) Then
          Me.Recordset.FindFirst "CLIENT_ID = " & cboLAST_NAME.Column(2)
        End If
    End With[/COLOR][/B]

Thanks.