Best Practice for deleting single record from sub form (1 Viewer)

chrisjames25

Registered User.
Local time
Today, 10:30
Joined
Dec 1, 2014
Messages
401
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:

Code:
   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
 

Attachments

  • INvoice Form.jpg
    INvoice Form.jpg
    80.9 KB · Views: 60

psyc0tic1

Access Moron
Local time
Today, 05:30
Joined
Jul 10, 2017
Messages
360
use this
Code:
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

MVP / VIP
Local time
Today, 10:30
Joined
Jan 14, 2017
Messages
18,164
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

Access Moron
Local time
Today, 05:30
Joined
Jul 10, 2017
Messages
360
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

MVP / VIP
Local time
Today, 10:30
Joined
Jan 14, 2017
Messages
18,164
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

Access Moron
Local time
Today, 05:30
Joined
Jul 10, 2017
Messages
360
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 :rolleyes:
 

isladogs

MVP / VIP
Local time
Today, 10:30
Joined
Jan 14, 2017
Messages
18,164
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 :)
 

Users who are viewing this thread

Top Bottom