how to write VBA for DELETE RECORD?

[DanNy]

i wrote a VBA code myself for delete function, but its not work and show me
error message "you cant delete the database object 'xxx' while it's open".
i want to delete a record in form.

this is the code i wrote:
-----------------------------------------------------------
Private Sub del_Click()
On Error GoTo Err_del_Click

Msg = "are you sureï¹–" ' Define message.
style = VbMsgBoxStyle.vbDefaultButton1 Or _
VbMsgBoxStyle.vbInformation Or VbMsgBoxStyle.vbYesNo
title = "reminder ^_^" ' Define title.
' Display message.
response = MsgBox(Msg, style, title)
If response = VbMsgBoxResult.vbYes Then ' User chose yes.

DoCmd.Close acForm, "student"
DoCmd.DeleteObject acForm, "student"

Msg = "Deletedï¹—" ' Define message.
style = VbMsgBoxStyle.vbDefaultButton1 And _
VbMsgBoxStyle.vbInformation And VbMsgBoxStyle.vbYesNo
title = "reminder ^_^" ' Define title.
' Display message.
response = MsgBox(Msg, style, title)

Else

Exit_del_Click:
Exit Sub

Err_del_Click:
MsgBox Err.Description


Resume Exit_del_Click
End If
End Sub
_____________________________________________________________

can you check the error for me? thanks a lot!

 

[Douglas J. Steele]

Your VBA code is trying to delete a form from the database. Is that what you
want? Where is del_Click running? Is it in the module associated with form
student?

I'm assuming that what you really are trying to do is delete the record for
that specific student. If that's the case, you need to run SQL to delete the
data from the table, not delete the form.

Dim strSQL As String

strSQL = "DELETE FROM [NameOfTable] " & _
"WHERE [NameOfField] = " & Value
CurrentDb.Execute strSQL, dbFailOnError

If NameOfField is a text field, you'd use

strSQL = "DELETE FROM [NameOfTable] " & _
"WHERE [NameOfField] = '" & Value & "'"

or

strSQL = "DELETE FROM [NameOfTable] " & _
"WHERE [NameOfField] = """ & Value & """"

If NameOfField is a date field, you'd use

strSQL = "DELETE FROM [NameOfTable] " & _
"WHERE [NameOfField] = " & _
Format(Value, "\#yyyy\-mm\-dd\#")

 

[John Spencer]

If you want to delete the current record from the current form then the code
might look something like

Dim tfAllowDeletions as Boolean
If Me.NewRecord = False Then
tfAllowDeletions = .AllowDeletions

If Me.AllowDeletions = False Then Me.AllowDeletions = True

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me .AllowDeletions = tfAllowDeletions

Else
Me.Undo

End If 'Delete existing records only


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..