Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Message When Delete Records (Access 2003)

    When I go to delete a record I want to change the standard warning that says "The record cannot be deleted because there are related records" to my own personal message. What code do I use for that?

    Kerry

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Message When Delete Records (Access 2003)

    You can't change this message itself (as far as I know), but you need to get in before this message is displayed and display your own message.

    Here is an example:

    Private Sub cmdDelete_Click()
    Dim lngcount As Long
    Dim Msg, Style, Title, Response
    Dim sql As String
    lngcount = DCount("[BidderID]", "tblBidders", "[BuyerID]=" & Me![BuyerID])
    If lngcount > 0 Then
    MsgBox ("This Buyer cannot be deleted as it has registered at auctions.")
    Exit Sub
    Else
    Msg = "Are you sure you want to permanently delete this bidder entry ?" ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    Title = "Delete confirmation" ' Define title.
    ' Display message.
    Response = MsgBox(Msg, Style, Title)
    If Reponse = vbYes Then ' User chose Yes.
    sql = "delete tblbuyers.* from tblbuyers where tblbuyers.[buyerid]=" & Me![BuyerID]
    CurrentDb.Execute sql, dbFailOnError

    End If
    End If
    end sub
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message When Delete Records (Access 2003)

    You cannot change the message when you try to delete a record directly from a table or query, but that shiuldn't matter since end users shouldn't be able to work with a table or query directly.

    On a form bound to the table or query, you can use code in the On Error event:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
    Case 3200 ' related records
    MsgBox "Your custom message goes here.", vbExclamation
    Response = acDataErrContinue
    Case Else
    Response = acDataErrDisplay
    End Select
    End Sub

    The code checks which error occurred. If it is error 3200 ("The record cannot be deleted because there are related records"), a custom message is displayed, and the Response argument of the event procedure is set to acDataErrContinue. This tells Access to suppress the built-in error message. In all other cases, the response is set to acDataErrDisplay, causing Access to display the built-in message. You can add other Case <number> parts if you wish.

  4. #4
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Message When Delete Records (Access 2003)

    This is what I have on the Delete button. Where do I add your code?

    Private Sub cmdDelete_Click()
    On Error GoTo Err_cmdDelete_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    Exit_cmdDelete_Click:
    Exit Sub

    Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click

    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message When Delete Records (Access 2003)

    If you want to use the code posted by John Hutchison, it would replace your code, except of course you'd have to substitute the appropriate names.

    If you want to use the code I posted: it is a separate event procedure. You can copy and paste it from my reply into the module belonging to the form, below the code you already have. Or, if you prefer to create the code yourself:
    - Open the form in design view.
    - Make sure that the caption of the Properties window says Form.
    - Activate the Event tab.
    - Click in the On Error event (you'll probably have to scrolll down to see it).
    - Select [Event Procedure] from the dropdown list.
    - Click the ... button to the right of the dropdown arrow.
    - This will open the form's module in the Visual Basic Editor and create the event procedure:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)

    End Sub

    - Complete it as indicated in my previous reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •