access vba: why form_delete sub runs twice times

Question

I work with Access 97.

I have a subform attached to a parent form. The parent form is based on a database table and the subform based on select query: selects the offices that belongs to a group.

I would allow user to pull an office from his group, so I thought about using the delete button and use The event delete as below:

Private Sub Form_Delete(Cancel As Integer)

If MsgBox("are you sure?", vbYesNo, "MsgBox") = vbYes Then
   Call buro_delete
Else
    Cancel = True
End If

End Sub

Office and his group linked by a foreign key. The office will not remove physically, but we will put the foreign key column to null.

buro_delete

Private Sub buro_delete()
Dim l_adoConn As New ADODB.Connection
Dim l_adoCmd As New ADODB.Command
Dim l_ID As Long
Dim l_Sql As String

 l_adoConn.CommandTimeout = g_CmdTimeOut
 l_adoConn.Open (Allgemein.g_ADOConnStr)
 Set l_adoCmd.ActiveConnection = l_adoConn
 l_adoCmd.CommandType = adCmdText
 l_adoCmd.CommandTimeout = g_CmdTimeOut
 l_ID = Me.BTE_NR
 l_Sql = "Update BUEROS Set KL_KETTE_ID=null Where BTE_NR=" & l_ID
 l_adoCmd.CommandText = l_Sql
 l_adoCmd.Execute
End Sub

At run of the program, when I click on the delete button on any column in the subform, the msgbox is displayed the first time and it takes my answer (either yes or no) and then it appears for the second time.

I make a breakpoints and I debug, I see that the system runs the form_delete sub, call the buro_delete sub, and then returns at beginning of buro_delete sub and complete running second time.

why form_delete sub runs twice times?

any idea please

you have asked this question already? anyhow, can you share your table definitions for user, group and office? I have the feeling your DB design is incorrect. Also Form_delete will fire the number of times the records are selected to delete. From_onbeforedelete will only fire once — Krish
This has been asked before and then the question deleted by the author. it was stackoverflow.com/questions/29557179/… — Sam

kismert kismert · Accepted Answer · 2015-04-23T19:00:59

Nayomi,

Form_Delete() is not what you should use here. Looking at your Private Sub buro_delete() code, you are updating the underlying data to remove a foreign key. You are not deleting anything.

Instead, use a 'Delete' command button, and move your event code there:

' your command button
Private Sub cmdDeleteForeignKey)
   If MsgBox("are you sure?", vbYesNo, "MsgBox") = vbYes Then
      Call buro_delete
      Me.Requery
   End If
End Sub

You can also use the Form_KeyDown event to call the code when the user presses the Del key.

access vba: why form_delete sub runs twice times

1 Answers