MS Access Project - Maximum Records in drop-down box

[Norma Jean]

There is a place in the form to increase the number of records for the form
to be more than 10,000. My drop-down box listing the people in my database
has increased to over 10,000 names. How can I increase this. I have put in a
query reducing the number of names to access, but I still keep bumping up
against the limit. Any ideas other than splitting it into two boxes.

 

[Douglas J. Steele]

10,000 names in a combo box is FAR too many (by a factor of over 9,000 or
more?)

Take a look at what Allen Browne has at http://allenbrowne.com/ser-32.html
for some ideas.

 

[Norma Jean]

Thank you for your reference. That is exactly what I need. His explanations
are, of course, very brief. I changed his suggested code into the following.
I am getting an error, "Invalid column name 'fileas'". I'm not sure how to
indicate which column this is. In the table "People" FileAs is the second
column with the first column being PeopleId which is the Primary Key.

Option Compare Database
Dim scombo1Stub As String
Const conCombo1 = 3
Function ReloadCombo1(scombo1 As String)
Dim sNewCombo1 As String 'First chars of Combo1 in frmPplFind
sNewCombo1 = Nz(Left(scombo1, conCombo1), "")
'If first n chars are the same as previously, do nothing
If sNewCombo1 <> scombo1Stub Then
If Len(sNewCombo1) < conscombo1 Then
'Remove the rowsource
Me.Combo1.RowSource = "SELECT FileAs from People where
scombo1stub" = ""
Else
'New RowSource
Me.Combo1.RowSource = "select filesas from people where
(combo1stub like """ & sNewCombo1 & "*"")"
scombo1Stub = sNewCombo1
End If
End If

End Function

Private Sub Form_Current()
Call ReloadCombo1(Nz(Me.Combo1, ""))
End Sub

Private Sub Combo1_Change()
Dim cbo As ComboBox 'combo1 combo
Dim sText As String 'text property of combo

Set cbo = Me.Combo1
sText = cbo.Text
Select Case sText
Case " "
cob = Null
Case Else
Call ReloadCombo1(sText)
End Select
Set cbo = Nothing
End Sub

 

[Norma Jean]

Specifically, I believe my problem is in the where statement.

If Len(sNewStub) < conPplLookupMin Then
'Remove the RowSource
Me.PplLookUp.RowSource = "SELECT FileAs FROM People WHERE
(False);"
sPplLookupStub = ""
Else
'New RowSource
Me.PplLookUp.RowSource = "SELECT FileAs FROM People WHERE FileAs
Like """ & _
sNewStub & "*"") ORDER BY FileAs"
sPplLookupStub = sNewStub
End If