Results 1 to 5 of 5
  1. #1
    mphaneuf79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    4

    Issue deleting record

    I run Quality Assurance (QA) for a squadron in the Navy and I have a database that tracks on-going discrepancies that have not been fixed after a audit. I have two tables I am dealing with for this issue and those are the contact table and the discrepancy table. When a discrepancy comes in that I need to add to the database I assign a person in QA, a Quality Assurance Representative (QAR), to see the process through until the discrepancy is closed. I add those QARs to my contact form and it populates to the appropriate table. On my discrepancy input form I have the ability to pull the drop down and assign a QAR that exist in the contacts form. However, from time-to-time I rotate people out of QA and need to delete them out of the database as they no longer work in QA or the command. I created a form that pulls up the basic data of the selected QAR and contains a delete button to delete the record. However, when I click on the button it tells me that the contact also resides in my discrepancy table in the "assigned to" column for discrepancies they were assigned. I am racking my brain on how to clear those fields they are assigned on the discrepancy table and delete the contact from the contacts table. Thank you for any help you can provide.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Suggest instead of delete have a field to flag as 'inactive'. This can be a yes/no type or date/time to show date changed to inactive status.

    Sounds like you don't care about history of assignments. If that is the case, you will first need to change the assigned contact in Descrepancy table for each record they are associated with. Then contact can be deleted. Alternatively, if you have a new person to replace the outgoing one and they should assume all the same discrepancy assignments, just change the info in Contacts table for that record, or if no name yet change to TBD in name fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The inability to delete a record can be caused by various situations. While Cascade Updates and Cascade Deletes might be a setting you're missing and that is preventing this, I strongly advise stepping back and re-evaluating this practice. Not only can you end up with orphan records (those that no longer have a "parent" related record), reinstatement of a condition (e.g. user returns) means you need to recreate that which you have deleted, and for no good reason. It is unlikely you are about to run out of space unless maybe you're putting attachments in Access tables (and that is a different problem) but you are losing historical data. Where users are concerned, you are better off changing their status, and that is best done via a date field (e.g. InactiveDate). You simply ignore those whose Inactive date is not Null.
    Last edited by Micron; 06-20-2019 at 04:16 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    mphaneuf79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    4
    Thank you for the help. I wise person once told me to never delete data from my database. I dont know why I was trying to skirt that. I made an active/inactive radio button and the date they went inactive. I also worked the form to assign QARs to only pull from active personnel in the query. Thank you again!

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I made an active/inactive radio button and the date they went inactive.
    If I may, I would not use 2 fields to impart the same message. A field like InactiveDate would do both - tell you that user is inactive as well as when. If inactive date is null, they are not. In your situation it is possible for one or the other to suggest 'inactive' while the other does not. Besides, it's just generally accepted that each field in a record should provide information for one thing, and that no more than 1 field is used to impart the same basic information.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-11-2018, 01:43 PM
  2. Deleting a record and all associated information
    By ExponentiaI in forum Queries
    Replies: 3
    Last Post: 03-17-2017, 10:16 AM
  3. Deleting a record
    By alexc333 in forum Access
    Replies: 7
    Last Post: 07-26-2011, 10:43 AM
  4. Error when deleting a record
    By access in forum Forms
    Replies: 5
    Last Post: 06-15-2009, 12:33 PM
  5. Deleting Record Contents, not complete record...
    By Charles Waters in forum Access
    Replies: 2
    Last Post: 06-24-2008, 12:00 PM

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