Re-Insert Null value into database field

[Greyhound]

I have a form created in Access 2003. It is populted with a query. The
first textbox holds an 'ItemNumber'. ItemNumber's datatype in the database
is a Long. This field is not populated when a record is first entered into
the database, therefore is Null initially. The user enters item numbers at a
later time. When an item number is entered, I check to see if there is
already a item number matching the one that is entered. If that item number
has already been used, I tell the user that the number has already been used
and I set the textbox to an empty string. That is when the problem begins.
I get an error that states 'the value you entered isn't valid for this
field.'. How can I set the value of this field in the database back to Null?

 

[scott04]

What is the code that you are using to accomplish this function? It may help
us determine the cause of the problem

 

[Dirk Goldgar]

I have a form created in Access 2003. It is populted with a query. The
first textbox holds an 'ItemNumber'. ItemNumber's datatype in the
database
is a Long. This field is not populated when a record is first entered
into
the database, therefore is Null initially. The user enters item numbers
at a
later time. When an item number is entered, I check to see if there is
already a item number matching the one that is entered. If that item
number
has already been used, I tell the user that the number has already been
used
and I set the textbox to an empty string. That is when the problem
begins.
I get an error that states 'the value you entered isn't valid for this
field.'. How can I set the value of this field in the database back to
Null?

Don't do this:

Me.txtItemNumber = ""

Do this:

Me.txtItemNumber = Null

But if you are making your test for previously used numbers in the text
box's BeforeUpdate event, just cancel the event. If you are using a VBA
event procedure, set the procedure's Cancel argument to True.

 

[Greyhound]

Thanks for the reply. I tried Me.ItemNumber.Text = "" and I tried
Me.ItemNumber.Text = Null, both threw errors. I didn't solve the situation,
but I did convince the user to allow me to use zero as a default in the field
of the database so I set Me.ItemNumber.Text = 0 and all is good.