Select a Null value in Unbound Combobox

[Kowalski]

Hi
Using Access 2010.
I've got an unbound combobox with a default value, but I still would like users to enter a Null value. I've got the Null value in the list of drop down items, but when it is selected, the combox value does not go to null, but instead goes to the default value.
Any easy way to allow null values to be selected? I know it was possible with older versions of Access, but not any more.

 

[vbaInet]

For new records it will default to the Default Value.

The selections available are based on what is in the row source of the combo box. If you want a Null selection then it must exist in the row source. But note, you can always delete what's in the combo box to set it to Null.

To have a Null selection you will do something like this:

SELECT [SomeField] 
FROM Table
UNION
SELECT [COLOR=Red]Null[/COLOR]
FROM Table
ORDER BY [SomeField]

 

[Kowalski]

Hi
Thanks, but deleting what is in the combobox does not work. It works for bound fields, but if the field is unbound, then the default value appears when you delete the value.
It worked on Access 97 - 2003, not sure about 2007, but not in 2010.

 

[vbaInet]

Hi
Thanks, but deleting what is in the combobox does not work. It works for bound fields, but if the field is unbound, then the default value appears when you delete the value.

When you delete the value of combo and do what? Move to a different control on as soon as it's cleared?

What about the SQL provided?

 

[Kowalski]

It happens when the focus moves away from the control.
Even with the SQL provided.

 

[Galaxiom]

You will need to clear the default value property and insert it using VBA instead.

 

[Kowalski]

Yeah thanks. Not really the ideal solution, but it seems that some work around should be followed. Strange that Microsoft has changed this behaviour.
Thanks for the replies.

 

[Galaxiom]

I expect you have a field default in the table. A field default will be used even if you enter a null in the bound control.

However a control default can be cleared.

 

[Kowalski]

Remember it is only on unbound fields that the problem appears, so it's not bound to a table or a field with a default value. Unbound control on an unbound form. Should have a default, but the user should be able to remove it.

 

[Galaxiom]

I can clear the value inserted from the default property in an unbound combo either by deleting the value or selecting a Null value from the list.

(Access 2007) Yes odd if they have changed this. I would call it a bug.

 

[Kowalski]

2 work around solutions:
1. If you remove the default value in the OnChange event, the Null value stays put.
2. Don't use default values via the Properties, but assign in code in the Form's OnLoad event.
Me.[the unbound control] = <your default value>

Both are a hassle but at least is will work.