Best Practice for deleting single record from sub form

[chrisjames25]

HI I have an invoice form with a subform.

THe subform show the products i have sold on the particular invoice. It is displayed as a continuous form. What i am trying to do is be able to delete particular rows from the invoice form by clicking the command button. See image.

From help earlier today i have learnt that a recordset is not best way to do this and i think a delete query would do the job but wanting to check i havnt missed a really easy obvious method. I found following code online but it fails when i run it:

   If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Delete Current Record ?") = vbYes Then
        If Me.NewRecord Then
            DoCmd.RunCommand acCmdUndo
          Else
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdSelectRecord
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
        End If
    End If

It says the command or action deleterecord isnt available now?

Any advice much appreciated

 

[psyc0tic1]

use this

Option Compare Database
Option Explicit

Private Sub NameOfYourDeleteButton_Click()
    Dim strSQL As String
    
    strSQL = "DELETE NameOfTable.* FROM NameOfTable WHERE (((NameOfTable.NameOfPrimaryKeyField)=" & Me.NameOfPrimaryKeyField.Value & "));"
        CurrentDb.Execute strSQL, dbFailOnError
        Me.Requery

End Sub

 

[isladogs]

Chris
In your previous thread I gave you the code to delete a record in SQL
https://www.access-programmers.co.uk/forums/showpost.php?p=1599055&postcount=2

At the time you said it worked a treat.
So I'm wondering why you want to scrap that code in favour what you wrote in the first post of this thread

Richard (psychotic) has just given the same delete code again.
If the record has already been saved, the delete SQL is the correct approach.
If it hasn't yet been saved then your code makes little sense anyway as there is no record to delete.

 

[psyc0tic1]

Sorry about that Colin... I didn't know you had given him code already and the code I posted is from my database (I found it somewhere else) for a form I am working on.

 

[isladogs]

Hi Richard
No problem - it wasn't intended as a criticism of you.
I was merely pointing out to Chris that he already had the correct answer and your post reinforced that

BTW - its good to see you are now in pay back mode

 

[psyc0tic1]

Hi Richard
No problem - it wasn't intended as a criticism of you.
I was merely pointing out to Chris that he already had the correct answer and your post reinforced that

BTW - its good to see you are now in pay back mode

I always read and try to help... most times I am not smart enough to help

 

[isladogs]

That's the best way of learning and indeed its what almost all of us did at first.
Gradually I expect you'll find yourself answering more and more often.
Keep up the good work