Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237

    is it ok to delete unwanted records and whats the Best way if needed?

    if a record has accidentally been added, whats peoples opinion here on deleting it, from what I've read its a little frowned apon.



    I have Add new Entry forms and Edit Forms.

    is it better to delete straight from the table or is there a better way or should you just let that record go?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    if its a bad record , delete it.
    In the form, select it, press delete key.

  3. #3
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by ranman256 View Post
    if its a bad record , delete it.
    In the form, select it, press delete key.
    ranman256 Thanks that's what I thought.

    I have 5 subforms on a parent form which I have the Deletions set to No.

    can code over come the deletions control?

  4. #4
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    I am using the below code in a module

    Function DeleteARecord
    If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
    If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
    "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord
    End If
    End If
    End Function

    and then on click of the delete button

    Private Sub cmdDelete_Click()
    DeleteARecord
    End Sub


    I get a run time error 3200 as has related info in a tbl

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    6,123
    Sounds like your table is in a relationship with another table with referential integrity.
    The record could be deleted by first breaking the relationship - almost certainly a bad idea.
    Alternatively you need to delete that record in the master table - having confirmed its OK to delete

    An alternative method is to have a boolean field 'Active' with default value = true
    When you want to 'delete' a record you change Active to false
    So the record still exists IF needed in future.

    However, doing that will mean you need to alter existing queries to filter for Active=True
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by ridders52 View Post
    Sounds like your table is in a relationship with another table with referential integrity.
    The record could be deleted by first breaking the relationship - almost certainly a bad idea.
    Alternatively you need to delete that record in the master table - having confirmed its OK to delete

    An alternative method is to have a boolean field 'Active' with default value = true
    When you want to 'delete' a record you change Active to false
    So the record still exists IF needed in future.

    However, doing that will mean you need to alter existing queries to filter for Active=True
    yeah ridders52 Generally all my tables are in a relationship.... I think I have found a way by deleting the record from an Edit form, I have turned the deletions on and it seems to work fine.

    I do have accesslevel security in place on any forms that can change records of interest, so only the owner or myself will have access

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Shaun,

    What kind of data are you dealing with? How important to the business is any data manipulation?
    Whether it's OK to remove such a record or not really depends on the business and business rules.
    If it was your bank account, and someone removed $XX, what would you want to know?

  8. #8
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by orange View Post
    Shaun,

    What kind of data are you dealing with? How important to the business is any data manipulation?
    Whether it's OK to remove such a record or not really depends on the business and business rules.
    If it was your bank account, and someone removed $XX, what would you want to know?
    the data being deleted would not be important like bank accounts or to do with money...

    for example a client might give us the go ahead to do a job so we create a job record of the clients details. But then the client may cancel the job due to what ever reason and may not proceed down the track

    im only giving permissions to delete to owner and myself which will be taking over down the track

  9. #9
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    But then the client may cancel the job
    For this, I would not delete. What I might I want to know later:

    - how many times they did this. Could indicate they are too loose with their letting of orders/contracts or just don't take quoting seriously
    - if they change their mind again, the info could be easily re-activated if kept
    - I could know how many jobs/quotes or $$ potential earnings did not transpire into sales. Might be fodder for investigating sales staff, or whether or not it's limited to this customer
    - etc, etc.

    Knowledge is power - or so someone apparently once said.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Micron View Post
    For this, I would not delete. What I might I want to know later:

    - how many times they did this. Could indicate they are too loose with their letting of orders/contracts or just don't take quoting seriously
    - if they change their mind again, the info could be easily re-activated if kept
    - I could know how many jobs/quotes or $$ potential earnings did not transpire into sales. Might be fodder for investigating sales staff, or whether or not it's limited to this customer
    - etc, etc.

    Knowledge is power - or so someone apparently once said.
    yeah that's a good way to think Micron, thanks for pointing out. I think I was worried about waisting memory but in the scheme of things is nothing.

    Knowledge sure is power

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Volatile memory is not wasted if you apply filter. Storage is cheap. Access does have 2GB file size limit which might be a concern.
    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.

  12. #12
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by June7 View Post
    Volatile memory is not wasted if you apply filter. Storage is cheap. Access does have 2GB file size limit which might be a concern.
    yeah ok I haven't had much to do with applying filters...

    I wasn't aware of the 2gb size limit - is that for the whole database in total with data?

    I think Ive gone past that already and have had some out of memory errors happen

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    The 2GB size limit encompasses everything in the db - data, forms, reports, code.

    Run Compact & Repair then look at the file size stat in Windows Explorer.
    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.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    6,123
    ShaunG
    Are you sure you aren't confusing MB with GB

    If the db really is over 2GB I doubt you will be able to open it at all let alone compact it.
    However, you might be able to compact it successfully from another database
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by June7 View Post
    The 2GB size limit encompasses everything in the db - data, forms, reports, code.

    Run Compact & Repair then look at the file size stat in Windows Explorer.
    Sorry June7 I got my GB and MB mixed up...

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query creating records *2 of whats in database
    By brow1726 in forum Queries
    Replies: 3
    Last Post: 12-12-2012, 06:58 PM
  2. Delete Query Help Needed
    By tushar05 in forum Queries
    Replies: 1
    Last Post: 08-05-2011, 05:34 PM
  3. Subforms and unwanted duplicate records
    By Del Coro in forum Forms
    Replies: 5
    Last Post: 02-24-2011, 03:16 PM
  4. Replies: 6
    Last Post: 09-01-2010, 03:12 PM
  5. protecting records from unwanted edits
    By Hunt2871 in forum Security
    Replies: 2
    Last Post: 08-01-2010, 10:49 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