?
Solved

Access: Delete Current Record

Posted on 2007-12-05
4
Medium Priority
?
1,244 Views
Last Modified: 2013-11-27
I have a table (Name: "Main_Table" Columns: "Name", "SS#") Each record is distinct in this table

I have a subform (Recordsouce: "Main_Table" ---has 2 textboxes with control source: "Name", "SS#" ---The default view is set to "Continuous Forms" so that I can see all records in "Main_Table". Also "Allow Edits" is disabled because I want to force the user to press a "Delete button" so that code will be run.

In the footer of this subform is a button titled "Delete". How do I make it so that when users selects a record in the subform and click the button titled "Delete", my database will delete the selected record?

I am using the example  code below I found on line, but it does not work, because "Allow Edits" is set to "no".

Sample Code:

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

Open in new window

0
Comment
Question by:ouestque
[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
4 Comments
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 2000 total points
ID: 20417411
If you're command button was named "cmdDelete" you could add the following code to the On_Click Event.  That should take care of it for you.  Specifically this approach is not affected by the AllowEdits property being set to No because you're not deleting thhe form's recordset, you're deleting the form's RecordSetClone.  Although it will appear to do exactly the same thing the approach to what you're deleting and how is different enough that the value of the Form's Allow Edits Property is rendered MUTE.

Note:  I did take the libery of assuming that the original recordset contained two fields in it that had the names [SS#] and [Name] and also assumed that these names while technically undesirable because one contains the pound character "#" and the other field called "Name" is the equally undesirable because so many objects have a property called "Name" that it is usually best to give fields like these other names like SSN and CustomerName.  Even so, the code below will work with the fields named just as they are and I'm not suggesting you should rename them just because I pointed this little detail out.  The most important thing to remember is that if you Alias the fields by using a query like...

SELECT [SS#] as SSN, [Name] as CustomerName FROM Main_Table;

vs. what I assume is there whic is probably something like...

SELECT [SS#], [Name] FROM Main_Table;

or perhaps

SELECT * FROM Main_Table;

************************

The latter two examples will work fine with the code below as long as the table named "Main_Table" has two fields in it named "SS#" and "Name" (excluding quotes of course).  

Becasue of the way we call out the fields in the form's recordset clone it wouldn't really matter name you gave the fields as long as each line that read...

Me.RecordsetClone.Fields("SS#").Value

had the contents in quotes adjusted to match the field name in the table.  My hope is that by providing an approach that cares so little about whether good naming conventions were used or not you need not be compelled to incorporate naming conventions after the fact but can instead simply move on building your next table with more understanding than you may have had when you inherited or created the table named "Main_Table" or any others like it.  Anyway enough on that, the question wasn't about naming conventions but I thought it was worth pointing out that the solution doesn't care if good naming conventions were used or not, it'll work either way.

Rick
Private Sub cmdDelete_Click()
    If Me.NewRecord Then
        MsgBox "Can not delete a new record until it has been saved", vbCritical, "Delete Canceled"
    Else
        Me.RecordsetClone.Bookmark = Me.Bookmark
        If vbYes = MsgBox("Are you sure you want to delete this record?" & vbCrLf & vbCrLf & _
                          "     SSN# = " & Me.RecordsetClone.Fields("SS#").Value & vbCrLf & _
                          "     Name = " & Me.RecordsetClone.Fields("Name").Value & vbCrLf, _
                          vbYesNo + vbDefaultButton2 + vbExclamation, _
                          " Please Confirm") Then
            Me.RecordsetClone.Delete
        End If
    End If
End Sub

Open in new window

0
 
LVL 3

Expert Comment

by:incrediblejohn
ID: 20424772

Me.AllowEdits =True
 DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
 DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.AllowEdits = False

No?
0
 

Author Comment

by:ouestque
ID: 20460404
I want to thank ya'll so much for the help. Rick Rickards came up with the solution and got there first so he gets the points. Thank ya'll both agiain for your feedback!!!
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20462601
Glad to be of help. :)
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

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.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

762 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