I want to NOT DELETE when I Delete

[Randomblink]

I am working on a database where there will be a few people using it... I have already built forms where the users will interact with the data...

I have added buttons that allow the user to add new records and delete them as well...

All my buttons use the following code, this way I can control them to a T... Here is the code to help with my question...

Public Function btnBasics(curForm As Form, curButton As Label, Optional rqstdObj As Object)
Dim recNew As Boolean, deleteQuestion, btnOption As String, frmTarget As Form
Dim frmScope As String, strTarget As String, btnName As Integer, rCount As Integer
Dim testDebug As String
On Error GoTo Err_btnBasics

recNew = curForm.NewRecord

GoSub CheckTheButton

btnOption = Mid(curButton.Name, 5, 9)
Select Case btnOption
    Case "DelCurRec": GoSub DelCurRec
    Case "AddNewRec": GoSub AddNewRec
    Case "PrtCurRec": GoSub PrtCurRec
    Case "SavCurRec": GoSub SavCurRec
    Case "GotFrsRec": GoSub GotFrsRec
    Case "GotNxtRec": GoSub GotNxtRec
    Case "GotPrvRec": GoSub GotPrvRec
    Case "ClsCurFrm": GoSub ClsCurFrm
    Case "ClsPopFrm": GoSub ClsPopFrm
    Case "ClsLnkFrm": GoSub ClsPopFrm ' We treat LNK forms the same as POP forms...
    Case "OpnRegFrm": GoSub OpnRegFrm
    Case "OpnPopFrm": GoSub OpnPopFrm
    Case "OpnLnkFrm": GoSub OpnLnkFrm
End Select

Exit_btnBasics:
    Exit Function

OpnRegFrm:
    DoCmd.OpenForm frmScope
    Set frmOpener = curForm
    Return

OpnLnkFrm:
    Dim stLinkCriteria As String

    stLinkCriteria = "[" & CStr(curForm![mnIdentifier].ControlSource) & "]=" & curForm![mnIdentifier]
    DoCmd.OpenForm frmScope, , , stLinkCriteria, , , curForm![mnIdentifier]
    Set frmOpener = curForm
    Return

OpnPopFrm:
    DoCmd.OpenForm frmScope, , , , , , curForm![mnIdentifier]
    Set frmOpener = curForm
    Return

UndAllAct:
    Dim ctlC As Control
    ' For each control.
    For Each ctlC In curForm.Controls
        If ctlC.ControlType = acTextBox Then
        ' Restore Old Value.
            ctlC.Value = ctlC.OldValue
        End If
    Next ctlC
    Return

ClsPopFrm:
    If curForm.Dirty Then DoCmd.RunCommand acCmdSaveRecord
    Select Case frmOpener.Name
        Case Is = "": GoSub ClsCurFrm
        Case Is <> "":
            With frmOpener
                .Requery
                .Repaint
            End With
            GoSub ClsCurFrm
    End Select
    Return

ClsCurFrm:
    DoCmd.Close acForm, CStr(curForm.Name), acSaveYes
    If Forms.Count = 0 Then Application.Quit acQuitSaveAll
    Set frmOpener = Nothing
    Return

SavCurRec:
    DoCmd.RunCommand acCmdSaveRecord
    curForm.Repaint
    Return

PrtCurRec:
    DoCmd.RunCommand acCmdSaveRecord
    Select Case rqstdObj.Name
        Case "Invoice": DoCmd.OpenReport "rptInvoiceRoutingSlip", , , "[Pprwrk_ID] =" ' &  Form_subfrm_Paperwork.Pprwrk_ID  from a Different db
        Case "RFA": DoCmd.OpenReport "rptRFATrackingSlip", , , "[Pprwrk_ID] =" ' & Form_subfrm_Paperwork.Pprwrk_ID          from a Different db
    End Select
    Return

DelCurRec:
    deleteQuestion = MsgBox("Are you sure you want to delete the current " & curForm.Tag & "?", vbYesNo, "Delete " & curForm.Tag)
    Select Case deleteQuestion
        Case vbYes
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
            DoCmd.GoToRecord , , acFirst
        Case vbNo
            MsgBox "This Record was NOT deleted.", vbOKOnly, "Canceled Delete"
    End Select
    deleteQuestion = Null
    Return

AddNewRec:
    recNew = curForm.NewRecord
    Select Case recNew
        Case Is = True:
            Select Case curForm.Dirty
                Case True:
                    DoCmd.RunCommand acCmdSaveRecord
                    DoCmd.GoToRecord , , acNewRec
                Case False:
                    MsgBox "You are currently in a new record already."
            End Select
        Case Is = False: DoCmd.GoToRecord , , acNewRec
    End Select
    Return

GotFrsRec:
    If recNew = True Then Return
    DoCmd.GoToRecord , , acFirst
    Return

GotPrvRec:
    If recNew = True Then Return
    DoCmd.GoToRecord , , acPrevious
    Return

GotNxtRec:
    If recNew = True Then Return
    DoCmd.GoToRecord , , acNext
    Return

CheckTheButton:
    btnName = Len(curButton.Name) ' First we check the calling button for it's length
    rCount = (btnName - 14) 'Then, since the shortest button name is 14 characters long, lets count the characters MINUS 14
    Select Case rCount ' Now let's check that count
        Case Is <= 0: Return ' If the Count is equal to or less than Zero, we are done here...
        Case Is >= 7 ' however, if we have a number greater than or equal to 7, we have a target for our other actions
            frmScope = Right(curButton.Name, rCount) ' Let assign that target to our variable: frmScope
        Case Else
            MsgBox "We have a problem with:{btnbasics} under:{CheckTheButton}"
    End Select
Return

Err_btnBasics:
    errMessage Err
    Resume Exit_btnBasics

End Function

Now, to point out the code that runs for my Delete buttons I use the following:

DelCurRec:
    deleteQuestion = MsgBox("Are you sure you want to delete the current " & curForm.Tag & "?", vbYesNo, "Delete " & curForm.Tag)
    Select Case deleteQuestion
        Case vbYes
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
            DoCmd.GoToRecord , , acFirst
        Case vbNo
            MsgBox "This Record was NOT deleted.", vbOKOnly, "Canceled Delete"
    End Select
    deleteQuestion = Null
    Return

What I want to do... is INSTEAD of actually deleting the record, I need to do a couple things instead...

1) I want to log who is trying to delete the record
2) I want the record to not be viewable by regular users anymore

Now I have thought about adding a few extra fields to my database tables... something like...

table:
field: del_DeletingUser
field: del_Checked

When users clicked the DELETE button, the code above would check the del_Checked box AND drop their NTUser name into the del_DeletingUser field INSTEAD of actually deleting the record... Then I would just set a FILTER on ALL my forms to NOT show records where del_Checked was checked...

Then I would create a Front-End Admin where it was a duplicate of the other Front-End BUT all the forms would have the opposite filter... or maybe have button that would turn OFF that filter...

But, I figured before I start on that route, I would INSTEAD check to see if anyone has already come across this and worked out an alternative solution... Thanks a million...

 

[Fizzio]

I would agree with your method of 'deleting' the current record as it is the easiest to apply.
If the users have the possibility of deleting records from many tables, and you only want to be able to audit the delete procedure, I would be tempted to do the following.

Add the del_Checked field to each table where records can be deleted (and to the respective forms recordsource to filter on)

2 Create an audit table which records the username, Formname/recordsource/TableName where the record has been deleted from, the actual record ID or other identifier, timestamp. The formname/recordsource will be the easier values to obtain, especially if you have cascade delete enabled for child records)

does that help?

 

[Randomblink]

Yes and No

See, I am glad that someone agrees with some of what I said...

I guess I didn't clarify...

Since my function runs the following whenever someone hits the delete buttons...

DelCurRec:
    deleteQuestion = MsgBox("Are you sure you want to delete the current " & curForm.Tag & "?", vbYesNo, "Delete " & curForm.Tag)
    Select Case deleteQuestion
        Case vbYes
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
            DoCmd.GoToRecord , , acFirst
        Case vbNo
            MsgBox "This Record was NOT deleted.", vbOKOnly, "Canceled Delete"
    End Select
    deleteQuestion = Null
    Return

I figured I would replace the:
DoCmd.RunCOmmand adCmdDeleteRecord
with something like...

curForm.del_DeletingUser = <insert function that grabs NTUsername>
curForm.del_Checked.Value = True

Then, once the DoCmd.GoToRecord ,,acFirst hits, the Form will refresh, the Filter will kick in, and the record that was 'Deleted' will not be visible anymore...

Then, the admin Front-End will show those Deleted Records and the admin can decide whether they need to be deleted or not...

As for your adding those field to every table, this is where I was saying I wasn't clear... because that is EXACTLY what I was saying... Each table would have those two fields added... That way my DelCurRec would work for EVERYFORM I put the delete button on...

Thanks for the help... by the way...

 

[Fizzio]

Not every table would be necessary as I say due to the possibility of master/child tables and cascade update/delete.
You will only need to add the fields to the master tables as on the form/subform combination, if the main record does not show, neither will the sub-record.
This will save a little bit of time on your behalf.

 

[Randomblink]

Ahhhh

However, some of my subforms have data that is handled separately from its parent data...

For instance...
I have the following...

tbl_Bank
tbl_BankBranch
tbl_BankEmployees

Each table has a linked field to the one above it...
So Employees are linked to Branches...
So Branches are linked to Banks...
And I have forms setup for each level...

So, I have already added the fields in...
I really appreciate it...
THis is really nice...
Setting up the filter is something I have NEVER done on a form before, so I have to figure that one out next... THEN I have to adjust the listboxes that list out, for instance, all the branches of a bank... I have to switch them to NOT listout 'Fake Deleted' records in each... ugh!

Ah well... not everything is easy...