ComboBox Limitation?

misterqj · July 31st, 2003, 05:59 PM

I am working in Access 2000, and I have a combobox that I build with the following query:

Code:

SELECT MemberList.MemberID, [MemberList]![FirstName] & " " & [MemberList]![Middle] & " " & [MemberList]![LastName] AS Expr1
FROM MemberList
ORDER BY MemberList.LastName;

If I run this query by itself, I get all 215 records I expect, but on the form I get a combobox with varying numbers of entries each time the form is opened. I searched MSDN and couldn't find any notes that seemed to point me in the right direction, so I am hoping someone with a little more experience can point me in the right direction.

Thanks much,
Quint

WmFay · July 31st, 2003, 08:07 PM

Hmm, If you open the combobox and start typing the value for the last entry in your list, does it go to the bottom of the list? Suspect what you are reporting is normal default behavior for large lists.

When a recordset returns a large list for a list or combo-box, it has to scroll. If you move the slider to the bottom, you move a ways down the list, but not to the bottom. On my machine almost immediately, the slider gets smaller and moves to half way down the list. Repeating this, you eventually get to the bottom. As a test I used Northwind.mdb Orders (830 rows) and query:

SELECT Orders.OrderID, Trim([Orders].[ShipName]) & ", " & [Orders].[ShipAddress] AS Expr1
FROM Orders
ORDER BY Trim([Orders].[ShipName]) & ", " & [Orders].[ShipAddress];

I get 7 rows of "Wolski Zajazd, ul. Filtrowa 68" after 4 tries. If you get something else for this example, then it might be your equipment. If you other example doesn't work, then you have a problem. It should work.

misterqj · July 31st, 2003, 08:15 PM

I can make those kinds of mistakes sometimes - but in this case I double checked. I dragged the scroll bar all the way to the bottom, and AutoComplete didn't take me any deeper in the list.

It is finally working, but I don't know why. I looked at every setting on the combobox, and saw that Column Count was 4, but I made some changes to the combobox, and now I have only 2 columns. When I changed the Column Count to 2, all the entries showed.

That is the only change I made, and it is working now.

Thanks much,
Quint

WmFay · August 1st, 2003, 06:04 PM

Guess I won't ask what the fourth column was. Doesn't matter since that's where I was going next. Have run into those. I would think you get into undefined territory when the combobox references a number of columns different from your query. Just imagine the poor guy writing the code to map the results of a query to a combobox. If his bosses want the code done quickly so they can meet a release deadline, robustness is going to suffer. Especially if he has to rewrite it from scratch for a new technology.

If this were open source, you could find out exactly why it was failing and suggest ways to have soft failure. BUT we have the benefit of PROPRIETARY! As one of my mentors once said, "If it (the manual) doesn't explicitly say you can do that, why would you ever expect it to work? Don't ever code so that vendors can egg you."

Look at the bright side, you didn't have to write a response function accessing a query! Have fun! Just stay out of the puddles. (;))

misterqj · August 2nd, 2003, 05:57 PM

Originally, the first, middle, and last names were in separate colums in the combo box, with the last column being the id. When I figured out how to combine the names in one column, I never changed the specs of the combo box to only 2 columns.

Like you said, I never would have been able to build a trail to this conclusion, it was just a lucky bit of poking around that solved my problem.

I am finding this to be a very helpful forum. I was concerned, having been involved in mostly Perl/*nix programming, that it would be hard to find a community like http://perlmonks.org to provide guidance. This forum is becoming that community for me.

Thanks,
Quint