Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by tomashek@uiuc.edu, Nov 11, 2005.

  1. tomashek@uiuc.edu

    tomashek@uiuc.edu
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    What actually are the 3 worlds? And is Maya limited only to the physical plane? Questions and Answers Nov 30, 2012
    Sikh News Mightier than the kirpan | Hardeep Singh Kohli (Guardian Unlimited) Breaking News Feb 10, 2010
    Sikh News Sikh officer wins payout in turban row (Guardian Unlimited) Breaking News Oct 7, 2009
    Sikhism Sikhs Unlimited Book Reviews & Editorials Jul 9, 2009
    Sikh News Sikh police seek bulletproof turbans (Guardian Unlimited) Breaking News May 12, 2009

  3. Bill Edwards

    Bill Edwards
    Expand Collapse
    Guest

    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@uiuc.edu> wrote in message
    news:1131658598.645214.154570@g49g2000cwa.googlegroups.com...
    > 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
    >
     
  4. tomashek@uiuc.edu

    tomashek@uiuc.edu
    Expand Collapse
    Guest

    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


    Bill Edwards wrote:
    > 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@uiuc.edu> wrote in message
    > news:1131658598.645214.154570@g49g2000cwa.googlegroups.com...
    > > 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
    > >
     

Share This Page