The Maximum Number of Rows allowed in a Combo/List Box, and how to work around this

The Maximum Number of Rows allowed in a Combo/List Box, and how to work around this

In Access, there is a limitation to the number of rows allowed in either a Combo or List Box of 65,535.

As well as this limitation, consider the poor user that needs to scroll through 65,000 records to select the one that they want. Reallistically, you should not have more than about a few hundred items in a Combo box to help the user.

Also, you will find that the performance of the form will suffer as these records need to be loaded into, either when the Form loads or the control gets the focus.

If you really do want to display a large number of records in a Combo or List Box, then you can use this method:

Private Sub cboIPID_Change()
    Dim strSQL As String
    If Len(Me!cboIPID.Text & "") >= 4 Then
        strSQL = "SELECT IPID, Domain" _
            & " FROM tlkpIPAddress" _
            & " WHERE Domain<>'-' AND Domain LIKE '" & cboIPID.Text & "*'" _
            & " ORDER BY Domain;"
        Me!cboIPID.RowSource = strSQL
    Else
        Me!cboIPID.RowSource = ""
    End If
End Sub

Private Sub Form_Current()
    Dim strSQL As String
    If Not IsNull(Me!IPID) Then
        strSQL = "SELECT IPID, Domain" _
            & " FROM tlkpIPAddress" _
            & " WHERE IPID=" & Me!IPID
        Me!cboIPID.RowSource = strSQL
    Else
        Me!cboIPID.RowSource = ""
    End If
End Sub

Top

Last modified at 06/06/2006 14:54:29