Deleting a Record

This type of action is fraught with danger. Have you ever deleted something and within two seconds wished you hadn't done so? In Access, even the Microsoft experts agree that a deleted record can not be recovered. We are going to do something about that.

In this lesson we will create a Command Button that causes the current record (displayed on our form frmClient) to be deleted, but before deleting the record, we will write it out to an audit file - a plain ASCII text file that will hold a safe copy of the data in case we ever regret deleting it!

So, first let's write a public utility procedure to write a string of data to a plain ASCII text file.

Open up your utility module in design view. There you see our earlier procedure that tests for an internet connection.

We will write a simple VBA utility procedure. Please remember that a true utility procedure, such as this one, can be used in any MSOffice application. Our Library courses show you how to build and use complete libraries of utility procedures such as this one.

I am indebted to John Hutchison of Woody's Lounge for this tip.

Public Function strPath(strFullName As String) As String
strPath = Left(strFullName, Len(strFullName) - Len(Dir(strFullName)))
End Function

Our utility procedure is Public - it will be available for future use from anywhere. Our utility procedure is a function, so it can return a result (which we want). Our utility procedure's name indicates to humans that it returns a string value, to wit the Drive and Path of a full file description. Our utility procedure's sole argument is a string - hopefully a full file description.

The one line of code assigns a value to the function; it is this value of the function that we will use when we call this utility procedure. The string returned is just the left-hand part of the full file name (which we passed in as a parameter). How much of the left-hand side? The length of the original string MINUS the length of the part of the full name that represents just the file name and extent - that will be the drive and path portion of the full name.

Here is the TEST macro:

Sub TESTstrPath()
MsgBox strPath(CurrentDb().Name)
End Sub

We are now in a position to write another utility Procedure - one that writes a string of characters to a text file.

Public Function strPrintFile(strFile As String, strMsg As String) As String
Dim intFile As Integer
intFile = FreeFile
Dim strWorkFile As String
strWorkFile = strFile
' add an extent if there is not one yet.
If InStr(1, strFile, ".") > 0 Then
Else
strWorkFile = strWorkFile & ".txt"
End If
' add a path if there is not one yet.
If InStr(1, strFile, "\") > 0 Then
Else
strWorkFile = strDrivePath(CurrentDb().Name) & strWorkFile
End If
Open strWorkFile For Append As #intFile
Print #intFile, strMsg
Close #intFile
strPrintFile = strWorkFile
' Sub TESTPrintFile()
'     MsgBox strPrintFile("DeletedRecords", "Deleted text records")
' End Sub
End Function

This function takes a string of characters as a file name, takes another string of characters which are to be written to the file, and returns a string of characters representing the full name of the file that has been written to. Bear with me.

If you look at the embedded TEST macro, you will see that we have elected to write our deleted records to a file called simply "DeletedRecords". Our utility procedure "strPrintFile" determines if the supplied name has a valid file extent. If not (as happens in our case), strPrintFile will append a standard text extent ".TXT" to the name being used. Likewise, if the supplied name appears to have no Path component, our utility procedure will prefix the path of the database. Regardless, our utility will always return the full name of the file to which data has been written. We could preserve that name and at the end of the session pop up a message saying "Deleted records have been written to C:\GREAVES\DeletedRecords.TXT", or similar.

Now that we have a mechanism for preserving or archiving deleted data, we can develop a click event.

We will need a Command Button called cmd_Delete with a caption "Delete"; because deletion is fraught with hazards, we will insist that the user double-click to delete a record.

If you are using the Wizard you will need "Record Operations", and within there "Delete Record".

Your skeletal code will look like this:

Private Sub cmd_Delete_Click()
On Error GoTo Err_cmd_Delete_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_cmd_Delete_Click:
Exit Sub
Err_cmd_Delete_Click:
MsgBox Err.Description
Resume Exit_cmd_Delete_Click
End Sub 

But we want a double-click.

You will notice that the objects box, near the top-left corner of your VBE screen shows " cmd_Delete", while the methods box, near the top-right corner shows "Click". Select "DblClick" from the methods box to create new skeletal code, and cut and paste the body of the original Click event into your Double-click event. Make sure that you remove all traces of the Click event.

Private Sub cmd_Delete_DblClick(Cancel As Integer)
On Error GoTo Err_cmd_Delete_DblClick
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_cmd_Delete_DblClick:
Exit Sub
Err_cmd_Delete_DblClick:
MsgBox Err.Description
Resume Exit_cmd_Delete_DblClick
End Sub

As it stands, this code will surely delete the current record; however, we want to preserve the data (using our strPrintFile procedure) before the deletion takes place, so introduce yet another utility procedure, this time to take the current record, held in our form, and to re-assemble it as a string of data fields separated by the TAB character.

Public Function strFormRecord(frm As Form, strDelimiter As String) As String
Dim strResult As String
strResult = ""
Dim ctl As Control
For Each ctl In frm.Controls
On Error Resume Next
strResult = strResult & ctl & strDelimiter
Next ctl
strFormRecord = strResult
End Function

Finally, augment the basic double-click event to trap data before it is deleted:

Private Sub cmd_Delete_DblClick(Cancel As Integer)
On Error GoTo Err_cmd_Delete_DblClick
Dim strRecord As String
strRecord = strFormRecord(Me, vbTab)
Call strPrintFile("DeletedRecords", strRecord)
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_cmd_Delete_DblClick:
Exit Sub
Err_cmd_Delete_DblClick:
MsgBox Err.Description
Resume Exit_cmd_Delete_DblClick
End Sub

Search