troubleshooting Question

Need to be able to erase data from a bound textbox that does not allow nulls but allows empty strings

Avatar of valencehealth
valencehealth asked on
Microsoft Access
16 Comments1 Solution326 ViewsLast Modified:
I have a form with a bunch of bound textboxes to a table in my database.  Some of the fields in the database are text that do not allow nulls but allow empty strings (I want to keep it this way as it is much easier to deal with empty strings than nulls).  However, if the user trys erasing the value in one of these textboxes they get an error saying it can't be null.  So I created AfterUpdate events with the code that looks like the following:

  Me.txtBankName.Value = Nz(Me.txtBankName.Value, "")

However, this doesn't work because it is being called after you update and the error is occuring when you update so this code isn't getting called when you try erasing the value.  So then I got the idea of making it a BeforeUpdate but that gave me the following error:

Run-time error 2115: The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing D.S.A. Factors from saving the data in the field.

I know that I can create hidden fields that are bound to the table and then have these visible fields be unbound but have them set the bound fields in the AfterUpdate, but I really don't want to do that as it will make it very difficult to maintain.  Does anyone know how to solve this problem?


DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Database Architect / Application Developer
Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros