how to write VBA for DELETE RECORD? - ADO DAO RDO RDS

This is a discussion on how to write VBA for DELETE RECORD? - ADO DAO RDO RDS ; 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 ...

+ Reply to Thread
Results 1 to 4 of 4

how to write VBA for DELETE RECORD?

  1. Default how to write VBA for DELETE RECORD?

    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!

  2. Default Re: how to write VBA for DELETE RECORD?

    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\#")

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "DanNy" <DanNy@discussions.microsoft.com> wrote in message
    news:54E1D3EE-7E0E-49B5-97D3-142802D82381@microsoft.com...
    >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!




  3. Default Re: how to write VBA for DELETE RECORD?

    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
    ..

    "DanNy" <DanNy@discussions.microsoft.com> wrote in message
    news:54E1D3EE-7E0E-49B5-97D3-142802D82381@microsoft.com...
    >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!




  4. Default RE: how to write VBA for DELETE RECORD?

    You don't need to delete the form to delete the record it's displaying.

    The access button wizard will automatically create a record delete function
    for you.

+ Reply to Thread

Similar Threads

  1. Sub-Form Delete Record
    By Application Development in forum ADO DAO RDO RDS
    Replies: 1
    Last Post: 12-10-2007, 11:57 AM
  2. Can't delete record
    By Application Development in forum DOTNET
    Replies: 0
    Last Post: 12-03-2007, 09:18 PM
  3. Delete the last record
    By Application Development in forum ADO DAO RDO RDS
    Replies: 2
    Last Post: 11-15-2007, 02:45 AM
  4. Delete Record Code
    By Application Development in forum ADO DAO RDO RDS
    Replies: 2
    Last Post: 08-29-2007, 07:30 PM
  5. Delete record
    By Application Development in forum ADO DAO RDO RDS
    Replies: 2
    Last Post: 08-22-2007, 10:08 PM