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.

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”

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.

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.