Default Values

[Dan McClelland]

SQL Server 2000 back end and Access XP adp front end.

I have nvarchar fields with Allow Nulls set to false. In
other words, they are required fields. In Enterprise
Manager, opening the table and entering data directly,
these fields start out with null values and I MUST enter
values before attempting to save the record and insert it.

On my bound Access form, these required fields begin with
values of empty strings (""), so the insert happens with
no errors, even though the fields contain no real data.
There is nothing in the control's DefaultValue property
and no reason that an empty string should exist as the
field's initial value.

This doesn't make ANY sense to me. Any ideas?

 

[Tim Ferguson]

On my bound Access form, these required fields begin with
values of empty strings (""), so the insert happens with
no errors, even though the fields contain no real data.
There is nothing in the control's DefaultValue property
and no reason that an empty string should exist as the
field's initial value.

This doesn't make ANY sense to me. Any ideas?

Access does do some strange things with DefaultValues -- my main bugbear is
the fact that Integer fields end up with a Zero default.

You could try entering NULL into the defaultvalue in table design mode,
which should guarantee that your INSERT commands will fail unless you
remember all the required fields. Alternatively, you could use a realistic
default value.

B Wishes


Tim F

 

[Dan McClelland]

Thanks Tim, but setting a field's DefaultValue to Null in
table design doesn't appear to affect the value of the
textbox control bound to that field on my form. It is
still being initialized as an empty string.

Seems to me, with the importance of null values and
handling them properly, that this is a MAJOR data
integrity hole between Access and SQL Server. Looks like
I'll just have to add pages and pages of code to handle it.

 

[Tim Ferguson]

Seems to me, with the importance of null values and
handling them properly, that this is a MAJOR data
integrity hole between Access and SQL Server. Looks like
I'll just have to add pages and pages of code to handle it.

Hold on: something is odd here. I was under the impression that SQL server
did not accept ZLS but converted them all to nulls. A little bit of
experimentation shows that to be wrong.

On the other hand, classically an empty Access textbox does return Null,
rather than a ZLS (the opposite from Visual Basic itself) so that the value
can parse into a date field, for example, which a ZLS wouldn't.

Actually, the more I look at this the more confused I am getting. A new
record on an Access from is all nulls; as soon as one field is initialised,
the (autonumber) goes blank and the Required field textbox becomes ZLS,
while a non-required varchar textbox remains null. Now if I try to save the
record, I get an Required Field error.

Go figure. I am not an adp wizard: you may get more sense over in
m.p.a.adpsqlserver where I am sure they have seen this before.

Best of luck

Tim F

 

[TC]

I've already answered this under your other post. >>Textbox controls are not
variants. They are text items. They do not support Null<<

TC

 

[Rick Brandt]

I've already answered this under your other post. >>Textbox controls are not
variants. They are text items. They do not support Null<<

TC

Huh? Are you saying that a TextBox on a form cannot hold a Null value? They
certainly can. TextBoxes have no data *type* like a variable does or if they do
it must be Variant.

When moving to a new record and examining a TextBox with no default value it
will be Null. You can also set a TextBox to Null with...

Me!TexBoxName = Null

 

[TC]

Huh? Are you saying that a TextBox on a form cannot hold a Null value? They
certainly can. TextBoxes have no data *type* like a variable does or if they do
it must be Variant.

When moving to a new record and examining a TextBox with no default value it
will be Null. You can also set a TextBox to Null with...

Me!TexBoxName = Null

Er - I must be on Xmas drugs!!! Where did all that nonsense come from? I
must have been thinking of the .Text property. Sorry for any confusion to
readers ... :-(

TC