How can I make atextbox returns an empty string when nothing entered

[seaport]

I am using Access 2003 and SQL Server 2005. An Access form is bound to a linked table, which is linked to a SQL Server table. A text field in the text does not allow null value but accept empty string when nothing is entered, it also defaults to an empty string.

The form works most of time, but a key issue there is that the textbox in Access returns null value when nothing is entered. Every time when a user deletes the text from the textbox, I got an error, telling me that the null value is not allowed by that field. The error is from the database engine, not an VBA error. The error occurs even before the "textbox_beforeupdate" event. I tried several version of codes manipulating the key events and mouse events, but could not get a good solution because there are so many way to delete the text, like backspace, highlight and cut, highlight and backspace, etc.

I wonder whether I missed anything or there is a simple solution to this?

 

[TheAceMan1]

How are ya seaport . . .

[ol][li]In the table the control is bound to, set the [blue]Allow Zero Length[/blue] property to [blue]Yes[/blue].[/li]
[li]Now that you've made the change ... you need to reinherit the property change! You do this by setting the [blue]control soure[/blue] to any other field, then setting the [blue]control soure[/blue] to the actual field.[/li][/ol]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PWise]

TheAceMan:

I dont think that access saves the Allow Zero Length property For linked tables

 

[seaport]

pwise,

You are right. In the table design view, I got the red label warning "This property cannot be modified in linked table" for properties like "Required" or "Allow Zero Length". Actually, the "Allow Zero Length" was "Yes" for my table from the beginning. I think this is because the source table is a SQL Server table, which allows zero length string by default.

Even I change the property (just for testing), Access does not save the change.

So, back to my question, the table field does "allow zero length," and I still got the message "You tried to assign the Null value to a variable that is not a Variant data type."

Any other suggestions?

 

[seaport]

So it is clear that the problem lies in tables, not forms.

I made a test with two tables of exact same design. One table is local and the other is the linked table from SQL Server. Both table has one field that is not nullable but allows zero-length string. When editing data in table (inside Access), if I delete text from that field, the access local table automatically takes the zero-length string and there is no warning. But the SQL linked table shows the warning and I cannot bypass it.

 

[seaport]

I think I figured it out.

I can use the Change event of the textbox control. It fires before the warning appears.

 

[bhujanga]

If your current solution isn’t working, here’s something else you could try. Let's call the problem field "Some Text". Put an unbound text box on your form (and no text box bound to “Some Text”). When the record loads have it set the value in the unbound text box to the value in the "Some Text" field. Since the unbound field has to connection to the data you can put whatever you want in there. When you go to another record or close the form, another segment of code can be activated that sets "Some Text" to the value in this unbound text box. Perhaps at this point you'll get the error message but maybe it will work. Just a thought.

 

[seaport]

Thanks, bhujanga.
You are right that unbound controls give us the complete control. I used them in a couple of my Access projects. But for some small and timepressing projects, using bound controls/forms are certainly quicker and easier, which is the primary reason for me to use Access.