Limited Combo Box allowing empty string -- BUG?!?

[tomashek]

Hello everyone,

I am using an up-to-date (as far as OfficeUpdate tells me, at least)
Access 2003, and discovered an annoying feature that may even be a bug
with an Access Project.

I have a combo box on a form, bound to a data field that is, on the SQL
server side, defined as NOT NULL. The RowSource for the control is a
SQL statement that pulls a list of values, and the "Limit to List"
property of the combo box control is set to "Yes".

In a nutshell, I want to be sure that there is always a valid piece of
data in that field.

However, what Access is allowing a user to do is select an item in the
list, then "erase" that value's characters using the delete key, and
consequently saving the record with the empty string as the control's
value. Thus, instead of getting a valid value for that field, I get
the empty string.

While I understand there are ways around this, it seems as though it is
not supposed to function that way. After all, the empty string is NOT
a value in the drop-down list, and when I say "Limit to List: YES", I
don't want ANY value submitted that is not in the list!

On the server side, I cannot filter for the empty string without a
trigger (ugh!), so I am left with using code in the "BeforeUpdate"
event for EVERY limited control to keep users from "erasing" the value,
thinking they are deleting the record.

Is this a bug? Have others had this problem? Is there a global option
somewhere that can modify this behavior? I can't think of any instance
where I would want it to behave the way it does. Can you?

Thanks,
Todd

 

[Bill Edwards]

I tried this using a value list combo box and was able to reproduce it using
Access 2003 and SQL 2000. Never would have believed it.

I added a check constraint of IS NOT NULL and IS > ' ' (empty string) and
that seemed to catch the error when the user attempted to move off the
record.

I also solved it by basing the combo box on a query and defining a foreign
key constraint on the field.

 

[tomashek]

Yeah, pretty ugly "feature", huh?

I've gotten around it, too, but it makes for a lot of repetitious work
to do it. Good call on the FK constraint.

It shouldn't be necessary, though...

-Todd