Code to delete records after a search is conducted

[david.paton]

I work for an organisation that looks after war widows and I am making a database to record all of the widow’s details. I have a search form that searches through all records in a database for a phrase that is typed into a text box. The results are displayed in a list box. Here is a link to a page that describes how I made it. I have used all the same names as in this example for objects in my database. https://access-programmers.co.uk/forums/showthread.php?t=188663

So I am now able to search through records for items that I type in the search box and the list narrows as I type. As the widows die, I want to remove the widows from the database.

I was thinking that I would put a button on the search form with some code behind it that, once the record was located using the search function, I could highlight it in the list box and press the button to delete it.

I can’t code so would someone be able to help me with the code that I put behind the button please? I also can’t upload the database due to privacy issues.

 

[Uncle Gizmo]

I have a search form that searches through all records in a database for a phrase that is typed into a text box.

That's not good! You are relying on the operator to type exactly the same phrase every time. (The Phrase you are searching for) Any typing error will result in a record not being found. You would be much better off having a positive system where the user clicks a checkbox or makes a selection from a combobox.....

Updated - As it wasn't clear...

 

[david.paton]

I am just trying to make a way to delete records as when the widows die, I want to remove them.

I just had the text box as a way of finding the records you want deleted.

 

[david.paton]

I also will be the only person using this database so it is just to help myself find the records to delete them.

 

[Minty]

This may sound like teaching grandma to suck eggs, but generally you don't delete records immediately just expire them, with either an Archive flag or perhaps better in your case a expiry date.

Then you can easily still un-expire one if you make a mistake, but easily exclude those records from your daily tasks and reports.

 

[static]

You might be able to base another delete or update query on QRY_SearchAll.

i.e. in your button onclick event add something like.

currentdb.execute "Delete * from YourMainTable where ID in (Select ID from QRY_SearchAll)"

Take a backup first obviously.

 

[Gasman]

David,

I would second Minty's suggestions and just flag the record as Deceased?

I do something similar with records for listboxes where I need to move a record from one to another. To do that I update and take note of a yes/No field in the table called IsUsed

You could employ a similar process?
Then just filter our those flagged as Deceased in your search query?
I have also utilised that code as well.

I would probably add a date, so I could keep track of how mane in a year, and in that case you could use a date field and check for null insttead?

Lots of ways to do it, and keep the data there just in case.

FWIW here is my code for updating the IsUsed field. Note my list box is multi select, so it goes through all the list entries, but hopefully you get the idea.

Private Sub cmdAdd_Click()
  Dim varItm As Variant
  
  For Each varItm In Me.lstAvailable.ItemsSelected ' Selecteditem
    CurrentDb.Execute ("UPDATE tblUserParagraph SET IsUsed = True " _
    & "WHERE ParagraphID = " & Me.lstAvailable.Column(0, varItm))
  Next
  Me.lstAvailable.Selected(lstAvailable.ListIndex) = False ' Deselect entries
  Me.lstSelected.Selected(lstSelected.ListIndex) = False

  Me.lstAvailable.Requery
  Me.lstSelected.Requery
  
End Sub

HTH

 

[Uncle Gizmo]

I agree with flagging the record as opposed to deleting it, but I've always wondered about the data protection issue.

When someone asks you to remove all records relating to them from your system.

There must always be a category of Records you need to keep whatever the customer demands because you have to keep invoices and the like for tax purposes in other words you could not delete those sort of Records even if the customer insisted you did?

Sent from my SM-G925F using Tapatalk

 

[david.paton]

I got some help from a friend who is a programmer to delete a record from the list box but I think I like that idea of archiving records so maybe I will try that.

Here is the code he helped me with.



Private Sub Cmddelete_Click()

' If there are no records selected, then skip this function and throw a dialogue box
If Me.SearchResults.ItemsSelected.Count = 0 Then
If MsgBox("No records selected", vbOKOnly, "No Record Selected") = vbOK Then
Exit Sub
End If
End If
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbNo 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
Exit Sub
End If
End If
Dim strSQL As String
Dim db As DAO.Database

Set db = CurrentDb
strSQL = "DELETE " & _
"FROM [Widow] " & _
"WHERE ([WidowID] = "

For Each varItem In Me.SearchResults.ItemsSelected
strSQL = strSQL & Me.SearchResults.Column(0, varItem) & ")"
Next varItem

Call db.Execute(strSQL)
If MsgBox("Record Deleted", vbOKOnly, "Record Deleted") = vbOK Then
Me.SearchResults.Requery
Exit Sub
End If

End Sub




This code partially works, it doesn't ask you for a second confirmation though. Because I can't code, I don't know why but. Despite it working, I want to go with the option of not deleting it but I am not sure how to go about archiving items or applying an expiry date.

Could someone help me please with how to do this?

 

[Cronk]

Just to flag another reason for marking a record Inactive rather than deleting it or moving to another table is reporting.

I've done any number of member databases where there hav been the need to report on membership turnover ie numbers joining/leaving and reasons for leaving.