Re-Insert Null value into database field

  • Thread starter Thread starter Greyhound
  • Start date Start date
G

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?
 
What is the code that you are using to accomplish this function? It may help
us determine the cause of the problem
 
Greyhound said:
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?
Click to expand...


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.
 
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.
 
Back
Top