A2007 Some Combo Boxes changing back color on their own as they loose focus, WHY?

mdlueck said:
No... the control's value was Varient/String I remember, so must not be bound. The actual table column is numeric. So I believe that control is correctly unbound.
Click to expand...

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.
 
mdlueck said:
I would think that this Debug.Print output smacks at corruption, no?

Code: 
[B][COLOR=RoyalBlue]Nz(MePointer.fldver.Value, 0): ><[/COLOR][/B]
In my mind I should have received >0< , no?
Click to expand...
If it's not returning 0 then it's not Null, it's a zero-length string.
 
vbaInet said:
it's not Null, it's a zero-length string.
Click to expand...

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:
Code: 
If Len(Nz(Me.Textbox, vbNullString)) = 0 Then
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.
 
vbaInet said:
* 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.
Click to expand...

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. :D
 
vbaInet said:
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.
Click to expand...

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?
 
mdlueck said:
I am wrestling to see the difference VBA is imposing.
Click to expand...
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

mdlueck said:
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?
Click to expand...
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.
 
vbaInet said:
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.
Click to expand...

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.
 
mdlueck said:
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.
Click to expand...
Nz() is an If statement. It interprets as
Code: 
If IsNull(Me.Control) Then
    Me.Control = ""
End If
You are initialising the control. You would use Nz() when you want assign the value of the field to a variable or perform a comparison. When you use Nz() in a control assignment, that's initialising. Bound controls.
 
vbaInet said:
I've already explained why yours isn't Null in all cases.
Click to expand...

Still 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)
Validation class receiving the value from the DB class
Code: 
  Me.ver = ObjQualityTbl.ver
Validation class populating the form fields:
Code: 
  If Me.ver = -1 Then
    MePointer.fldver.Value = ""
  Else
    MePointer.fldver.Value = Me.ver
  End If
Validation class validating the form (Preparing to commit back to the DB)
Code: 
  Me.ver = Nz(MePointer.fldver.Value, -1)
BANG!

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?
 
Michael, it's really not a hard concept. I've explained this over again. I would advise you read through my posts again and perhaps you will see it from a different light.
 
vbaInet said:
Nz() is an If statement. It interprets as
Code: 
If IsNull(Me.Control) Then
    Me.Control = ""
End If
Click to expand...

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 If
Critique before I start changing my handling of this sort of thing in the program, please.
 
I really don't have anything else to add to be honest. I believe I've covered all that needs to be covered. It's all within my posts.

If I had more to say I would have responded.
 
No that bit of code is not good enough protection. I can trip up / cause an error still. I have now expanded out to:

Code: 
  If IsNull(MePointer.fldver.Value) Then
    Me.ver = -1
  Else
    Me.ver = Nz(MePointer.fldver.Value, -1)
  End If
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.
 
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.
 
mdlueck said:
such as when the control started as empty, I type a character in the field and then backspace it out.
Click to expand...

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 If
Yes, Or'ing BOTH checks in the If statement was necessary protection in this case to correctly handle all three possible UI scenarios.
 
Can you see what I mean by re-reading my posts. What you've just posted was what I gave you many many posts away.
vbaInet said:
* If you don't want to change that property then you can use something like this to check for both Null and zls:
Code: 
[COLOR=Blue] If Len(Nz(Me.Textbox, vbNullString)) = 0 Then[/COLOR]
By the way, vbNullString is the constant for "".
Click to expand...
Less code.
 
You must log in or register to reply here.

Similar threads

B
Combo Box Search Issue
2
Replies
34
Views
773
MajP
MajP
M
Proper focusing of windows in MS Access (natively or by a code)
2
Replies
31
Views
819
MajP
MajP
J
Interesting artical about what government can and can't do. It's a bit longish, but worth the read.
2 3
Replies
43
Views
1,695
Thales750
T
S
Speeding up the loading of multiple cascading combo boxes
2
Replies
39
Views
1,407
Pat Hartman
P
C
Conditionally formatting labels and other controls
Replies
0
Views
609
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom