Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box - Too many records? (Version 2000)

    I have a form containing two combo boxes containing identical code except for their data source. One combo box pulls from a table containing 2000 records or less. The second combo box uses a table containing 60,000 records. Each combo box has 14 columns. All of the columns except for five are set to 0" width. Once a name is selected in the combo box, the data in each column is being pushed into fields on the form using the after update event. All works fine when I am using the combo box pulling from the small table, but when I use the combo box pulling from the larger table, the data appears in each field of the form and then gives me an invalid bookmark error. I am not even using a bookmark in the code. If I press esc several times or exit the form and then open it again the data is there.

    To check if the size of the table was the cause of the problem, I appended the records from the large table into the smaller one and sure enough the combo box referencing the small table then had the same problem.

    Is there a maximum size or number of records that a combo box can satisfactorily work with? I currently am saving the record prior to the error so my data is not lost but I hate to tell users they have to press escape several times whenever they get the bookmark error.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box - Too many records? (Version 2000)

    This is not an appropriate use of comboboxes, and yes, there are limits. The optimum length of a combobox list is around 100 items. Anything over that slows down, and I believe the max is 15,000 items, but my memory may be going. The idea of comboboxes is that they give you a *quick* match. There isn't anything quick about matching every keystroke in 60,000 records, even in the single field it's matching on. Also, if you're actually displaying 14 columns, that's visual overkill. If you aren't displaying them, why bother? All you need is the PK for the record selected in order to be able to open the appropriate recordset and populate the form from that.

    You would be better advised to use a textbox to allow the user to enter a "find" critieria. The in the afterupdate event of the textbox, use code to set the rowsource for your combobox to only those records that are like your textbox contents plus and asterisk. Then the user can pick the correct match from a shorter list in the combobox. That will be much faster all around and should get around the problem of too many rows in the combobox.
    Charlotte

  4. #3
    Star Lounger bobdog's Avatar
    Join Date
    Jan 2001
    Posts
    83
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box - Too many records? (Version 2000)

    Charlotte's right about limits on combo boxes. It's a problem that's been around for a looong time. Basically, you're fetching back a huge pile of data every time you refresh. And 14 columns just adds to the mess. Back in my Foxpro days, several hundred records was about the most people recommended. 60,000 is over the top and won't get you a raise.

    On 'tuther hand, couldn't you do a query and use the query results as your recordsource? Seems like I saw an article to that effect in Advisor in the last few months. I can imagine filling a combo box with say letters of the alphabet or department names (depends on what you're fetching), and then re-source the combobox on the fly in the after update event, bringing back just the records that fit the query in the "where" clause.

    Just a thought.

  5. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box - Too many records? (Version 2000)

    That's exactly what I was suggesting. I use a textbox and allow the user to enter the criteria, then set the rowsource of the combobox using that criteria in the on-the-fly query.
    Charlotte

  6. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Too many records? (Version 2000)

    Thanks to both of you for your most helpful response. Of all the books I have read, I had never seen the issue of data maximum for a combo box addressed. I knew it was slow but didn't know that it should not be used with a record source that large. My application is designed for keeping track of discipline and injuries that occur in each school. The people table has to be filled with student names and their info. Because students come and go, I have to capture all the data for each person at the time the event occurs. What I am trying to do is provide a way for the input person to select the student and all their data from one of two tables by use of a combo box, one displaying all the students at that school and one displaying all the students in the school division.

    I used the suggestion to supply a text box for typing the beginning of the name the user is searching for and making that the criteria for the query (plus an asterisk) that the combo box is using. I placed this text box in the location that the user would normally use to type in the combo box and then when the text box loses focus, which will happen when the user clicks the drop down arrow for the combo box, the combo box is requeried and the text box becomes invisible. It is working a lot faster although I am still working through a couple of problems. I would like to make the combo box drop down through code after it is requeried. What code would accomplish that? At this time, I click it once and it requeries the combo box but does not drop down so I have to click it again once the hourglass is gone.

    I would like to try the method you suggested of setting the the row source in code for the combo box but could not figure out how to accomplish that. Do you have a sample table with an example of the code to do that?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  7. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - DropDown(Version 2000)

    You can use the Dropdown method to force the list in the specified combo box to drop down when the control receives the focus.
    Syntax is: yourcomboboxname.Dropdown

  8. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box - Too many records? (Version 2000)

    I don't understand. Do both your combos list student names? That's doing it the hard way. You can't build infinitely flexible applications, at least not with the current technology. Do they always know the division? If so, make them choose a division first, and then use that to filter down the list of students. The division combo should be a short one, and that should reduce the number of names eligible for the second combo even before using a find approach.

    The problem you're having is that clicking the down arrow interferes with other code before it has a chance to run. If you put a Dropdown in the combo's GotFocus event, it will drop down automatically, but clicking on the arrow will interfere with that.

    Here's what I usually do:

    1. Disable the combobox by setting its enabled property to false or by making the combobox invisible or simply by removing its rowsource.

    2. Use the AfterUpdate event of the textbox to create the rowsource SQL statement in code. Then enable (or make it visible) the combobox and set the rowsource property of the combobox to that SQL string. That's where you requery the combobox, not from any of the combobox events.
    <pre>Private Sub txtFind_AfterUpdate()
    Dim strSQL As String

    strSQL = "SELECT * FROM tblStudent " _
    & "WHERE tblStudent.LastName Like '" _
    & Me!txtFind & "*';"
    Me!cboStudent.RowSource = strSQL
    Me!cboStudent.requery
    Me!cboStudent.DropDown
    End Sub</pre>

    If you're filtering by Division, then you would need to add the division to the criteria part of the rowsource string.
    Charlotte

  9. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Too many records? (Version 2000)

    Concerning the number of entries in a combo/list. There is a difference between what is possible and what is useful. It is a part of designing a user interface and an important part is that it can be used, sometimes overlooked with all of the possibilites available.
    For searching in a large selection of records I sometimes use a special search dialog which has various filtering possibilites and can be called by eg. F4 keypress or a mouse double click in the edit field. If the user knows the 'ID' then it can be entered directly and then checked for validity when the user leaves the field (or even during entry).

  10. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Too many records? (Version 2000)

    Andy, do you have a sample database or sample code to accomplish what you are describing. I have implemented Charlotte's code and it has speeded up the process but has not eliminated the error messages. Even though I am filtering the data using the text box, the size of the table from which the data is being pulled must still be causing problems.

    If I reduce the size of the table to 10,000 records or less, no errors are created. But just filtering them through an SQL statement is not doing the trick. My other thought is maybe I could create a temp table based on the filtered data and use that for the row source. I know this has the potential for creating bloat in my database but I have it compacting automatically each time it closes.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  11. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Too many records? (Version 2000)

    Creating a temp table is a bad idea. I imagine the performance would drop considerably.
    I can't supply sample code - mine is embedded in a dll and developed with C++. I use a virtual list control to assist with performance. It 'refreshes' in a fraction of a second on a 90 Mhz machine based on a table with thousands of records.
    I have attatched a bitmap so that you can see what I have done.

    Concerning the error messages is it still about the source being too big for the combo? Again, for me, a combo with more than a few choices for each starting letter is getting unwieldy. The user HAS to be able to make an accurate rapid choice else it is not the correct tool for the job.
    Attached Images Attached Images

  12. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Too many records? (Version 2000)

    Let me explain fully what I need to do and maybe there may be another solution that someone could suggest. For example, if a fight occurs in a school. We need to pull each student's info (name, address, phone, their school, sex, ethnic code, etc.). I can not simply record their student number and then link to the table containing all the student data because the students move around alot and the data has to be captured and stored into a people table so that it reflects their data at the time the event occured.

    Most of the time the event would involve students from their own school and that is no problem. The combo box designed to select a student from their own school pulls from a table containing 2000 or fewer names and the user simply starts typing in the student's last name and then selects the student involved which then pushes the data into the fields in the people table by use of a subform for the event. However, one of the participants in the fight may be from a different school and we need to be able to provide a way for them to select that student's data from a table containing all the students from schools in our county (roughly 60,000 records).

    In the all school's combo box the process has been speeded up by having a user type the first couple letters of the student's last name into a text box and using the code Charlotte posted, a filtered list is used by the combo box to display only those that begin with the characters typed in the text box.

    What happens next is the data from the student I selected displays for an instant and then I get an error message* (not the same one each time) and each of the fields in my form turn to "#Error". The funny thing is that if I press esc several times, each field then returns to the data it is supposed to display. After this happens and the errors are cleared, I can select from the countywide combo box and will not get another error until I close the form and reopen it and then the error procedure will be repeated.

    *Error messages that display will say one of the following :
    "Not a valid bookmark" (No bookmark is referred to in the code I am using)
    "Can not change the record because another user is making changes" (no other user is using the database)
    "Unrecognized database format C:WindowsTempJetC6F1.tmp"
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  13. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box - Too many records? (Version 2000)

    <hr>"Unrecognized database format C:WindowsTempJetC6F1.tmp"<hr>
    Is this a file you explicitly use/create or is it a temp file Jet is creating?
    Charlotte

  14. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Too many records? (Version 2000)

    This is referring to a temp file Jet is creating. The other two messages I got prior to using the textbox to filter info for the combo box. This message was a new one that I get from time to time now. But just like the other messages, I can click OK and then press esc several times and my data that I had the combo box push into my fields will then reappear.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  15. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Too many records? (Version 2000)

    Have you tried compact and repair on the front and back ends?
    If so then have you used the Jetcomp utility to repair the front and back ends?

    To select I think something along the lines I posted could work. From the popup dialog you can enter the first couple of letters of the name in the name field, then likewise for the school and after just a few keypresses hopefully have just a few entries in the list to select.

  16. #15
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Too many records? (Version 2000)

    Where can I obtain or download the Jetcomp utility. I run compact and repair automatically each time the front end or back end is closed.

    I finally found an article on Microsoft's knowledge base providing the data limits for a combo box. There is a maximum of 65,536 rows that Access 2000 can display in a combo box and a 64kb size total.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Page 1 of 2 12 LastLast

Posting Permissions

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