"Delete Record" command button woes

[Access User]

I just added what I thought was going to be a 'Delete Record' cmd button to
my Access form. Here's the VBA the 'wizard' created:


Private Sub Command96_Click()
On Error GoTo Err_Command96_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command96_Click:
Exit Sub

Err_Command96_Click:
MsgBox Err.Description
Resume Exit_Command96_Click

End Sub


Apart from making this pronging sound when I click it, it has no other effect.

Anyone know what's going on? I would've thought this'd be a piece of cake...

 

[Klatuu]

Here is a better way.
Remove the domenuitem code from your button's click event. It is
interesting that Microsoft says not to use domenuitem, that it is obsolete
and is only there for backward compatibility, but Access wizards still
produce such unreadable code.

Put this line of code in the Click event instead:

Call DelCurrentRec(Me)

Now create a new standard module and paste the code below into it. Do not
name the module DelCurrentRec. Access will not allow that.

Now, you can use this code from any from to delete a record.

'---------------------------------------------------------------------------------------
' Procedure : DelCurrentRec
' DateTime : 2/6/2006 09:07
' Author : Dave Hargis
' Purpose : Deletes a record
'---------------------------------------------------------------------------------------
'
Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function

 

[Access User]

Hi and thanks for trying to help but there seem to be at least one issue
that's preventing things from soldiering on.

1) Here's the click code as modified per your response:

Private Sub Command96_Click()
On Error GoTo Err_Command96_Click


Call DelCurrentRec(Me)

Exit_Command96_Click:
Exit Sub

Err_Command96_Click:
MsgBox Err.Description
Resume Exit_Command96_Click

End Sub

2) after adding the module, when asked I named it "DelRecord" minus the
quotation marks.

3) when I click on the cmd button I get two messages which I'm quoting below

a) Error 13 (Type mismatch) in procedure DelCurrentRec of Module
modFormOperations which has an 'Ok' button which when clicked displays the
following one
b) Object variable or with block variable not set

Any ideas at this point?

 

[Stockwell43]

Hi Access User,

Try this:

Private Sub cmdDeleteRecord_Click()
Dim MsgStr As String
Dim TitleStr As String
MsgStr = "Are You Sure You Want To Delete This Record?"
TitleStr = "Confirm Delete Record"
If MsgBox(MsgStr, vbYesNo, TitleStr) = vbNo Then
Exit Sub
End If
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
End Sub

My button is called cmdDeleteRecord but replace it with your name. When you
are on the current record click the button to delete it. I believe I found
another way of doing it where there was less code but this works and has been
tested by the managers and I having heard any complaints yet so it must be
working for them.

 

[Chris]

In your module, make sure the following line of code
is either all on one line:

Public Function DelCurrentRec(ByRef frmSomeForm As Form)

Or make sure that you add an underscore if the
code is in multiple lines

Public Function DelCurrentRec(ByRef _
frmSomeForm As Form)

Text does not wrap correctly in the newsgroup posts so sometimes you have to
do a little clean up after copying code.

HTH,
Chris