INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Code behind Command button to delete record

Code behind Command button to delete record

(OP)
Using Access 2016
Donations part of a church congregation database

If the user wants to Delete a donation for a particular donor, the form is opened, the donor selected, and the record desired for deletion selected.

The user has two choices: (1) press Delete on the keyboard, or (2) press a Delete command button

Behind the command button, called cmdDelete, lies this code

CODE -->

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Select Case MsgBox("  Do you really wish to" _
                   & vbCrLf & "   DELETE this record?" _
                   & vbCrLf & "" _
                   & vbCrLf & "This cannot be undone!" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Delete check")
    Case vbYes
        GoTo DeleteProcess
    Case vbNo
        Exit Sub
End Select
    
DeleteProcess:
    DoCmd.SetWarnings False
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    DoCmd.SetWarnings True

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub 

This has always worked in the past but doesn't work in Access 2016 (part of the Microsoft Office 365 suite).

Can anyone point me to a way to fix this code?

Thanks!

Tom

RE: Code behind Command button to delete record

That is some old code. It is best to avoid DoMenuItem. Try use these lines instead.

CODE --> vba

'select and delete the record
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord 

Duane
Hook'D on Access
MS Access MVP

RE: Code behind Command button to delete record

(OP)
Duane
Thanks. It's been quite some time since I built that database, and - primarily in view of my age - I'm doing less and less of it now.

It would seem that Access 16 may have made some changes that make old code, such as what I used, redundant.

Thanks again.
Tom

RE: Code behind Command button to delete record

(OP)
Actually, changing the code behind the Delete command button creates another problem.

Now, when I press the Delete command button, I get the message I built in asking whether or not I want to delete the record, yes or no, so I click Yes, and then get the message

CODE

The command or action 'Delete Record' isn't available now 

Interestingly enough, selecting the record and hitting the Delete key on the keyboard works.

I have checked the Form;s properties to be sure that "Allow Deletions" is set, and it is.

Not sure what's going on.

Tom

RE: Code behind Command button to delete record

(OP)
Yes, it's a Continuous form.

The Delete Command button is at the top of the form, outside the area where the records show.

RE: Code behind Command button to delete record

(OP)
Is there a way I can send a screen shot on here?

RE: Code behind Command button to delete record

(OP)
It's in the Detail section, in an area across the top, just above where the records for editing show. Right beside it is a command button to "Return to Main Menu"
Tom

RE: Code behind Command button to delete record

(OP)
It's invisible until the user selects the particular record they wish to edit or delete.

RE: Code behind Command button to delete record

You could provide your entire code. This is what works for me:

CODE --> vba

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

    If MsgBox("Confirm Delete", vbYesNo + vbQuestion, "Really?") = vbYes Then
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdDeleteRecord
    End If
Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub 

Duane
Hook'D on Access
MS Access MVP

RE: Code behind Command button to delete record

(OP)
Duane
I apologize. Duane. I did show the original code in the initial post. I didn't post the altered code. Here it is.

CODE

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Select Case MsgBox("  Do you really wish to" _
                   & vbCrLf & "   DELETE this record?" _
                   & vbCrLf & "" _
                   & vbCrLf & "This cannot be undone!" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Delete check")
    Case vbYes
        GoTo DeleteProcess
    Case vbNo
        Exit Sub
End Select
    
DeleteProcess:
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    
    DoCmd.SetWarnings True

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub 

The only difference I see between your method and mine is that you are using If and End If around the two select and delete lines...whereas I am using a Case statement.

Should that make a difference?
Tom

RE: Code behind Command button to delete record

(OP)
Sorry, one mistake in answering your question.

Since this is an Edit form it's a Single form, not Continuous.

What happens is this:
User opens the form
Down the left side are the all Envelope numbers that are assigned.
The user selects the desired envelope number, and a list of entries for that donor come up.
The user can then edit amounts in a particular record or delete that record.


Tom

RE: Code behind Command button to delete record

(OP)
No subforms.

It's called frmEditNewGivings and is used strictly for editing entries that have been made for donors - to correct mistakes, duplicate entries made.
Tom

RE: Code behind Command button to delete record

Explain this "The user selects the desired envelope number, and a list of entries for that donor come up." What creates the list on the left?

Duane
Hook'D on Access
MS Access MVP

RE: Code behind Command button to delete record

(OP)
It's a list box that pulls data from a query.

The code for the query is

CODE

SELECT DISTINCT qryNewGivings.EnvNbr
FROM qryNewGivings
ORDER BY qryNewGivings.EnvNbr; 

The list will contain the envelope numbers in which data exists.

qryNewGivings queries data in tblNewGivings

Tom

RE: Code behind Command button to delete record

Is the "list of entries" the list box? Otherwise it suggests a continuous form.

Duane
Hook'D on Access
MS Access MVP

RE: Code behind Command button to delete record

(OP)
The list box is down the left side. It has the assigned envelope numbers.

When the user selects, say, 719, all donstions, by date given, come up for that envelope number, populated by the data from the query.

I see that I called the form Single. And that Delete command button had always worked until now.

I can change it to Continuous if it will make the difference.
Tom

RE: Code behind Command button to delete record

How can "all the donations come up"? This sounds like a continous form? Do you actually see more than a single donation at a time?

Duane
Hook'D on Access
MS Access MVP

RE: Code behind Command button to delete record

(OP)
If envelope

RE: Code behind Command button to delete record

(OP)
If envelope 719 has made donations on 20 Sundays during thia year, you will see all of those in rows down the form.

Date, then each category (Local, M&S, Memorial, Building etc.) in a row across the form.

The properties showed Single, but I will change that. It's so long since I designed it.

RE: Code behind Command button to delete record

Now I recall you are storing multiple donations in a single record.

I would try create a simple form with the minimal controls and code to test.

First thing would be to fix the table structure.

Duane
Hook'D on Access
MS Access MVP

RE: Code behind Command button to delete record

(OP)
I still don't quite understand why the Delete command button doesn't work...results in an error message saying that "command Delete record" isn't available right now...however, hitting the Delete key works fine.
Tom

RE: Code behind Command button to delete record

The selection when using the delete key is definitely in the record. I am thinking the record selection in code doesn't find the correct record. Where was your cursor prior to clicking the delete button?

What is the SQL/record source of the form?

Duane
Hook'D on Access
MS Access MVP

RE: Code behind Command button to delete record

(OP)
Duane
I got this one fixed. I recreated the form, and this one is okay now. Not really sure what was going on, but I'm glad to have it fixed.
Tom

RE: Code behind Command button to delete record

Based on "list box is down the left side" AND "you will see all of those in rows down the form" there must have been a single main form and a continuous subform. I know of no other way to have these appear on a standard form:

               +--------------+
               |   DELETE     |
               +--------------+

+---------+   +_Label1     Label2    Label3_+     
|list     |   |_____________________________|
|         |   |_____________________________|
|         |   |_____________________________|
|         |   |_____________________________|
|         |   |_____________________________|
|         |   |_____________________________|
|         |
+---------+
 

Duane
Hook'D on Access
MS Access MVP

RE: Code behind Command button to delete record

(OP)
Duane
Yes, you are abundantly correct. I did confuse the issue in that regard. (the only excuse I can offer is that I am just recovering from a bout of the shingles and they're nothing to be coveted, really energy-draining)

Anyway, that's no excuse for wasting your time and I sincerely apologize. I don't know what was wrong with me that I didn't figure that out yesterday. A forest-trees thing maybe.

In recreating, I moved the DELETE command button from the main form to the subform, and that, I believe, was the issue from the outset. I need to check with the user, the church secretary, but I suspect it hasn't worked for a while and she just didn't tell me.

Tom

RE: Code behind Command button to delete record

Tom,
Thanks for the confirmation that I am not totally confused.

Good luck with the shingles.

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close