Try something like this in your code:
Private Sub cmdDeleteRecord_Click()
If Me.Dirty Then Me.Undo
If Not Me.NewRecord Then RunCommand acCmdDeleteRecord
End Sub
If you click the button while an edit is in progress, Access has to deal
with that first. Since you want to dump the record, it makes sense to undo
it rather than let Access save it and delete it (and it avoids any issues if
the record cannot be saved.)
After that, you may find you are at a new record, which can't be deleted.
Avoiding these 2 cases may solve your issues.
The 'No Current Record' issue is a known bug with A2002 SP3. Just add error
handling to ignore the error in the (AfterDelConfirm?) event.
The order of events you describe is weird. What it should do is fire the
Delete event first, once for *each* record being deleted. (That's one only
if the command button is clicked, but could be multiples in a
datasheet/continuous form if you use other methods such as the toolbar.) It
will be different if you use an ADP (not an MDB):
http://support.microsoft.com/kb/234866/
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dennis" <(E-Mail Removed)> wrote in message
news:2F66BF9B-9A10-4155-A8B5-(E-Mail Removed)...
> Hi,
>
> I’m new to Access & VBA, but many years of coding experience.
>
> OS: XP Pro – SP3
> Access: 2003 – SP3
>
> Issue:
> I’m having a problem getting a Delete Button to work. I want to delete
> the
> current record from the table on a simple name and address form. No
> sub-screens involved.
>
> There are two issues:
> 1. Sometimes it tells me that there is no current record even though I
> can
> see it on the screen.
> 2. Sometime Events fire in wrong sequence.
>
>
> 1. I can see the record on the screen, but it tells me there is no
> current
> record. When I click on the Edit on the Access Main Menu, there is no
> Select
> Record or Select All visible on the drop down menu. This is intermittent.
>
> 2. As part of my testing, I’ll click on the Delete Button three or four
> times and answer No each time. The normal events that fire (based on
> Debug.Print) are:
>
> cbDelMem_Click
> From_Delete
>
> Every once in a while, the events that fire are:
>
> cbDelMem_Click
> Form_BeforeDelConfirm
> Form_AfterDelConfirm - which displays the “member” was deleted msgbox
> Form_Delete – which displays “Ok to delete member”?
>
> My questioin is what is causing the Form_BeforeDelConfirm and
> Form_AfterDelConfirm to fire before the Form_Delete event and how do I fix
> it?
>
> History
> Been reading Delete Button items on the internet for the last week try to
> solve it. But I’m afraid it Access 1 Me 0.
>
> Following is the code:
>
> Private Sub cbDelMem_Click()
> On Error GoTo Err_cbDelMem_Click
>
> Debug.Print (“cbDelMem_Click”)
> If Me.Dirty Then
> Me.Undo
> End If
> If Not Me.NewRecord Then
> DoCmd.RunCommand acCmdSelectRecord
> DoCmd.RunCommand acCmdDeleteRecord
> End If
>
> Exit_cbDelMem_Click:
> Exit Sub
>
> Err_cbDelMem_Click:
>
> ' If the Delete Command was cancelled, Access generates a RunCmdCancel
> error message.
> ' We do not want to display this message, so we will not display the
> RunCmdCancel message.
> '
> Call BaseUtil.Dsp_Err_Msg(Err.Number, Err.Description,
> "cbDelMem_Click")
> Resume Exit_cbDelMem_Click
>
> End Sub
>
> ==========================================================
>
> Private Sub Form_AfterDelConfirm(Status As Integer)
> On Error GoTo Err_Form_AfterDelConfirm
>
> Debug.Print ("Form_AfterDelConfirm")
> DoCmd.SetWarnings True ' Turn on Access
> delete msg - turned off in B4 Del Confirm
>
> glngMbButton = vbOKOnly + vbInformation
> gstrMbTitle = "After Del Confirmation "
>
> Select Case Status
> Case acDeleteOK
> gstrMbText = gstrMemName & " was deleted." ' Position new
> record after delete!!!
> Case acDeleteCancel
> gstrMbText = gstrMemName & " was NOT deleted" & vbCrLf & vbCrLf
> gstrMbText = gstrMbText & "Programmer canceled the deletion."
> Case acDeleteUserCancel
> gstrMbText = gstrMemName & " was NOT deleted" & vbCrLf & vbCrLf
> gstrMbText = gstrMbText & "You canceled the deletion."
> End Select
> gintMbResp = MsgBox(gstrMbText, glngMbButton, gstrMbTitle)
>
> Exit_Form_AfterDelConfirm:
> Exit Sub
>
>
> Err_Form_AfterDelConfirm:
> Call BaseUtil.Dsp_Err_Msg(Err.Number, Err.Description,
> "Form_AfterDelConfirm ")
> Resume Exit_Form_AfterDelConfirm
>
> End Sub
>
> ==========================================================
>
> Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
> On Error GoTo Err_Form_BeforeDelConfirm
>
> Debug.Print ("Form_BeforeDelConfirm")
> DoCmd.SetWarnings False ' Turn off Access generated
> delete msg
> ' Turn on Access generated
> delete msg in AfterDelConfirm
> Exit_Form_BeforeDelConfirm:
> Exit Sub
>
> Err_Form_BeforeDelConfirm:
> Call BaseUtil.Dsp_Err_Msg(Err.Number, Err.Description,
> "Form_BeforeDelConfirm ")
> Resume Exit_Form_BeforeDelConfirm
>
> End Sub
>
> ============================================================
>
> Private Sub Form_Delete(Cancel As Integer)
> On Error GoTo Err_Form_Delete
>
> ' A custom Ok to Delete Member Msg Box is done in the BeforeDelConfirm
> event on the Form
> ' Properties. The BeforeDelConfirm event is processed as a result of
> running the
> ' acCmdDeleteRecord.
> '
> ' If the user agrees to the ok to delete, then the member record is
> deleted.
> ' If the user does not agree to the ok to delete, then the member
> record
> is restored.
>
> Dim sSp6 As String
> Debug.Print ("Form_Delete")
>
> sSp6 = " " ' 6 spaces
> gstrMemName = "'" & [FirstName] & " " & [LastName] & "'"
>
> gstrMbTitle = "Ok to Delete Member?"
> glngMbButton = vbYesNo + vbExclamation + vbDefaultButton2
> gstrMbText = "Do you want to delete " & gstrMemName & "?" & sSp6 &
> vbCrLf & vbCrLf
> gstrMbText = gstrMbText & "Yes = Yes, Delete the member's information."
> & vbCrLf
> gstrMbText = gstrMbText & "No = No, Do NOT delete the member's
> information"
>
> gintMbResp = MsgBox(gstrMbText, glngMbButton, gstrMbTitle) ' OK to
> delete member? Msgbox
>
> Select Case gintMbResp ' Process user
> response to Ok to Delete?
> Case vbYes ' User click
> YES,
> delete member
> Cancel = False
> Case Else
> Cancel = True ' Setting
> Cancel
> = True cancels the
> glngMbButton = vbOKOnly + vbInformation ' Delete event!
> Display msg to let
> gstrMbText = gstrMemName & " was NOT been deleted" '
> user know.
> gstrMbTitle = "Member NOT Deleted"
> gintMbResp = MsgBox(gstrMbText, glngMbButton, gstrMbTitle)
> End Select
>
> Exit_Form_Delete:
> Exit Sub
>
> Err_Form_Delete:
> Call BaseUtil.Dsp_Err_Msg(Err.Number, Err.Description, "Form_Delete ")
> Resume Exit_Form_Delete
>
> End Sub
>
> --
> Dennis
Similar Threads
