Ways around the "You tried to assign the Null value to a variable..."

[buratti]

I am geting the error "You tried to assign the Null value to a variable that is not a Variant data type" after deleting the data from a field. I kinda get why I am getting the error, but I need to find a way to avoid the user getting it. Also, no code behind it is trying to assign any value to any variable. I'll explain further how I am getting it:

I have an orders form, which the structure is extremely similar to the orders table from the Northwind sample database. The form is based on a query of my Customers table and Orders table. When the user enters the customers account number in the account number field in the orders table, it "auto populates" the rest of the customer data from the customers table. If the user, for whatever reason, deletes the account number, and then tries to switch fields, they get the error indicated above. I can understand that the reason I am getting this is because that is the field that is "linking" the customers and orders table, and an empty value would essentially break that link and "confuse" the database.

So my question is, how can I supress that warning? The reason why a user may delete the contents of that filed, generating the error, would be only when entering new records/orders. The user often may start a new order form, and enter the account number to only realize that the customer is not yet set up in the database (account numbers are generated from another software system and then set up in this database only when need be). If that is the case the user may sometimes delete the entered account number before clicking my "cancel" button, which would throw that error.

What I would ultimatelly like the behavior to be is that if the user "clears" the contents of that field, it undo's any data entry since the form was opened. Obviously I have tried to use undo commands in the after update event of the field, but this error is triggered before the after update event is triggered.

Any suggestions on how to aviod this error?

 

[Pat Hartman]

I would change the account number to a combo and set its limit to list property to yes. If you are using A2007 or newer, you have other properties that can be used to allow you to open a specific form, add a new record, and update the combo so when you return to the order entry form, the customer is now available in the dropdown.

 

[buratti]

Well to add more information to my original post, the field is actually already a combo box, the limit to list is set to Yes and I am using the NotInList event procedure to display a custom msgbox asking the user if they want to add the customer and if so then opens the appropiate form to add the customer. I just omitted that for simple explination of why a user might delete the contents of that field.

Regardless of why they would delete contents of the field, sometimes they just do and when they do I need a way to automatically Undo the deletion and revert the form back to the way it was when first opened, or at least create a custom warning that would give the user more detail as to what they are doing.

On an additional related note. I am getting a different error if I open the form in add mode, but do not enter any data and try to close the form (just as if canceling the addition of a record without entering anything yet). The message I get is "the microsoft access database engine could not find a record in the table 'Customers' with key matching filed(s) 'CustomerID'

 

[Pat Hartman]

The esc key clears the most recent field and returns it to its previous value and hitting esc a second time clears all changes to the current record.

If you want to trap nulls, the best place is the form's BeforeUpdate event. You can then give the user an appropriate message and cancel the update.

If Me.SomeField & "" = "" Then
    Msgbox "SomeField requires a value.", vbOKONLY
    Cancel = True
    Me.SomeField.SetFocus
    Exit Sub
End If