Combo Box Limit (2K3)

Topic

New Reply

I have a combo box on a form that performs a look up of names. There are approximately 71,000 records (and more to enter) in the table that the combo box looks up data in.
The names are sorted alphabetically by last name. Currently, the lookup only will show names up to names beginning with TUCH (approximately the 65,000 name).

Is there a limit to how many records can be displayed in the drop down window of a combo box.? And if so, is there a way to display all the records in an alternate way?

Reply | Quote

Replies

A list box or combo box can display 65,536 (2 to the power 16) items, but you should keep the list much shorter – working with long lists is a pain.
One workaround is to use cascading combo boxes, for example the user selects the first letter of the name from one combo box, and this causes a second combo box to display only names beginning with that letter. This will limit the number of items displayed in the second combo box. The idea could be taken further, if needed.

See How to synchronize two combo boxes on a form in Access 2002 or in Access 2003, or search this forum for cascading combo box.

Reply | Quote

Thank you Hans. I will try this out and see how it works.

Reply | Quote

Another option is to have an option group with 26 buttons that restricts the combo box to names starting with the selected letter.

Here is the code I have used for that

Private Sub AlphaGroup_AfterUpdate()
Dim strChar As String
Dim strFilter As String
Dim sql As String
strChar = Chr(Me!AlphaGroup + 64)
strFilter = ” where [surname] like ” & chr(34) & strChar & “*” & chr(34)
sql = “SELECT tblMembership.RecordNumber, [surname] & ‘, ‘ & [firstname] AS Member , tblMembership.Status FROM tblMembership ”
sql = sql & strFilter
sql = sql & “ORDER BY [surname], [firstname] ”
Me!ComboMember.RowSource = sql
End Sub

Added a bit later
The value of each button in the group is a number starting at 1,2,3, etc

Reply | Quote

A sample database that illustrates this approach is attached to post 189,484 (it’s in Access 97 format, but if works when converted to Access 2000 or 2002/2003 format).

Reply | Quote

Thank you Hans & John,

I decided to utilize the toggle button option and it is working great. I appreciate your help

Reply | Quote