Select case problem

[RipperT]

The following code is in the after update event of unbound control
..cboCellNo

With Me
Select Case .cboCellNo
Case 1 To 2
.cboBunk.Enabled = True
.cboBunk.RowSource = "A;B"
.cboBunk = ""
Case Null
.cboBunk.Enabled = True
.cboBunk.RowSource = "I;J"
.cboBunk.SetFocus
Case Else
Debug.Print .cboCellNo
.cboBunk.Enabled = False
.cboBunk = ""
.cboBunk.RowSource = ""
End Select
End With

If I delete a value from .cboCellNo and hit enter, Case Else fires and
Debug.Print line prints the word Null. If the value of .cboCellNo is Null,
why doesn't the Null case fire? I've tried variations like Case Is Null,
Case Is = Null etc. Why won't it fire?

Thanks

Ripper

 

[Dirk Goldgar]

The following code is in the after update event of unbound control
.cboCellNo

With Me
Select Case .cboCellNo
Case 1 To 2
.cboBunk.Enabled = True
.cboBunk.RowSource = "A;B"
.cboBunk = ""
Case Null
.cboBunk.Enabled = True
.cboBunk.RowSource = "I;J"
.cboBunk.SetFocus
Case Else
Debug.Print .cboCellNo
.cboBunk.Enabled = False
.cboBunk = ""
.cboBunk.RowSource = ""
End Select
End With

If I delete a value from .cboCellNo and hit enter, Case Else fires and
Debug.Print line prints the word Null. If the value of .cboCellNo is Null,
why doesn't the Null case fire? I've tried variations like Case Is Null,
Case Is = Null etc. Why won't it fire?

Because Null is never equal to anything. Also, in VBA you have to use the
IsNull() function to test for Null. You need to make a two-stage test:

With Me
If IsNull(.cboCellNo) Then
.cboBunk.Enabled = True
.cboBunk.RowSource = "I;J"
.cboBunk.SetFocus
Else
Select Case .cboCellNo
Case 1 To 2
.cboBunk.Enabled = True
.cboBunk.RowSource = "A;B"
.cboBunk = ""
Case Else
.cboBunk.Enabled = False
.cboBunk = ""
.cboBunk.RowSource = ""
End Select
End If
End With