Delete a record from a dialog and requery the form

[beacon]

Hi everybody,

This is probably going to sound unorthodox, but I have to log records that are deleted...I know, go figure.

Anyway, I have a form with a (continuous) subform, and on the subform I have a command button, called cmdDelete, that launches a dialog form, called frmDeleteCurren tRecord, where the user has to enter their name and a reason.

I have managed, through a ton of trial and tribulation, found a way to delete the current record on the subform from the query, qryEpisodeDetai l, using SQL. I had to pass parameters to the dialog form in order to create the delete query.

The problem I'm having, is that after the delete query runs and I'm returned to the subform, it says "#Deleted" in each of the controls on the subform. I have tried to refresh/requery in what feels like 100 different ways, but I keep getting error messages about how I need to save the current record.

Here's the code for the cmdDelete:

Private Sub cmdDelete_Click()
 
    Dim strConfirmDelete As String, strConfirmDeleteTitle As String
    Dim strCancelDelete As String, strCancelDeleteTitle As String
    Dim strNullDelete As String, strNullDeleteTitle As String
    Dim varParameter
    Dim varWhere As String
 
    'Parameters to be passed 0-8
    Dim strEpisodeDetailID, strPatientDetailIDFK, strFormIDFK, strDeficiencyIDFK
    Dim strProgressNoteIDFK, strDeficiencyDate, strQuantity, strEmployeeIDFK
    Dim strCorrectedDate
 
    strConfirmDelete = "Are you sure you want to delete this record?" + Chr(13) + Chr(13) + "You will be required to log the deletion."
    strConfirmDeleteTitle = "Confirm Delete"
    strCancelDelete = "The delete operation has been cancelled."
    strCancelDeleteTitle = "Delete Cancelled"
    strNullDelete = "The record you are trying to delete does not exist."
    strNullDeleteTitle = "Delete Failed: No Record"
 
    varWhere = "[EpisodeDetailID] = " & Me.EpisodeDetailID
 
    strEpisodeDetailID = Me.EpisodeDetailID
    strPatientDetailIDFK = Me.PatientDetailIDFK
    strFormIDFK = Me.FormIDFK
    strDeficiencyIDFK = Me.DeficiencyIDFK
    strProgressNoteIDFK = Me.ProgressNoteIDFK
    strDeficiencyDate = Me.DeficiencyDate
    strQuantity = Me.Quantity
    strEmployeeIDFK = Me.EmployeeIDFK
    strCorrectedDate = Me.CorrectedDate
 
    varParameter = strEpisodeDetailID & ";" & strPatientDetailIDFK & ";" & strFormIDFK
    varParameter = varParameter & ";" & strDeficiencyIDFK & ";" & strProgressNoteIDFK
    varParameter = varParameter & ";" & strDeficiencyDate & ";" & strQuantity
    varParameter = varParameter & ";" & strEmployeeIDFK & ";" & strCorrectedDate
 
    If vbYes = MsgBox(strConfirmDelete, vbExclamation + vbYesNo, strConfirmDeleteTitle) Then
        Forms!frmDeficiency.Visible = False
        If IsNull(DLookup("EpisodeDetailID", "qryEpisodeDetail", strWhere)) Then
            MsgBox strNullDelete, vbInformation + vbOKOnly, strNullDeleteTitle
            Me.FormIDFK.SetFocus
            Exit Sub
        Else
            DoCmd.OpenForm "frmDeleteCurrentRecord", DataMode:=acFormAdd, _
                WindowMode:=acDialog, openArgs:=varParameter
        End If
    Else
        MsgBox strCancelDelete, vbInformation + vbOKOnly, strCancelDeleteTitle
        Me.FormIDFK.SetFocus
        Exit Sub
    End If
 
End Sub
 

And here's the code for the cmdSubmit on the frmDeleteCurren tRecord:

Private Sub cmdSubmit_Click()
 
    Dim varEpisodeDetailID As String, varPatientDetailIDFK As String, _
        varFormIDFK As String, varDeficiencyIDFK As String, _
        varProgressNoteIDFK As String, varDeficiencyDate As String, _
        varQuantity As String, varEmployeeIDFK As String, _
        varCorrectedDate As String, varAuditorFName As String, _
        varAuditorLName As String, varReason As String, varDateDeleted As String
    Dim nullErrorTitle As String, nullErrorMessage As String
    Dim strConfirmDelete As String, strConfirmDeleteTitle As String
    Dim strCancelDelete As String, strCancelDeleteTitle As String
    Dim ctl As Control
    Dim strDeleteRecordSQL As String, strLogDeletionSQL As String
    Dim strDeleteReasonSQL As String
 
    'Title for fields with null values
    '------------------------------------
    nullErrorTitle = ": Null Value"
 
    'String for null values
    '------------------------
    nullErrorMessage = " cannot be blank. Please enter a value in this field."
 
    'Test for null values
    '-----------------------
    For Each ctl In Me.Controls
       If IsNull(ctl) And Not IsNothing(ctl.Tag) Then
            ctlName = ctl.Tag
            MsgBox ctlName + nullErrorMessage, vbExclamation, ctlName + nullErrorTitle
            ctl.SetFocus
            Exit Sub
        End If
    Next ctl    ' End of null value test
 
    varAuditorFName = Me.AuditorFirstName
    varAuditorLName = Me.AuditorLastName
    varReason = Me.ReasonDeleted
    varDateDeleted = Me.DateDeleted
    varEpisodeDetailID = Me.EpisodeDetailIDFK
    varPatientDetailIDFK = Me.PatientDetailIDFK
    varFormIDFK = Me.FormIDFK
    varDeficiencyIDFK = Me.DeficiencyIDFK
    varProgressNoteIDFK = Me.ProgressNoteIDFK
    varDeficiencyDate = Me.DeficiencyDate
    varQuantity = Me.Quantity
    varEmployeeIDFK = Me.EmployeeIDFK
    varCorrectedDate = Me.CorrectedDate
 
    strLogDeletionSQL = "INSERT INTO qryDeletedRecordsLog ( PatientDetailIDFK, EpisodeDetailID, FormIDFK, ProgressNoteIDFK, DeficiencyIDFK, DeficiencyDate, Quantity, EmployeeIDFK, CorrectedDate ) " & _
                        "SELECT qryEpisodeDetail.PatientDetailIDFK, qryEpisodeDetail.EpisodeDetailID, qryEpisodeDetail.FormIDFK, qryEpisodeDetail.ProgressNoteIDFK, qryEpisodeDetail.DeficiencyIDFK, qryEpisodeDetail.DeficiencyDate, qryEpisodeDetail.Quantity, qryEpisodeDetail.EmployeeIDFK, qryEpisodeDetail.CorrectedDate " & _
                        "FROM qryEpisodeDetail " & _
                        "WHERE (((qryEpisodeDetail.EpisodeDetailID)= " & varEpisodeDetailID & "));"
 
    strDeleteReasonSQL = "UPDATE qryDeletedRecordsLog " & _
                         "SET AuditorFirstName ='" & varAuditorFName & "', " & _
                         "AuditorLastName ='" & varAuditorLName & "', " & _
                         "ReasonDeleted = '" & varReason & "', " & _
                         "DateDeleted = #" & varDateDeleted & "# " & _
                         "WHERE (((qryDeletedRecordsLog.EpisodeDetailID)= " & varEpisodeDetailID & "));"
 
    strConfirmDelete = "Are you sure you want to delete this record?" + Chr(13) + Chr(13) + "Warning: The action cannot be undone."
    strConfirmDeleteTitle = "Confirm Delete"
    strCancelDelete = "The delete operation has been cancelled."
    strCancelDeleteTitle = "Delete Cancelled"
 
    strDeleteRecordSQL = "DELETE * FROM tblEpisodeDetail " & _
                         "WHERE tblEpisodeDetail.EpisodeDetailID = " & varEpisodeDetailID & ";"
 
    If vbYes = MsgBox(strConfirmDelete, vbExclamation + vbYesNo, strConfirmDeleteTitle) Then
        DoCmd.RunSQL strLogDeletionSQL
        DoCmd.RunSQL strDeleteReasonSQL
        DoCmd.RunSQL strDeleteRecordSQL
        CloseMe
        'THIS IS WHERE IT WON'T UPDATE - Sorry for the all caps, just an attention-getter.
        Forms![frmDeficiency]![fSubEpisodeDetail].Refresh
        Forms!frmDeficiency.Visible = True
    Else
        Exit Sub
    End If
 
End Sub
 

My intention is to have the delete command function just like the delete command on the toolbar, except for the fact that I need to go to another form real quick, have the user jot down some info, and then return to the updated subform.

It's late, so if this is unclear, please forgive me. I'll be happy to clarify if anything doesn't make sense.

[LeeGr]

Not sure if you guys got this resolved, but here's the solution:

Me.RecordSource = Me.RecordSource
Me.Requery