Combobox with "Any" as 1st/null value?

[flemmo]

Hi

I have a combobox with a list of values fed from a table. Simply ID and Name, ID hidden and Name visible.

I need to add the word "Any" as the first option, before the list of values from the table.
Access usually has the 1st row as an empty/null field with no value. I could either add the word "Any" to this row, or insert a new one perhaps?

The end goal is to take the ID from the selection to a report. If the user has selected "Any", then no filtering will take place on the report.

 

[DCrake]

There is an Access solution out there, but you could just put a message on the control tip text that says "Leave blank for all records" Then only apply the filter if a selection has been made.

 

[Trevor G]

You can use the On Current Event to set the value to "Any" change the cboAdvisor name to your combo box name

Private Sub Form_Current()
Me.cboAdvisor.Value = "Any"
End Sub

 

[flemmo]

Thanks for the suggestions.
Trevor, I tried your suggestion. It works on a text field but not on my combobox. Perhaps I need to change the settings of the combo?

 

[Trevor G]

Take a look at an example here. It works fine.

 

[flemmo]

Thanks Trevor.
Comparing your example to mine, I cant see why mine isn't playing ball... I'll keep trying.

 

[Trevor G]

Can you upload an extract of your database with a sample table and sample form, I can then look at it for you.

 

[flemmo]

Got it working.
I changed the width of the id column from 0cm to 1cm, viewed the form and it displayed "Any". Then changed that column back to 0cm and "Any" is still showing

Thanks again Trevor

 

[Trevor G]

Excellent News, well done.

 

[flemmo]

Oh no! Now I've saved the database and reopened, "Any" has gone again
It looks like its going to the 0cm column. I've created a new textfield and set it to equal the value of the combo and it says "Any", so I know the value of the combo is being set, but I cant see it because the column is 0cm wide.

If needed I'll try and create a copy for my form with some dummy data and upload it.

 

[flemmo]

Think I've found a solution - just to select a new list item in the combo as follows:

SELECT [Categories].[CategoryID], [Categories].[CategoryName] FROM [Categories]  ORDER BY [CategoryName] ASC;
UNION Select "0", "All Categories" FROM [Categories]  ORDER BY [CategoryName] ASC;