Results 1 to 6 of 6
  1. #1
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56

    Relationships - Delete user but keep history/data

    I have a DB setup with users and their information. How can I remove a user from the DB without losing their data?



    Basically I just don't want the users seeing this (deleted) user in the list on a form. So maybe "hide" is a better term than delete.

    Details:
    Access 2010

    Table "Employees" - holds username and some other user specific info.
    Table "Hours" - holds username related data(how many hours they worked on specific dates).
    Form "WorkHourEntry" - The form I use to allow the users to login and enter their data(hours). The have a combo box that displays all the users in a list. This list/combo box is where I want to hide users from when the employee leaves the company or dept. and is no longer needed. But, we need to keep their history/data.

    Thanks,
    Tom

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Typically you'd have a "status" field, which you could use to filter out old users on your combo.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you search various forums you will see techniques that suggest you don't physically delete records. These approaches, generally, use a "logical delete". This is implemented by means of a boolean field (Yes/No) in the table. The field is often named something like: IsDeletedYN or similar. When this field/flag is set to
    Yes/ON/-1/True, this record is no longer active. It has been "logically deleted".
    This requires logic in your queries/forms/reports to be aware of the value in IsDeletedYN. In your WHERE clauses/constraints, you must include Where IsDeletedYN <> False. If you do not use IsDeletedYN in your criteria, you will get all records-- including the ones you have "logically deleted".

    Good luck with you project.

  4. #4
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Deleting records which have references is always a big no-no. As others have suggested, make a Yes/No field to indicate whether that user is active. Then adjust your queries accordingly to show active users only for those queries that need to have this filter.

  5. #5
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    That worked 'orange' thank you. I simply added a "Disabled" field to my table with employees. Then I filtered via that column with the following code in criteria:

    <>True

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are welcome. Don't forget the Disabled field in any of your queries.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to add user name to comment history
    By sspreyer in forum Programming
    Replies: 1
    Last Post: 11-26-2013, 06:31 PM
  2. user access to delete
    By 54.69.6d.20 in forum Programming
    Replies: 3
    Last Post: 07-31-2012, 09:44 AM
  3. Replies: 2
    Last Post: 04-25-2012, 08:14 AM
  4. Replies: 2
    Last Post: 03-01-2012, 11:14 AM
  5. Replies: 12
    Last Post: 10-20-2011, 08:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums