CODEWITHSUNDEEP
formsms-accessvba
plateriot
plateriot

Reputation: 431

Deleting the first record on continuous form in MS Access 2013

I have a "remove" button on a continuous form that performs the following SQL when clicked:

Dim strSQL As String
strSQL = "Delete SLIDE_NAME, COB_TITLE, COB_ID, COB_CATEGORY " _
      & "FROM tbl_SLIDE_LIST " _
      & "WHERE SLIDE_NAME='" & strSlideName & "' AND COB_TITLE='" & strCOB_TITLE & "';"

CurrentDb.Execute (strSQL)

The button sits on the continuous form like so: enter image description here

There must be some sort of initialization I need to perform to make this work properly -

I can delete the 2nd and 3rd records with no problem, but if I click the "remove" button on the first record, the subform becomes blank, as if I deleted all records - (which is not actually the case). Then if I reselect the "Test Slide" to look at what actually happened, I receive the error "The data has been changed" Another user edited this record and saved the changes before you attempted to save your changes.

What is happening here and what do I need to do to make this work correctly?

Upvotes: 0

Views: 379

Answers (1)

Andre
Andre

Reputation: 27634

If you delete a record with SQL, the form doesn't know about it. You need to do Me.Requery afterwards.

Much simpler would be to run DoCmd.RunCommand acCmdDeleteRecord.

From http://access.mvps.org/access/RunCommand/codeex/50-223.htm :

'Code from Microsoft Knowledge Base Adapted by Terry Wickenden
Private Sub cmdDelete_Click()

  On Error GoTo Err_cmdDelete_Click

  DoCmd.SetWarnings False
  If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") =  vbYes Then
    DoCmd.RunCommand acCmdSelectRecord ' I think this isn't needed
    DoCmd.RunCommand acCmdDeleteRecord
  End If

Exit_cmdDelete_Click:
  DoCmd.SetWarnings True
  Exit Sub

Err_cmdDelete_Click:
  MsgBox Err.Description
  Resume Exit_cmdDelete_Click

End Sub

Upvotes: 2

Related Questions