Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Combo Box Limit (2K3)

    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?
    Easy Access

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Limit (2K3)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combo Box Limit (2K3)

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

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Combo Box Limit (2K3)

    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


    [i]Added a bit later
    The value of each button in the group is a number starting at 1,2,3, etc
    Attached Images Attached Images
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Limit (2K3)

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

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combo Box Limit (2K3)

    Thank you Hans & John,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •