Help with combo box behaviour

[winsa]

Hi

Using A2K3, SQL backend.

I have a bound combo box displaying 2 columns (of a possible 5).
LimitToList is set to Yes.

I am using the following test in the Click event of a Save&Close button on
the form: If Len(Me.Debtor & VbNullString) = 0 Then Msgbox "Error"

This works if I haven't touched the combo box at all when filling in the
form, the error message will fire. However, if I select a Debtor, tab off or
leave the combo box, go back and delete the entry using the backspace key,
the error message will not fire when the Save&Close button is clicked.
Access seems to bypass the error message and tries to save, which it
obviously can't because Debtor is a required field, and thereby throws up the
INSERT fails error.

To me it looks like an empty combo box as I thought I had deleted all the
text. However, I have noticed that whenever I go back to the combo box,
there seems to be an entry of 5 spaces?!? I have tried testing this in the
NotInList event, but that event doesn't seem to fire. I have also tried
testing in the BeforeUpdate event too, but same result, ie. nothing!

Can anybody explain why this is happening and/or how I can then test whether
or not this field is null/empty before continuing?

Any help would be greatly appreciated.

 

[SteveM]

Try this instead:
If Nz(Me.Debtor, "") = "" Then Msgbox "Error"

Steve

 

[winsa]

Hi Steve

Thanks for your suggestion. Sorry for not getting back to you, as I have
been off from work for a while.

The suggested use of Nz didn't work. Only worked when I entered the five
spaces, but then it's not testing for an empty field any longer.

Any other suggestions?

 

[Bob Quintal]

Hi Steve

Thanks for your suggestion. Sorry for not getting back to
you, as I have been off from work for a while.

The suggested use of Nz didn't work. Only worked when I
entered the five spaces, but then it's not testing for an
empty field any longer.

Any other suggestions?

try If Len(Trim(Me.Debtor) & VbNullString) = 0 Then Msgbox
"Error"

 

[winsa]

Excellent!! The TRIM function worked like a charm!! I'm still getting those
weird five spaces if I delete the entry in the combo box, but the error is
firing correctly.

Thanks so much Bob! You're a star!

 

[Bob Quintal]

Excellent!! The TRIM function worked like a charm!! I'm
still getting those weird five spaces if I delete the entry in
the combo box, but the error is firing correctly.

Thanks so much Bob! You're a star!

/me blushes.

Thank you.

In the last few days I've been called a genius, a god and now a
star. If only my boss would call me something other than
"Troublemaker"