Access 97 look up limitation??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box that uses a simple query as a row source to allow a user
to look records up based on a unique 10 digit key. Entries ARE limited to
the list.

I have around 48,000 records in the table being searched.

Suddenly valid numbers are yeilding a "Not in list" message.

There only seem to be specific records that this happens on, so I am
wondering if this is an issue of capacity. Since this has worked for some
time. If I sort the row source differently the results seem to change.
Currently the row source is:
SELECT DISTINCTROW Table.Field, table.Anotherfield FROM table;

When I SORT the list I can manually find the value, but it still does not
see it as "In list" as far as the error is concerned.

SELECT DISTINCTROW Table.Field, table.Anotherfield FROM Account ORDER BY
Table.Field;

Any ideas that might be causing this?
 
What setting do you have under:
Tools | Options | Edit/Find | Don't display lists where...?

If that is not the issue, it is possible that an index has corrupted, so
try:
Tools | Database Utilities | Compact/Repair.

Is there anything special about the ones that are not found? For example, do
they contain hypens? There is an issue with special characters like hypens
being matched in Access 2000 and later.

48k records is not going to be very efficient. Would you consider
delay-loading the combo? Details in:
Combos with Tens of Thousands of Records
at:
http://members.iinet.net.au/~allenbrowne/ser-32.html
 
I will look into your suggestions, thank you for taking the time to offer
them!!

I have found one thing experimenting on my own, and that is that the problem
seems to be somehow linked to the auto expand. If I turn the property off,
and tab to the box, type the value I am looking for it fails (Claims the item
is not in the list).

If I tab to the control, taking care to not let the control expand, but
simply type the value and hit enter (or allow the form to update in any way),
it finds the record perfectly.

There are no special characters in the field, simply a string of numbers up
to 10 digits long.

I will try your suggestions.

Thanks.

Rick
 
Back
Top