How to clear a text box?

[Guest]

Dear All,

I have an Access application which could be run on Access or SQL Server as a
backend. I need to clear a value of a text box, so I set Me.txtStudentID =
Null. That is fine in Access but it generate an error in SQL Server, try to
assign a Null to a variable not variant type. I change the code to
Me.txtStudentID = "". This time it works fine in SQL Server, but it generates
an error in Access, "Field 'STUDENT.NAME' cannot be a zero-length string."
I do not know which value I should set to that text box so it will be OK in
Access and SQL Server. Please, give me some advice.

Thank you.

 

[Nikos Yannacopoulos]

This is not a question of the "correct code" to use; it has to do with
the differences in the table definition between the two systems. In
Access, you have left the Required property for the field to No
(default), so it accepts Null, while you have set Allow Zero Length to
No also, so "" (zero length string) is not accepted. On the SQL Server
table, on the other side, the default setting for Null is "not
accepted". This is why you are experiencing this behaviour.
My advice is: based on the logic of your app, decide whether Null is
accepted or not, and make the required change in the table design on
either side, so both sides behave alike.

HTH,
Nikos

 

[Guest]

Thanks Nikos.

That is what I want. Since that field is a primary key, I cannot set the
field to Null in SQL Server. Therefore, I set Allow Zero Length to Yes in
Access.

You are wonderfull.

 

[Nikos Yannacopoulos]

PK? This is different, zero-length strings are not much better than
nulls for a PK!
Also, names are a bad choice for PK fields, for several reasons (often
discussed in the NG's). For instance, what guarantee is there that you
will only ever have one John Smith? Or, what Guarantee is the that you,
or any other user, will never misspell my surname (trust me, it happens
more often than not!). It is advised that you use a PK of guaranteed
uniqueness; for instance, in the US the Social Security Number is a
common choice, when known. If nothing of the sort is available, then an
autonumber field is a good choice. Either will not only guarantee
uniqueness, but will also remove the source of your current problem,
rather than circumvent it.

HTH,
Nikos