CODEWITHSUNDEEP
vbams-accessms-access-forms
Daniel Bragg
Daniel Bragg

Reputation: 1953

Why is conditional test for ComboBox value being ignored? (Access VBA)

I have an If clause that was being completely ignored, until I discovered a workaround, but now I'd like to better understand what is happening under-the-hood.

We have a form with a ComboBox that gets populated, but a default value is not set on it. When I go to save the form, we need to test if the user selected a value. The code was:

If (Me.Combo78.Value = "") Then
    mb "Please select a value"
End If

The code would never fire, until I changed the condition to read:

If ("" & Me.Combo78.Value = "") Then
    mb "Please select a value"
End If

I'm guessing that the "" & forces the comparison to be a text comparison, and therefore can validly test against the empty string "", but what was the previous comparison actually doing, and is there a better, more intuitive way to manage this? The solution I have just feels gross.

-- 30 years experience with coding in Pascal, HTML, Javascript, but <1 year coding in VBA, after being handed a legacy application that needs debugging.

Upvotes: 1

Views: 40

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

If the ComboBox has no value then Me.Combo78.Value will be null and consequently Me.Combo78.Value = "" will be null and will not validate the test expression for the if statement.

In your second code, concatenating an empty string with a null value will return an empty string, and so "" & Me.Combo78.Value returns an empty string, thus validating the test expression.

If you wish, you can verify this for yourself in the VBE Immediate Window (accessible using Ctrl+G):

?Null = ""
Null
?Null & "" = ""
True

A more readable solution might be:

If IsNull(Me.Combo78) Or Me.Combo78 = "" Then
    mb "Please select a value"
End If

Alternatively, you could use the Nz function:

If Nz(Me.Combo78, "") = "" Then
    mb "Please select a value"
End If

Since the Value property is the default member for this class, it may be safely omitted.

Upvotes: 1

Related Questions