Indeed, control fldver has a blank "Control Source" parameter. It is an unbound field.
I think that settles starting the rebuild of this misbehaving form.
A2007 Some Combo Boxes changing back color on their own as they loose focus, WHY? (1 Viewer)
- Thread starter mdlueck
- Start date
Indeed, control fldver has a blank "Control Source" parameter. It is an unbound field.
I think that settles starting the rebuild of this misbehaving form.
If it's not returning 0 then it's not Null, it's a zero-length string.
PotAto / PotOto, in my mind those two are the same.
Using Nz to extract values from field controls... a field control having had nothing populated to it, then have its "un-value" extracted from it, Nz should properly detect the "un-value" and select the default value instead.
All right, then if using Nz is not enough protection, then any suggestions what would be? And why is this a unique case / why have I encountered such nonsense elsewhere already? I have been successfully using Nz to provide default values for fields not filled in all over in Access applications... even at times having "" be the default value. In this case it is a numeric datatype, so 0 was to be the default.
I think I remember seeing some code of yours where you were intialising some controls with "" which is the zero-length string. If that value ever gets saved Nz() won't be of any use. Null isn't a value whereas zls is a value so they're not the same. That's the short explanation, there's a much longer explanation re the differences.
So you have three options:
* Set the Allow Zero-Length String property of the Text fields to No. This will ensure that it will never contain zls and everytime you validate using Nz() it will always pass.
* If you don't want to change that property then you can use something like this to check for both Null and zls:
By the way, vbNullString is the constant for "".
* If I was right about you initialising your controls with "" then I would advise you stop doing that and convert your Nulls to "" with Nz() alone. You will need to UPDATE all your fields to SET all zls to Null.
So you have three options:
* Set the Allow Zero-Length String property of the Text fields to No. This will ensure that it will never contain zls and everytime you validate using Nz() it will always pass.
* If you don't want to change that property then you can use something like this to check for both Null and zls:
Code:
If Len(Nz(Me.Textbox, vbNullString)) = 0 Then* If I was right about you initialising your controls with "" then I would advise you stop doing that and convert your Nulls to "" with Nz() alone. You will need to UPDATE all your fields to SET all zls to Null.
I am wrestling to see the difference VBA is imposing.
For string values, yes I have been reading controls with Nz(fldcontrol, "") and if it is a numeric field then either Nz(fldcontrol, 0) or Nz(fldcontrol, -1) depending on the code and if I can use 0/-1 as a null indicator value. (In this case, I should have been using -1 as a null indicator value, but that is besides the point since Nz is not working as needed.)
So in this case there was a true NULL in the database, thus that NULL got converted to "" by Nz when the record was populated into the Edit screen. Nothing was changed in that field, record was attempted to be committed, I was expecting Nz to realize that the field was still blank so select the default value instead. Nz did not work that way, thus the code blew up.
A long time ago I seem to recall a similar crash on a record ADD screen before I started using Nz() to read the field control values. I would bring up the ADD screen, try to COMMIT a blank form. BANG! Using Nz() solved that trouble, and I have been doing so ever since... until now. That a field which has never had anything typed into it has a different NULL value than a control which has had a character entered into it, then deleted. I guess I am failing to understand... "'tis better to have loved and lost than to have never loved at all."
Thank you for your assistance, vbaInet... MUCH appreciated.
All right, I will accept that point in VBA context.
What should I be initializing blank/empty/NULL value field controls with if not "" so that Nz() will NOT fail when I attempt to read the value of the control?
There's a big difference. A field of any data type is initialised as Null. Numeric fields cannot be set to zls but can be set to Null. Null is the initialised "value". I put value in quotes because Null isn't really a value. It points to nothing, no memory location, nothing. Have a read of this from pbaldy:
http://baldyweb.com/NullEmptyEtc.htm
You can initialise your Numeric fields to zero if you wish but leave the Text fields as they are. The problem you're having is that you're seeing fields as if they are variables. Fields do not need to be initialised any further.
Another thing to note is that "" occupies space whereas Null doesn't. So why use up space by initialising a field with a value when you can leave it initialised and not use up any space? Think about that.
Allen Browne suggests the syntax I am using...
"Common Errors with Null"
http://allenbrowne.com/casu-12.html
"Error 4: Nulls and non-Variants"
strName = Nz(Me.MiddleName, "")
Ma ei tea... (shrug)
"Common Errors with Null"
http://allenbrowne.com/casu-12.html
"Error 4: Nulls and non-Variants"
strName = Nz(Me.MiddleName, "")
Ma ei tea... (shrug)
Allen Browne speaks strictly in the context of Null. I've already explained why yours isn't Null in all cases.
I have not coded If checks around the setting / fetching of each field. I have used Nz() to protect against uni-initialized type errors. The only way to get around setting fields in certain conditions would be to If block protect each field set / fetch.
Nz() is an If statement. It interprets as
Code:
If IsNull(Me.Control) Then
Me.Control = ""
End IfStill his LOC I quoted was "protection for String variable types".
When the DB class saw the NULL in the DB, then it would have initialized the field control to "". When I read the fields trying to save the record, then I would expect.... Let's just trace this one field through the classes...
DB class reading the field
Code:
Me.ver = Nz(adoRS!ver, -1)
Code:
Me.ver = ObjQualityTbl.ver
Code:
If Me.ver = -1 Then
MePointer.fldver.Value = ""
Else
MePointer.fldver.Value = Me.ver
End If
Code:
Me.ver = Nz(MePointer.fldver.Value, -1)So yes, I already have an If block in the case of this field to handle the NULL indicator value.
Should I be replacing "" with some more-nullish value?
Along those lines, this successfully works in this particular spot of the Validation class:
Code:
If IsNull(MePointer.fldver.Value) Then
Me.ver = -1
Else
Me.ver = MePointer.fldver.Value
End IfNo that bit of code is not good enough protection. I can trip up / cause an error still. I have now expanded out to:
and even this still has "Type Mismatch" as the IsNull sometimes is not found true, such as when the control started as empty, I type a character in the field and then backspace it out. Going from empty to value or value to empty works, just not from empty to having a value and right away removing that value. Very frustrating business.
I say VBA has too may NULL options.
Code:
If IsNull(MePointer.fldver.Value) Then
Me.ver = -1
Else
Me.ver = Nz(MePointer.fldver.Value, -1)
End IfI say VBA has too may NULL options.
Last edited:
I really think you need to re-read my posts and look at it from a different perspective. Everything you're complaining about now has already been covered in my posts but you've not picked up on it.
I've already told you the options you have, I also mentioned the pros and cons for initialising a field, I gave you the differences between Null and zls, and I also mentioned updating already saved data to comply with your new rules. Just take some time to go through my posts and digest it.
I've already told you the options you have, I also mentioned the pros and cons for initialising a field, I gave you the differences between Null and zls, and I also mentioned updating already saved data to comply with your new rules. Just take some time to go through my posts and digest it.
Finally found a solution which works in all three UI scenarios...
Code:
If IsNull(MePointer.fldver.Value) Or (Len(MePointer.fldver.Value) = 0) Then
Me.ver = -1
Else
Me.ver = MePointer.fldver.Value
End IfCan you see what I mean by re-reading my posts. What you've just posted was what I gave you many many posts away.
Less code.
My mind has been reeling with how complicated VBA makes NULL handling.
Implemented, other than switching to using vbNullString as that is more typing that two double quotes. Thanks!
Similar threads
