Code behind Command button to delete record 1

[THWatson]

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

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

 

[dhookom]

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

    [highlight #4E9A06]'select and delete the record[/highlight]
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

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

 

[THWatson]

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

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

 

[dhookom]

Is this a continuous form? Where is the delete button?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Yes, it's a Continuous form.

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

 

[THWatson]

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

 

[THWatson]

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

 

[THWatson]

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

 

[dhookom]

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

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

 

[THWatson]

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

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

 

[THWatson]

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

 

[dhookom]

Are there any subforms involved?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

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

 

[dhookom]

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

 

[THWatson]

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

The code for the query is

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

 

[dhookom]

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

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

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

 

[dhookom]

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

 

[THWatson]

If envelope