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.:
Const conSuburbMin = 4
Provided by Allen Browne, abrowne@odyssey.apana.org.au
