Microsoft Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com

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:

1. Leave the combo's RowSource property blank.
2. 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.
3. 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