Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    48

    Unanswered: Delete current record through coding

    I am trying to execute following code to delete the current record but to my surprise, it does not delete the current record but the first record of the table

    I am fail to understand the reason for the same.

    Private Sub CmdDel_Click()
    Dim dbs As Database
    Dim rcd As Recordset
    Set dbs = CurrentDb
    Set rcd = dbs.OpenRecordset("Customer")
    Dim reply
    reply = MsgBox("Do you want to delete the record", vbYesNo)
    If reply = vbYes Then
    rcd.Delete
    Me.Refresh
    Else
    DoCmd.CancelEvent
    End If
    If rcd.EOF Then
    rcd.MoveNext
    ElseIf Not rcd.BOF Then
    rcd.MovePrevious
    End If
    Me.CmdAdd.Enabled = True
    Me.CmdAdd.SetFocus
    Me.CmdSave.Enabled = False
    Me.CmdDel.Enabled = True
    Me.CmdUndo.Enabled = True
    End Sub

    Any suggestion to overcome this problem will be highly appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is normal: you don't do anything to synchronize the current record of the recordset with the current record of the form.

    Try:
    Code:
    Dim rcd As Recordset
    Set rcd = Me.RecordsetClone
    rcd.Bookmark = Me.Bookmark
    rcd.Delete
    Or simply:
    Code:
        Me.Recordset.Delete
    Have a nice day!

  3. #3
    Join Date
    May 2008
    Posts
    48
    Thanks a lot.

    However, but I would also like to refesh the form using Me.Refresh after deletion in such a way that if cursor is in last record it moves to previous record & if curosr is in top of the record, if moves to the next record.

    But the same is not working.

    Please help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you use Me.Recordset.Delete, it's done automatically.
    Have a nice day!

  5. #5
    Join Date
    May 2008
    Posts
    48
    Despite of using following code, form is not refeshed. Cursor automatically goes to new record.

    Dim rcd As Recordset
    Set rcd = Me.RecordsetClone
    rcd.Bookmark = Me.Bookmark
    rcd.Delete

    I am attaching herewith code, kindly look & see where is the fault.

    Thanking you in appreciation of your prompt reply.
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is normal if the form allows the addition of new records. In such case, when you continuously click on "Next Record" (the button with the right arrow in the navigation buttons) you finally reach an empty (new) record.

    Here is a solution:
    Code:
        Me.Recordset.Delete
        If Me.NewRecord = True Then
            DoCmd.GoToRecord , , acLast
        End If
    Have a nice day!

  7. #7
    Join Date
    May 2008
    Posts
    48
    It appears to be some confusion at your end.

    I have never clicked any button (neither through navigation button nor Add command button) to add new record.

    When I did click Delete Button to delete the current record, though current record deleted but automatically cursor moves to the new record and the available fields are showing as Blank.

    Hope this clarifies.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The navigation buttons are standard on any form, except if you explicitly disable them by setting the NavigationButtons property of the form to False. It does not have anything to do with the way you use Access, it's simply the way Access forms work.
    Have a nice day!

Posting Permissions

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