Solved

Access: Delete Current Record

Posted on 2007-12-05
4
1,219 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 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