Return to Swichboard automatically if deleted record is last record

[JillianM]

Hi All,

I have a form. The source of the form is a query which prompts the user to enter a date. When the date is entered, all applicable records are returned. Simple stuff. So, I have a "delete" command button on my form. It works fine. The problem is, if say, the user wanted to delete a record and it was the only record showing, the record gets deleted and then the form just goes blank. What I would like to do, is add some code to the existing command (below) that brings up a YN box and says something like "You have deleted the last record for this date. Return to home screen?" and when the user clicks "yes", the program returns to the user interface/switchboard. If there are more records to show, the delete function would act normally and just show the next record.

Is my idea possible? Can anyone help me out? Your help is greatly appreciated. The command code is below:

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click

Dim mbxresponse As VbMsgBoxResult

mbxresponse = MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo)

If mbxresponse = vbYes Then
Call DoCmd.SetWarnings(False)
Call DoCmd.RunCommand(acCmdDeleteRecord)
Call DoCmd.SetWarnings(True)
Else
Call MsgBox("The record was not deleted")
End If

Exit_cmdDeleteRecord_Click:

Call DoCmd.SetWarnings(True)
Exit Sub

Err_cmdDeleteRecord_Click:

Call MsgBox("The record was not deleted" & vbCrLf & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , _
"Error Deleting Record")
Resume Exit_cmdDeleteRecord_Click
End Sub

 

[TheAceMan1]

How are ya JillianM . . .

Try this:

[blue]On Error GoTo GotErr
   Dim NL As String, DL As String
   
   NL = vbNewLine
   DL = NL & NL

   If MsgBox("Are you sure you want to delete this record?", _
             vbQuestion + vbYesNo) = vbYes Then
      Call DoCmd.SetWarnings(False)
      Call DoCmd.RunCommand(acCmdDeleteRecord)
      DoEvents
      
      If Me.Recordset.RecordCount = 0 Then
         If MsgBox("You have deleted the last record for this date." & DL & _
                    "Return to Home Screen?", _
                    vbQuestion + vbYesNo, _
                    "User Response Required! . . .") = vbYes Then
            DoCmd.OPenForm "[purple][B][I]SwitchBoardFormName[/I][/B][/purple]"
            DoEvents
            DoCmd.Close acForm, Me.Name, acSaveNo
         End If
      End If
   Else
      Call MsgBox("The record was not deleted")
   End If
   
SeeYa:
   Call DoCmd.SetWarnings(True)
   Exit Sub
   
GotErr:
   Call MsgBox("The record was not deleted" & DL & _
   "Description: " & Err.Description & NL & _
   "Error Number: " & Err.Number, , _
   "Error Deleting Record")
   Resume SeeYa[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JillianM]

You rock my world! That worked without a hitch. Thank you so much for your help!