CODEWITHSUNDEEP
ms-accessvba
Alina B.
Alina B.

Reputation: 1276

ComboBox empty/null throws error. How to "reset" field?

I have a combobox in my form with a table-filled list as rowsource. When the user enters the field, changes something and delete it so that the combobox is empty I get the error "You tried to assign a null value to a variable that is not a variant data type".

The problem is that my combobox does not allow an empty string but i also cannot find an event in the combobox that fires before the error. So I cannot use Len or IsNull to catch the error. Currently I use this "work around" but it is non-specific as it uses the error event of the form not of the combobox:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Me.myField.Undo
    Response = acDataErrContinue
End Sub

Is there a better way to handle this issue?

I found this. But that did not solve the problem.

Upvotes: 1

Views: 2431

Answers (2)

Whitekn3
Whitekn3

Reputation: 77

It's an undocumented Microsoft "Feature" designed to drive you crazy. And the people who tell you "look in your code", "somewhere in your code" don't know what they are talking about.

Two workarounds. Change the underlying field in the table to allow nulls, OR, and I think this is the best way, change your combo to an unbound control and set the underlying field in the AfterUpdate event.

Upvotes: 4

user2070039
user2070039

Reputation:

As Remou indicated in the comments, the error is in response to what you are doing after a new value is entered into the combobox. Check the combobox events. If you don't mind about the type and want to use null values, you can change your variable to a Variant type. If you are using an actual data type such as string or integer then use the NZ() function to screen out nulls.

For example, if you want to assign an empty string to a string variable if the combobox is null then you can do:

dim s as string
s = nz(me.combobox,"")

As for firing before the error - somewhere in your code you already have the offending code that is causing the error. You need to locate that. More information will be required from you in order to provide a more detailed and specific answer.

Upvotes: 3

Related Questions