Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting or Hiding lookup records (2002/SR-1)

    I want to allow my users to delete or hide records from lookup tables even when they have records associated on it's many side.
    For example: Say I have tblLocations with a 1-to-many referential integ checked, cascade updates relationship with tblMain. My tblMain is storing tblLocations primary key.
    I want to give my users the ability to delete/hide locations from combo boxes on the forms when a location is no longer valid. Obviously by deleting a location from tblLocations it removes the key stored in tblMain.
    What would be the best way to allow them to delete/hide locations so they do not show up in the combo boxes on the various forms? Should I have a separate table linked to tblLocations and use that to fill the combo boxes on the various forms? How should I go about doing this?

    Thanks,
    Don

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    I typically handle this type of situation (Locations that are no longer valid or Employees that are no longer employed) by placing a Yes/No field in the table. I name this field Active. I uncheck it when the record is no longer applicable in displays. Now if you have your combobox select only the Locations that are active, the others will not display.

    I do not delete these records, because they are typically associated with history and I think that should be kept in tact.

    HTH

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    Thomas,
    Thanks for the reply. I did try this method but it actually removes existing selections that I would like to keep displayed. For example: John Doe is no longer an employee but he is associated with 20 records in the database. So when I uncheck the active field for him he no longer shows up in the selection list in the combo box which is what I want. But in my forms John Doe doesn't show up in the 20 records that he is associated with. Each record that he is associated with are blank on the forms. Is there a way to keep his name in the combo box for the records he was already associated with?
    Thanks for you help.
    Don

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    Sorry, but I do not understand what you are trying to accomplish. First you said "... no longer shows up in the selection list in the combo box which is what I want", then later you said "Is there a way to keep his name in the combo box ... ". Please clarify what these mean.

    What is the connection between the combobox and the form(s)? If they are seperate, check to see that the form's data source includes all Employees. If the combobox is on the form, why do you want to see something that you can't choose from the combobox?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting or Hiding lookup records (2002/SR-1)

    You can do this by enabling cascading deletes if you have implemented referential integrity on the relationships, but it isn't a particularly good idea to allow your users to do this. Thomas's suggestion of an Active field is a better option. You just have to add that to the criteria of your forms and comboboxes so that you only select records that are active. It would simplify things to have an Active field in all the tables, not just in the lookup tables. You could use an update query to update the "many" records to Active = False if the lookup item were set to false.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    Thomas,
    The record itself may still be active, but the employee isn't. Since the record is still active I need to be able to see what employee handled (for instance) this order. So when browsing (orders), John Doe's name would be displayed in the combo box, but not in the list when the combo box is "dropped down" because he (John Doe) is not active.
    My combo box is on the form and it's control source is EmployeeID (the foreign key to the employee table), and it's rowsource is looking up the names in the employee table and hiding the key value.
    I'm sorry that I can't explain this much better right now. I'll think about how to word this in a better way.
    Thanks for all your help.
    Don

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    I understand your situation perfectly - we are fighting this one right now. The solution we came up with is an interim one while we find a better one, but it works pretty well in some cases. The combo box row source is normally set to display all employee records, active or note. However when you enter the combo box, we trigger the OnEnter event where we set the RowSource property to be only Active Employees. That has the undesirable effect of clearing the display for the current record (and any other records visible if you are using continuous forms). Once you select an active person, assuming you want to edit, and exit the combo box, we restore the RowSource to show all employees. Not perfect, but it got us through a major cutover this week.

    Longer term, I think we will build an unbound form for adding new records where the combo box is restricted to the set of active employees. That still leaves the issue of needing to edit the combo box, but it makes adding records less obnoxious. In addition, we can put the add form at the top of the form and display the existing records in a continuous form. Another strategy is to layer controls so you have a display text box that sits directly on top of the text portion of the combo box, and put it in front of the combo. When you click the combo box it comes forward and you can select only the active employees. However this approach suffers from the same problem above - the combo box will be blank if an inactive employee is assigned to the record. In additions we see some performance and functional issues with stacked controls from time to time.

    Hope our experience is useful to you.
    Wendell

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting or Hiding lookup records (2002/SR-1)

    I've handled something similar in the past by using a two controls stacked (not optimal and this is about the only place I ever use stacked controls), a bound textbox on top of an unbound combobox, which has its backstyle set to transparent. That allows the display of the saved value even though it does not exist in the current combobox list. The combobox has its tabstop property set to no, and the textbox is sized to display the down arrow of the combobox.
    Charlotte

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    I stumbled into Charlottes soultion several months ago and it works great.

    I have added a ysnActive field to most of my tables, gives you flexibility, history and eliminates hard coding yourself into a corner.

    Other fields I include in most tables include:

    dtmDateCreate
    dtmDateUpdate
    dtmUpdateID
    dtmDateDelete

    Can't tell you how many times these fields have saved me. Operators have a way of not telling you everything (Who, What, When & Where) when your trouble shooting a problem.

    HTH

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    When you use this technique, is your record source for the form a query linking to the lookup table so you display the text rather than a number? And do you then run a VBA update routine on the AfterUpdate event for the unbound combo to update the data source?
    Wendell

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    I should use the technique with the active yes/no field, but not for selecting the the rowsource of the combo.
    Select all employees, active and not active. Show the active yes/no field in the combo.
    In the before update of the combo, do a check on the column yes/no and if not active cancel the update with a message that indicate that employee is no longer active and had to select another.
    Francois

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting or Hiding lookup records (2002/SR-1)

    Exactly.
    Charlotte

  13. #13
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    Wendell,
    Your solution to change the rowsource at runtime works perfectly for me because I'm not using continuous forms.
    Thanks for your help!!
    Don

  14. #14
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    I thought about going that route also Francois but I don't want to even allow the user to see the inactive employees. I have gone with changing the rowsource at runtime like Wendell suggested since I'm not using continuous forms, seems to be working great. Thanks for the reply!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Don

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Deleting or Hiding lookup records (2002/SR-1)

    Good suggestion - that's what we tried first. We actually sorted the inactive ones at the end. But the problem was that there were more inactive employees than active ones and that sort of blew the users away. If you only have a few inactive users it works reasonably well. But this database has been used since 1998, and the group has substantial staff turnover. At the moment they seem reasonably satisfied even though when the click in the combo, the inactive person disappears.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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