Allowing selection of null from combobox

[bd528]

Hi,

I have a combobox, and when it's form opens, it is set to null. If the user selects a value from the combobox, perhaps in error, I can't figure out how to allow the value to be deleted. Each time the value is deleted, and the user tries to set focus to the next control, they are presented with the message "The text you entered isn't an item in the list.

The rowsource for the combobox is :-

SELECT DISTINCT [tblBrokerages].[ID],[tblBrokerages].[Brokerage_Name] FROM tblBrokerages ORDER BY [Brokerage_Name];

Can anyone advise on how to allow the user to delete the value from this combobox and leave it empty.

Thanks in advance

 

[MarkK]

See if this works...

SELECT t.* FROM (
   SELECT Null As ID, "" As Brokerage_Name 
   FROM tblBrokerages
   UNION
   SELECT DISTINCT ID, Brokerage_Name 
   FROM tblBrokerages ) As t
ORDER BY t.Brokerage_Name;

 

[bd528]

See if this works...

SELECT t.* FROM (
   SELECT Null As ID, "" As Brokerage_Name 
   FROM tblBrokerages
   UNION
   SELECT DISTINCT ID, Brokerage_Name 
   FROM tblBrokerages ) As t
ORDER BY t.Brokerage_Name;

Thanks for your suggestion. Unfortunately, this didn't create a null value to pick from, and the user is still unable to remove a previously selected entry.

 

[smig]

Can you explain what you are trying to get with this combo ?

 

[bd528]

Can you explain what you are trying to get with this combo ?

The value associated with this combo box isn't required to complete the form. But if someone selects a value, and then decides that there shouldn't be one, I'd like the option to remove the selected value from the combobox.

I've gotten round this currently by having a button that clears the value, but being able to delete the value would be preferable.

 

[Grumm]

So if the user select a value that is in the combobox, sometimes he will get the message : "The text you entered isn't an item in the list."

So if I am correct you ask the user to select a text from a pre-defined list. If the user selects one you tell him that it isn't an item of the list ?

Are you sure a combobox is the best control you need for what you try to do ?

 

[bd528]

So if the user select a value that is in the combobox, sometimes he will get the message : "The text you entered isn't an item in the list."

So if I am correct you ask the user to select a text from a pre-defined list. If the user selects one you tell him that it isn't an item of the list ?

Are you sure a combobox is the best control you need for what you try to do ?

Not quite. The list is pre-defined. To have a completed form, the user either has to have a null value, or a option from the combobox. There are no issues with the items on the combobox. I would just like the facility to delete the value if it is picked by mistake. Currently, if the user deletes the value and tries to move to another control, they get the message "The text you entered isn't an item in the list."

 

[static]

To clear a combo you should only need to press the Escape key or delete the value.
If that is not the case, you will need to supply a copy of the database for somebody to look at.

 

[bd528]

To clear a combo you should only need to press the Escape key or delete the value.
If that is not the case, you will need to supply a copy of the database for somebody to look at.

I'm fairly sure, as I've explained the situation, someone may have a solution without having to post the database, but you may be right.

 

[Grumm]

Maybe you can use the event NotInList ?
https://msdn.microsoft.com/en-us/library/office/ff845736.aspx

Something like this :http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=64608&stc=1&d=1480606660

If you open the form and enter a random text you will get a nice message telling you it is not possible and it will undo the handling the user just did.

 

[static]

I'm fairly sure, as I've explained the situation, someone may have a solution without having to post the database, but you may be right.

Maybe someone will have solution. How long do you want to wait?

Yes, you've explained the situation, but you've given zero details other than a bit of SQL, which has nothing to do with the error.

Is the combo bound to a field? If so does the field require a value?
Is there an Input Mask.
Does your form contain code that could change the value without the user knowing?
...

 

[bd528]

Maybe someone will have solution. How long do you want to wait?

Yes, you've explained the situation, but you've given zero details other than a bit of SQL, which has nothing to do with the error.

Is the combo bound to a field? If so does the field require a value?
Is there an Input Mask.
Does your form contain code that could change the value without the user knowing?
...

The combobox is unbound
There is no input mask
There is no code to change the value

I feel the SQL was worth posting as it's the source of the combobox, and I stick by that fact.

 

[static]

Ok, if that's all we have to play with, what's with the DISTINCT? Non unique ID?

 

[MarkK]

I'm fairly sure, as I've explained the situation, someone may have a solution without having to post the database, but you may be right.

I have created an unbound combo with no input mask, no associated code, and deleting the value in the control nulls the control. I cannot reproduce your problem or replicate your claim, and therefore cannot troubleshoot it.

To me, as with all extraordinary claims, the "burden of proof" lies with the party making the claim.

 

[bd528]

I have created an unbound combo with no input mask, no associated code, and deleting the value in the control nulls the control. I cannot reproduce your problem or replicate your claim, and therefore cannot troubleshoot it.

To me, as with all extraordinary claims, the "burden of proof" lies with the party making the claim.

It's not really an extraordinary claim. I posted my original question, answered the following questions. That's all there is to it. I'm happy to answer anything else, but I feel no need or inclination to prove anything.

 

[static]

You felt a need or inclination to get help with a problem.

If you don't care enough to provide information without being asked, don't expect people to care enough to try and help you.

The only proof you need to provide = Delete the data if need be and upload the file.

 

[Minty]

Try putting a command button on your form - call it cmdReset - in click event put the code

Me.YourCombox = Null

If that doesn't work there is something very strange going on. Possibly the control is corrupt. Try deleting it and starting again.

 

[Frothingslosh]

Out of curiosity, how do you have 'Limit to List' set?

 

[bd528]

You felt a need or inclination to get help with a problem.

Thats right. I'm not sure what point you're making here, but anyway...

Maybe you can use the event NotInList ?
https://msdn.microsoft.com/en-us/library/office/ff845736.aspx

Something like this :http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=64608&stc=1&d=1480606660

If you open the form and enter a random text you will get a nice message telling you it is not possible and it will undo the handling the user just did.

Sorry, I missed this post. The example works great, and is what I will use going forward.

Try putting a command button on your form - call it cmdReset - in click event put the code

Me.YourCombox = Null

If that doesn't work there is something very strange going on. Possibly the control is corrupt. Try deleting it and starting again.

I was using the button approach previously as a work around, and that worked. I followed your suggestion and deleted the control. Using Grumm's help, I now have an answer to my question. Thank you both.

 

[Isaac]

How about just adding the word "blank" or "null" to the combobox, and then adding the appropriate code in the AfterUpdate even of the combobox, adjusting the recordsource to suit? That would be one way to approach it, but then again, I'm not sure what your current combobox code is that filters. I don't usually apply an actual Filter to a form, I tend to set the recordsource instead.