Solved

Use VBA to test a record, before deleting it and then abort the delete instruction.

Posted on 2014-01-16
4
836 Views
Last Modified: 2014-01-16
When selecting a record and pressing the delete button, I want the system to do a test on the record and if positive, I want to advise the user of the condition and why he may not delete the record. Then I want the system to abort the delete instruction.

I envisage that that will be done by VBA code in the On Delete action of the applicable form.
Will you help please.
0
Comment
Question by:Fritz Paul
  • 2
  • 2
4 Comments
 
LVL 84
ID: 39785249
What sort of conditions?

If you want to check if a value exists in the record, for example:

If Me!SomeField = 1 Then
  If Msgbox("The value of SomeField is " & Me!SomeField & " Do you want to remove this record?", vbYesNo) = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord
  End If
End If

Or do you need to check one or more values in the current record against other values in other tables?
0
 

Author Comment

by:Fritz Paul
ID: 39785327
Say form record selectors are set to yes. Then user clicks on record selector and on keyboard presses delete. Then

If Me!SomeField = 1 Then
   Msgbox("The value of SomeField is " & Me!SomeField & " and the record may therefore not be deleted.")

Cancel the delete action   'This is the VBA I am looking for please.

End If
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39785368
You can use the Form's Delete method to handle this:

Private Sub Form_Delete(Cancel As Integer)
    If Me!SomeField = 1 Then
           Msgbox("The value of SomeField is " & Me!SomeField & " and the record may therefore not be deleted.")
        Cancel = True
    End If
End Sub

Open in new window

Setting "Cancel = True" stops the process from continuing.
0
 

Author Closing Comment

by:Fritz Paul
ID: 39785376
Thanks a lot. It works.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question