Full Version: Combo Box First (and Second) Row Blank, Cannot Sort Alphabetically
UtterAccess Forums > Microsoft® Access > Access Forms
thesolution
I have multiple combo boxes on a form that are all unbounded. 6 combo boxes in total. 3 of the combo boxes I use DISTINCT to hide any duplicates. The problem is that I think it may be causing the first row to be blank. And on one of the combo boxes, the first 2 rows are blank. The record sources for the 3 mentioned combo boxes are from one table with each of the controls referencing individual fields. Below is an example from one of the combo boxes:
!--c1-->
CODE
SELECT DISTINCT tblLCells.Facilitator FROM tblLCells;

So that was my first issue. The second issue involves one of the combo boxes that does not experience the issue mentioned above. I want this combo box to sort alphabetically but whenever I choose 'Ascending' in the Sort property, it seems to disappear when I close the query builder. I will say that I am attempting to sort by a column that is not the bounded column. This works in other combo boxes, I'm not sure why it's not working for this one.
Otried manually changing the SQL code by adding ORDER BY but again, it disappears after clicking OK. Any ideas?
thesolution
Nix the second issue, Access decided it would accept the change for once.
Doug Steele
Have you tried simply typing the SQL into the RowSource property, rather than going through the query builder?
See whether this works any better for you:
CODE
SELECT DISTINCT Facilitator FROM tblLCells WHERE Facilitator IS NOT NULL ORDER BY Facilitator
thesolution
Thanks for the code. I will say that your provided code works almost too much, as now a entry is selected on form load. I would still like the user to select an entry manually vs one being selected on load, which could cause problems.
Doug Steele
If an entry is being selected on load, either the combo box is bound to some field in the form's RecordSource, so that it's displaying the value of that field for the current row, or else there's code somewhere that selects a value in the combo box.
imply setting the combo box's RowSource will not cause a value to be selected otherwise.
thesolution
The combo box is unbounded and I double-checked the code for that particular form and could find nothing that would potentially select a value.
o I've been noticing if I just close and restart Access many of my issues go away. Your code works perfectly, thank you again Doug.
thesolution
The combo box with the 2 blank rows still has one blank row, which is kind of perplexing to me considering the code stated IS NOT NULL. I checked the table to see if any of the entries for that field were filled with a space but did not find any. I'm not even sure if that possible though. Any ideas?
Doug Steele
Possibilities are that it's a zero-length string ("") or that it's a space.
See whether this removes the row for you:
CODE
SELECT DISTINCT Facilitator FROM tblLCells WHERE Len(Trim(Facilitator & "")) > 0 ORDER BY Facilitator
thesolution
Worked pefectly, thank you Doug. I am curious though, why is it necessary to use Len with Trim?
Doug Steele
Len, as the name implies, give you the length of the string. However, Len(Null) actually gives you Null, so you can't just check Len([Facilitator]). To get around that, you can use Len([Facilitator] & ""), because & does not propagate Nulls (i.e. Null & "" is "", whereas Null + "" is still Null)
However, if Facilitator contains spaces (which I don't believe technically is ever should), you won't pick it up, since Len(" ") would return 1. Trim removes leading and trailing spaces, so Len(Trim(" ")) equals Len(Trim(" ")) equals 0, so now we have the ability to check whether a field is any one of Null, a zero-length string ("") or any number of spaces.
And given how VBA deals with strings, the Len function doesn't introduce much overhead.
thesolution
Thanks for the explanation, you're a bank of knowledge!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.