Combo box returning only about 1000 records

[cjbuechler]

I have a form with a combo box that only returns about 1000 records from the table it references. As we add new records to the table the ones at the end no longer show in the combo box until we get the total number back down.

Question: Is this a limitation of combo boxes (I had never seen it before) or something else?

The combo box's rowsource is obtained through the following code:

Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim sInLocation As String
Dim varTemp As Variant

'get data the user has rights to
LocationID.RowSource = "SELECT tblLocations.LocationID, tblLocations.LocName " _
& "FROM tblLocations INNER JOIN tblEmployeeLocations ON " _
& "tblLocations.LocationID = tblEmployeeLocations.LocationID " _
& "WHERE tblEmployeeLocations.EmployeeID = " & gsUserEmpID
cboIndividual.RowSource = "SELECT DISTINCT tblIndividual.IndividualID, " _
& "ISNULL(tblIndividual.LastName, '') " _
& "+ ', ' + ISNULL(tblIndividual.FirstName, '') " _
& "+ ' ' + ISNULL(tblIndividual.MiddleName, '') AS FullName " _


& "FROM tblEmployeeLocations INNER JOIN tblLocations ON " _
& "tblEmployeeLocations.LocationID = tblLocations.LocationID INNER JOIN " _
& "tblEmployees ON tblEmployeeLocations.EmployeeID = tblEmployees.EmployeeID " _
& "RIGHT OUTER JOIN tblIndividual ON tblLocations.LocationID = tblIndividual.LocationID " _
& "WHERE tblEmployees.User_Name = '" & gsUserName & "' " _
& "ORDER BY ISNULL(tblIndividual.LastName, '') " _
& "+ ', ' + ISNULL(tblIndividual.FirstName, '') " _
& "+ ' ' + ISNULL(tblIndividual.MiddleName, ''), " _
& "tblIndividual.IndividualID"

sSQL = "SELECT LOCATIONID FROM tblEmployeeLocations " _
& "WHERE EMPLOYEEID = " & gsUserEmpID

[jzwp11]

Check Options->Edit/Find, you might see a limit. (See attached for an example)

Why would you want your user to search through a combo box with that many items?

[cjbuechler]

Thanks Jzwp11, I was hopefull but it looks like that didn't change the limit in that list.

If I run the select statement in a SQL query it shows all records. Looks like it's only with Combo Boxes that I get that problem.

It's a list of contact records built by an old designer. I'm open to suggestions on a better way.

[jzwp11]

Doing some research, there is mention of people who have tens of thousands of records showing in a combo box. I could not find a specific limit for combo boxes on the Microsoft site. You show the query constructed in code but you only said that you tried the SELECT statement. There is a WHERE clause in the code that would restrict records did you include that?

[cjbuechler]

Thank you, I appreciate your help.

It also occurs to me that I should have mentioned the record set is from a linked SQL table.

[jzwp11]

Looking more closely at the code you provided, it is restricting the combo box.

This portion of the code, finds the location of the user but it does not appear to be referenced directly on the code that provides the data to the other combo box

'get data the user has rights to
LocationID.RowSource = "SELECT tblLocations.LocationID, tblLocations.LocName " _
& "FROM tblLocations INNER JOIN tblEmployeeLocations ON " _
& "tblLocations.LocationID = tblEmployeeLocations.LocationID " _
& "WHERE tblEmployeeLocations.EmployeeID = " & gsUserEmpID

The following portion of the code provides the row source to the cboIndividual combo box which I assume is the one that is limited but it doesn't use the location combo box above, but filters based on the user again (see red highlighted area). I'm also not sure about the right join (shown in blue); that may also be causing some records to be ignored but it is hard to say without a better understanding of your table structure.

cboIndividual.RowSource = "SELECT DISTINCT tblIndividual.IndividualID, " _
& "ISNULL(tblIndividual.LastName, '') " _
& "+ ', ' + ISNULL(tblIndividual.FirstName, '') " _
& "+ ' ' + ISNULL(tblIndividual.MiddleName, '') AS FullName " _
& "FROM tblEmployeeLocations INNER JOIN tblLocations ON " _
& "tblEmployeeLocations.LocationID = tblLocations.LocationID INNER JOIN " _
& "tblEmployees ON tblEmployeeLocations.EmployeeID = tblEmployees.EmployeeID " _
& "RIGHT OUTER JOIN tblIndividual ON tblLocations.LocationID = tblIndividual.LocationID " _
& "WHERE tblEmployees.User_Name = '" & gsUserName & "' " _
& "ORDER BY ISNULL(tblIndividual.LastName, '') " _
& "+ ', ' + ISNULL(tblIndividual.FirstName, '') " _
& "+ ' ' + ISNULL(tblIndividual.MiddleName, ''), " _
& "tblIndividual.IndividualID"

[cjbuechler]

Sorry, the LocationID.Rowsource is for another combo box used in another tab on this form and isn't related the the cboindividual one. Sorry, my fault for including that in the thread info.

My first thought was that something was wrong with the select but I dropped the select statement for cboindividual into a query analyzer and it displayed all the expected rows. The joins work fine otherwise and taking them out still produces the problem. It's always the last people in the list that don't display...We always know we have more than 1000 records when we start losing the Z's in the drop down.

[jzwp11]

I'm at a loss relative to the Access side; I'm not familiar with SQL Server enough to know if you can restrict that way. Sorry that I could not be of more help.

[cjbuechler]

Thanks though. I appreciate you taking a look at it.


The SQL seems fine from all my testing but the Access front end seems to be the issue. I'm thinking the original designer programatically set a limit on the box but I have now idea where.

[cjbuechler]

Figured it out and I need to appologize for incorrect info....the total records were 10000 not 1000.

Jzwp11, thanks for your help. You lead me to the options screen and on the Advanced tab there is a max records limit set. It was set to 10000. Once increased it allowed all the records to show.

[jzwp11]

I'm glad you got it worked out! Best of luck on your project.

[alexk_doxa]

Jzwp11, I would like also to thank you very much for your help!!!
I did a LOT of searching before find your solution. God bless you!

[jzwp11]

Glad we could help out, and welcome to the forum!