Full Version: All records not displayed in list box (only first 65535 records)
UtterAccess Forums > Microsoft® Access > Access Forms
Well, I'm actually using Access 2003, but creating a 2000 file format (all end users don't have 2003)
My problem is that I have a MATERIAL field on my form has a lookup, but it won't display all the records in the table/query it is looking in. It only displays the first 65535 records. I couldn't find a setting in the properties of the combo box, query, or table that would make it able to display all records.
I have about 100,000 Material records in the table I'm looking in, but I could have a couple hundred thousand in the future I guess...
I believe you're limited to the 65535 records in your combo box.
What I have done in cases like that is to actually create a pop up dialog box that has a sub form in DATASHEET view ... then there is no limitation.
You will need to use something other than a listbox/combobox for this; they are limited to 65,535 records. You could use a subform displaying a datasheet to simulate the functionality.

A list box with more than a few hundred items in it really becomes unusable from a user-interface perspective, anyway. You may want to rethink what you are trying to do.
I would suggest that having a combo/list box with 100K+ records would be slow and not very user friendly...
would suggest using another control to filter this list (like a search box)
Also this link should help
Good info and good points everyone, thank you! I will not use the list box for all these records... I looked at the link Danny sent, and that would be cool, but the code is a little above me head right now. I did searches on this form, but all the searches on "validation" in forms are for specific examples (i.e. length must be X or must be a number).
All I want is a simple validation on a field. I have a table that has part numbers. I have a datasheet form where the user will enter a part number. As soon as the user tabs or uses the mouse to exit that field I need it to validate the entry and display a custom message if it isn't in the part table... Any assistance would be greatly appreciated!
Why do you want to display so many records in a listbox?
The validation should be pretty easy, just use DCount.
If it returns 0 then the value entered isn't in the list.
Odon't need to display them in a listbox really, I just need the validation.
I'm not sure how to use the DCount (or much code at all really) but I'll do a search... I thought it would be a little easier, like in the text box property I could put ' =[PartTable]![PartNumber] ' in the Validation Rule, but that gives me the following error: ' The expression [PartTable] you entered in the form control's ValidationRule property contains the error The object doesn't contain the Automation object 'PartTable.'. ?
Off to more searching (on DCount now)...
Thank you!
Finally! I got it! I did the following on On Lost Focus to validate the material entered is in the other table:
Private Sub Material_LostFocus()
If DCount("*", "0400qry Validate Material") = 0 Then
MsgBox ("No data found")
End If
End Sub
Thanks everyone!
Are you guys sure that datasheet forms can display a very large number or records? I know Continuous forms are limited to 65535 records
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.