[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Access: Delete Current Record

Posted on 2007-12-05
4
Medium Priority
?
1,249 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

650 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