Solved

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

Posted on 2014-01-16
4
906 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 85
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 85

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

626 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