Disabling form control based on combobox not working with null

[Kozbot]

I am trying to disable certain form controls based on a combo box selection. The combo box looks up a product list in a products table. However I am not disabling the form control based on the first column combo box value but the value of column in the same record.

Private Sub cboProduct_AfterUpdate()
If Me.cboProduct.Column(12) = "Null" Then Me.Surface_Resistivity.Enabled = False Else Me.Surface_Resistivity.Enabled = True
End Sub

This code works fine if the value in column 12 is not null and I change the code to look for that value. I've also tried it with If IsNull(me.cboproduct.column(12) and the code stiill doesn't function.

What am I missing with null values here?

 

[James Dudden]

You are using Null wrong. What you have is a string called 'Null' rather than testing if it is actually null. Try this:

Private Sub cboProduct_AfterUpdate()
If IsNull(Me.cboProduct.Column(12)) Then Me.Surface_Resistivity.Enabled = False Else Me.Surface_Resistivity.Enabled = True
End Sub

 

[boblarson]

You don't use "Null" in quotes. Null is not a value. It is a condition - lack of value.

I would use this method as it handles Nulls AND empty strings "" which can be there if someone just backspaces the value out.

If Len(Me.cboProduct.Column(12) & vbNullString) = 0

So to shorten your code we have:

Me.Surface_Resistivity.Enabled = (Len(Me.cboProduct.Column(12) & vbNullString) = 0 )

And that would take care of it.

 

[pr2-eugin]

What if the Value is not Null but a Zero Length String? I always use a different approach to trap Null and ZLS.. Something along the lines of..

Private Sub cboProduct_AfterUpdate()
    If [COLOR=Red][B]Len([/B][/COLOR]Me.cboProduct.Column(12)[COLOR=Red][B] & vbNullString) = 0[/B][/COLOR] Then 
        Me.Surface_Resistivity.Enabled = False 
    Else 
        Me.Surface_Resistivity.Enabled = True
    End If
End Sub

Also just that you know.. Null is a value which defies all rules of arithmetic comparison.. =, <>.. It is not a value that you can compare it with..

EDIT: James & Bob beat me to it..

 

[Kozbot]

ahhh yes this works. you guys rule