Results 1 to 11 of 11

Thread: Delete (2000)

  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete (2000)

    Hi,

    I put some VB code on the delete button to let user to use when they want to delete the record. Please see code below:

    If Not IsNull(Me![Episode ID]) Then
    Dim rst As DAO.Recordset, db As DAO.Database, strSQL As String
    Set db = CurrentDb()
    strSQL = "Select * From Service Where [Episode ID] = " & Me![Episode ID]
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If MsgBox("Delete current record?", vbYesNo, "Delete Record") = vbYes Then
    With rst
    .MoveFirst
    .Delete
    .Close
    End With
    End If
    DoCmd.GoToRecord , , acNewRec
    End If

    When I doing the test, the error message pops up:

    Run-time error '3021': No current record.

    Please help.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete (2000)

    Instead of using a lot of DAO code to delete the current record, you can simply use

    RunCommand acCmdDeleteRecord

    This is less likely to cause a conflict. If you want to suppress the built-in prompt for confirmation, you can use

    DoCmd.SetWarnings False
    RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (2000)

    Thanks, Hans.

    I tried the code and I got the following error message:

    Run-time error '2046': The command or action 'DeleteRecord' isn't available now.

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete (2000)

    Do you try to click the button while you're in a new record?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (2000)

    yes. Hans.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete (2000)

    There's not much point in deleting a new record, is there?

    If you want to avoid the error message, build an error handler for your code. See <post:=377,394>post 377,394</post:> for an explanation.

    The free add-in MZ-Tools 3.0 can add error handling to your procedures using a customizable template.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (2000)

    Thanks, Hans.

    I put the following code include error handle:

    If Not IsNull(Me![Episode ID]) Then
    Dim rst As DAO.Recordset, db As DAO.Database, strSQL As String
    On Error GoTo ErrHandler
    Set db = CurrentDb()
    strSQL = "Select * From Service Where [Episode ID] = " & Me![Episode ID]
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If MsgBox("Delete current record?", vbYesNo, "Delete Record") = vbYes Then
    With rst
    .MoveFirst
    .Delete
    .Close
    End With
    End If
    DoCmd.GoToRecord , , acNewRec
    End If
    ErrHandler:
    Select Case Err
    Case 2046
    ' Action canceled - ignore.
    Case Else
    MsgBox Err.Description, vbExclamation
    End Select

    I still get "No current record" message.

    Please Help!!!

    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete (2000)

    You shouldn't use DAO to delete the current record in a form. See <post:=608,009>post 608,009</post:> higher up in this thread.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (2000)

    Hi Hans,

    I use default CmdDeleteRecord button by following code:

    On Error GoTo Err_Command108_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    Exit_Command108_Click:
    Exit Sub

    Err_Command108_Click:
    MsgBox Err.Description
    Resume Exit_Command108_Click

    End Sub

    First I got error message "You can't save this record at this time." If I click "Delete" button again, then I can delete the record.

    Please help.

    Thanks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete (2000)

    Gary, please take the time to read my previous replies carefully.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (2000)

    Thanks, Hana.

    I solve the problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •