D
Dennis
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
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