Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    20

    Unanswered: Removing a value from a combobox without affecting the database

    Hello,

    I have a combobox in a form that holds string values ("cmbTypes). This Combobox gets the values from a SELECT query done on a table ("types" table). The value chosen in the cobobox is fed into the main table, which contains reservations.
    One more detail (I don't know if it's relevent): There is a subdatasheet connecting between the types table and the main table (made automatically by ACCESS).

    The problem is this:

    One of the types is not relevent anymore and I was asked to get rid of it (as a option in the combobox), but at the same time to leave all old resevations with this type for future analysis.

    I tried taking it off the types table ----> All records with this type were erased.

    I tried manipulating the query for the combobox ("SELECT ...WHERE NOT)-------> All records with this type were erased.

    I tried deleting the subdatasheet, but it didn't make a difference ( and probably has nothing to do with it for all I know).

    How Can it be done? Where am I wrong?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    best thing to do is to change the way the comnbobox is populated
    put a flag (column on the relevant datasource to indicate if this is a current value, or if you prefer a delisted value.

    only populate the combo box with current values

    eg select my,column,list from mytable where IsCurrent=true
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2010
    Posts
    20

    Thanks

    Thanks for the quick response.


    I'm not sure I understood you fully - I've added a new boolean field to the type table, checked all types in type table as true except the old one and adjusted the query so that it'll show only 'TRUE' types. Is that what you meant by flag and IsCurrent ? If not, can you explain again?

    Anyways what I did worked, but I forgot one thing (which didn't): My users are accustomed to using the form's Datasheet view, and of course, when the old 'type' is not in the combobox, the old records with the old type are blank. Is there a way to sort that out or am I stuck?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yeah sounds like you've got the idea of an "iscurrent" flag. another a\pproach is to flag a row as "NoLongerValid".. its much of a muchness

    its a design stylistic, but I do not ever give my users the option of a datasheet, which they can edit or modify. if they want a spreadsheet style display, they can have it, but they cannot ever make changes tot he underlying data. the reason you can't control or validate data using VBA with datasheet views. the user interface is one of the places to stop or deter crap data entering the system. you have limitd validation rules built in to Access JET so much of the validation has to be done in the USer interface.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •