Combo box with data from table with over 260,000 records

make your own control (textbox) that searchs for the first value available, each time you press a key (or better change the text value)

one method would be to have a separate combo box with a rowsource of something like:

Select DISTINCT LEFT([YourFieldName], instr([YourFieldName], "-")-1) as temp
FROM yourTable

or you could use a textbox to enter the first series of numbers.

Then, use either the change event of that control, or a button to dynamically configure the combo boxes rowsource, something like:

Private Sub text_Filter_Change

    me.cbo_ProductNum.RowSource = "SELECT ProductNum, ProductName " _
                                                         & "FROM yourTable " _
                                                         & "WHERE [ProductNum] Like """ & me.txt_Filter & "*"""

End Sub

I would suggest you think about having a combo box with anywhere near that amount of data.  If you want to make sure someone enters a correct value, I'd use the AFTERUPDATE event and run a query to see if the entered data is correct.

I'd never put that many items in a combo box (or list box).

Scott C.

see this link as reference

http://allenbrowne.com/ser-32.html

hth

Tom ... you are defeating the purpose of a combo box, with that many records ... unless you use some very specialized techniques.  But in the mean time, you can try this ... put this code in your Form Load event:

Private Sub Form_Load()
Dim x
x = Me.YourComboBoxName.ListCount

End Sub

This will force Access to populate the entire combo initially ..

mx

That (clarkscott's post) is why I prefer to use cascading combos or a text box with a filter button.

All that said, I'm running A2003 and I cannot find a limit to the number of rows a combo box will display ??

The limit I believe is ~65K ... that's in print somewhere ... pretty sure.

mx

That's what happens in a combo/list box ...  which is why - with a LARGE row source, it's seems the Scrolling never catches up (eventually it does).  So, doing the trick (thanks Marcus) I showed in the Form Load eliminates that ... in a combo box, and to some degree, but not completely ... in a List box.

That's also why the combo box will run slow (seemingly not responsive) while typing in characters.  It must find the match FIRST before it displays in the combo.  Thus, the user can type several characters before the match (MAY) be found.  I don't know about you, but I backspaced 3 times typing this comment ???).  I'm glad I didn't have to wait 3 seconds or longer to notice my typos.  

Scott C

Jeff pointed out in other traffic that Options/EditFind provides a box where you can insert a value.  It just so happens on my machine running A2003 with XP Pro and 1.5 gbytes of memory I have an upper limit of 32, 766 or (2^15) - 2  for that option.

Thank you for all of the comments.  Nothing has solved my problem directly.  The comment from boag2000 got my hopes up but it apparently doesn't apply to combo boxes.  I really do need the large number of records for the client.  Ideally, they would key in the number in one field and then the corresponding data fields (product name, active ingredient) would appear to validate that they keyed it correctly.  They are discarded pesticides, so in theory, they could be collecting products that were produced in 1976, so I really can't date-range them.  I have created the filter box option (i.e. For 100-585, enter the 100 in one box, then tab to the 2nd box and type the 585, which does then allow for the fields to be be displayed), but that causes extra keystrokes for the clients which they indicated in the business requirements was something that they wanted to decrease in the rewrite.

Obviously there are exceptions to every rule.

In your case 260000 records just threw up red flags...
Can't you filter on the Category?
http://www.epa.gov/pesticides/about/types.htm

I mean, ...if your list is being truncated so you can't see all the records, ...it's no good for you anyway...

What about just displaying a textbox for them to enter a "partial" part number.
Then run a query off this value to return a wildcard match.
Then they can use the results of this query to find the exact PartNumber.

..Just a variation what what other Experts have posted...

JeffCoachman