Solved

Access: Delete Current Record

Posted on 2007-12-05
4
1,199 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
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now