Full Version: Combo Box - don't show duplicates and have first row blank
UtterAccess Forums > Microsoft® Access > Access Forms
valdous
Hi there,
I have a form (ViewForm) with a combo box on it. I need to populate the combo box with the contents of a field (EstateName) from the Receipts table, without showing duplicates, and with a blank first row.
I had set the rowsource to the following and since there is the possibility of the field being blank, I didn't want those to appear. But, it starts at the first populated record but I would prefer it be blank.
SELECT Receipts.EstateName
FROM Receipts
WHERE (((Receipts.EstateName)<>""))
Orderbr /> have a form (ViewForm) with a combo box on it. I need to populate the combo box with the contents of a field (EstateName) from the Receipts table, without showing duplicates, and with a blank first row.
I had set the rowsource to the following and since there is the possibility of the field being blank, I didn't want those to appear. But, it starts at the first populated record but I would prefer it be blank.
SELECT Receipts.EstateName
FROM Receipts
WHERE (((Receipts.EstateName)<>""))
ORDER BY Receipts.EstateName;
Also, I don't want the field to be editable. I had set the Limit to List propoerty to Yes. However, I found that I can edit the field (change it a field that already exists in the table). What am I missing there?
Thanks!
ScottGem
The answer:
SELECT DISTINCT Receipts.EstateName
FROM Receipts
Order:
SELECT DISTINCT Receipts.EstateName
FROM Receipts
ORDER BY Receipts.EstateName;
That should show not duplicated names including a blank first row.
But I'm kinda of confused as to why you are doing this. If you don't want to edit the value then why use a combobox in this instance? Doesn't make any sense.
valdous
The form basically prompts the user to select an estate then print a report based on that info. I wanted to avoid having them type it in manually and the list includes only existing estate names... Is there a better way?
tried the code you suggested above and now, when I first open the form, it displays the contents of first record, but when I dro pdown, I do see the blank row, although I am unable to select anything in the list...
valdous
Ignore the part about the not able to select. During my testing, I had set the form's "Allow edits" to No. It is back to yes and works fine. I also resolved the blank row thing. So I guess my only issue is how to have the user select but not change anything in the combo box...
fkegley
Well, one way would be this:
On the KeyPress event of the combo box, this code:
KeyAscii = 0
That would cause the ASCII code of any key press in the combo box to become 0, which essentially cancels the key press.
aaraccess
That is such good advice. I've gotten around this issue by pointing box to query and sorting and grouping. Your method is better.
valdous
This seems like exactly what I want except that I get an error. I inserted the exact code as you provided above in the KeyPress event. When I go to test it and type in a letter in the combo box field, I getthe following error:
Can't find the macro 'KeyAscii = 0'
fkegley
You didn't do it right. Open the form in design view, get the properties of the combo box. On the Event page, click the On Key Press row, then the dropdown at the right end. Choose [Event Procedure] from the list that appears, then click the ... at the right end of the row. The code window should open, NOW type in the line of code that I sent you earlier:
eyAscii = 0
valdous
:-)
That did it. That's a great tool.
One more question:
Any idea why the combo box remembers the last value selected? Is there a way to set it back to the blank entry on close?
fkegley
Me.ComboBoxName = NULL
valdous
In the On Close event, I have the following as a procedure:
Private Sub Form_Close()
Me.BoxEstateName = Null
End Sub
When I close the form, I get the following run time error:
You can't assign a value to this object.
What am I missing this time? :-(
ScottGem
I'm still confused here. If I understand you, you are using this combo to set criteria for a report. In that case, all you need is to set the Limit To List to yes and make the control unbound. Either standing alone will not allow the user to change the list since the Unbound control isn't saved anywhere or using the LimitToLIst will prevent entering a value not on the list.
valdous
I had it bound. I changed it to unbound and it is working perfectly!
Thanks!
ScottGem
Glad to asisst
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.