Changes to records reverted when updating a textbox programatically

[Lhoj]

I have one form to edit the records of a single table. Everything on it is working as expected except for one text field which is reverting the changes done to the record when exiting the form when I empty it through VBA code, not when I manually delete its content on the form, though.

This is the setup. The form has 25-30 fields of various types (mostly text boxes, check boxes, and a couple of attachment fields) each corresponding to a field on the table. They can be considered as 4 groups of fields, that I wanted to activate or deactivate through 4 main checkboxes. If the main checkbox is activated, all the controls corresponding to that 'category' are enabled. If it is unchecked, all controls are disabled. For commodity, I want to reset the value of all those controls, so if the user unchecks the main checkbox and some related fields have data it warns the user and, upon confirmation, it erases the data and disables the controls.

This is working for every field on the form except for a simple textbox. The textbox is exactly the same as some others who are working OK. It's just a textbox with an input mask and a validation rule to ensure it has a certain fixed length. The way I go to delete the data and disable the textboxes is the following:

Me.textbox = ""
Me.textbox.Enabled = False

The main checkbox for this particular textbox handles another 2 identical textboxes, three checkboxes and two attachment fields. If I comment the two lines for the problematic textbox, everything updates right. If I leave them on, controls lose their value and are disabled, but upon exiting the form all changes done are rolled back and reverted to their previous value. Deleting the textbox's content from the form manually doesn't cause this error upon exiting.

I've gone through the code line by line and no error is thrown (neither when updating nor when exiting the form). The field has an after update macro to keep a history of past values, but so does every other field and they are not causing this behaviour.

Any general idea what might be the problem? Because I'm at a total loss.

Thank you so much!

[Minty]

I suspect its the input mask and or validation rule blocking the change.
If it's bound to a field, try setting it to Null instead of "" assuming your field allows nulls which it sounds like it must do if its optional.

Be aware that "" is also know as a zero length string or ZLS and is not the same as a null.

[orange]

Can you post:
-the validation rule
-input mask
-sample data that is "reverting" unintended

??does the after update data macro show the value has changed??

[Lhoj]

Hi, Minty and Orange,

I think I stumbled upon the problem while trying to set a more contained test enviroment for you guys to test in right after posting this thread, and you are definitely right. Thing is those fields don't allow duplicates, but all the records have data on most of those fields, so there was no problem. Testing in a new enviroment with barely no data on the table revealed the fact that more fields are causing this (more precisely, those with no duplicates allowed). So I'm guessing setting them to null instead of blank will fix it. I have a meeting right now but I'll try that asap and update wit the results.

Thank you both!

[Lhoj]

So yeah, that fixed it. I don't know why it didn't warn me that the changes couldn't be made because it would create duplicates and whatnot, nor why emptying the textbox by hand didn't cause the problem (isn't that the same as setting it to ""?), but at least I can go on. Next time I'll start by testing in a smaller environment, it will save me SO much trouble...

Thanks again!

[Micron]

isn't that the same as setting it to ""

It is not. For lack of a better explanation, an empty string (or zero length string) is something - you just can't see it. Deleting the control value sets it to Null. You cannot see Null values either, nor are they the same as "". Null is considered "unknown" which is not the same as "Nothing" either. Clear as mud?

[Lhoj]

Well, I'd never have figured that emptying a textbox would set it to null. Considering a blank (or no text) is a thing, that's the most logic thing to set the value to after emptying a textbox. In my head null has always been the equivalent to void, or not set or initialized or a similar state. Good to know what those fields' values are set to when emptied.

I'm guessing then the only way to set its value to blank (or "") is through code?

[Micron]

As far as I know, that or sql are the only ways. Not something I'd ordinarily do though.