Combos with Tens of Thousands of Records Combos become unworkable with many thousands of records, even many hundreds in Access 2. By loading records into the combo only after the user has typed the first three or four characters, you can use combos far beyond their normal limits, even with the AutoExpand property on.
This is the idea:
Leave the comboÆs RowSource property blank. 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. Call this function in the comboÆs Change event, and the formÆs Current event.
Example: Look up Postal Codes from Suburb For this example you need a table named Postcodes, with fields Suburb, State, Postcode. You may be able to create this table from downloaded data, for example postcodes for Australia. Make sure all three fields are indexed.
You also need a combo with these properties:
Name Suburb RowSource BoundColumn 1 ColumnCount 3
Step 1: Paste this into the General Declarations section of your formÆs module:
Dim sSuburbStub As String Const conSuburbMin = 3 Function ReloadSuburb(sSuburb As String) Dim sNewStub As String ' First chars of Suburb.Text
sNewStub = Nz(Left(sSuburb, conSuburbMin),"") ' If first n chars are the same as previously, do nothing. If sNewStub <> sSuburbStub Then If Len(sNewStub) < conSuburbMin Then 'Remove the RowSource Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM Postcodes WHERE (False);" sSuburbStub = "" Else 'New RowSource Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM Postcodes WHERE (Suburb Like """ & _ sNewStub & "*"") ORDER BY Suburb, State, Postcode;" sSuburbStub = sNewStub End If End If End Function
Step 2: In the formÆs Current event procedure, enter this line:
Call ReloadSuburb(Nz(Me.Suburb, ""))
Step 3: In the comboÆs Change event procedure, you could also use a single line. The code below illustrates how to do a little more, blocking initial spaces, and forcing "Mt " to "Mount ":
Dim cbo As ComboBox ' Suburb combo. Dim sText As String ' Text property of combo.
Set cbo = Me.Suburb sText = cbo.Text Select Case sText Case " " ' Remove initial space cbo = Null Case "MT " ' Change "Mt " to "Mount ". cbo = "MOUNT " cbo.SelStart = 6 Call ReloadSuburb(sText) Case Else ' Reload RowSource data. Call ReloadSuburb(sText) End Select Set cbo = Nothing
Step 4: To assign the State and Postcode, add this code to the comboÆs AfterUpdate event procedure:
Dim cbo As ComboBox Set cbo = Me.Suburb If Not IsNull(cbo.Value) Then If cbo.Value = cbo.Column(0) Then If Len(cbo.Column(1)) > 0 Then Me.State = cbo.Column(1) End If If Len(cbo.Column(2)) > 0 Then Me.Postcode = cbo.Column(2) End If Else Me.Postcode = Null End If End If Set cbo = Nothing
The combo in Use As the user types the first two characters, the drop-down list is empty. At the third character, the list fills with just the entries beginning with those three characters. At the fourth character, Access completes the first matching name (assuming the comboÆs AutoExpand is on). Once enough characters are typed to identify the suburb, the user tabs to the next field. As they leave the combo, State and Postcode are assigned.
The time taken to load the combo between keystrokes is minimal. This occurs once only for each entry, unless the user backspaces through the first three characters again.
If your list still contains too many records, you can reduce them by another order of magnitude by changing the value of constant conSuburbMin from 3 to 4, i.e.: