Delete record from datasheet with vba DatasheetDoCmd.RunCommand acCmdDeleteRecord (error 2046)

Question

Please help me!

What I have:

First, I'm using Access 2007. I have a form that include's a subform. In that subform a have a datasheet from a SQL query which get 3 fields. The first field (Supprimer) his a dummy field to display a hyperlink that will be use has a button:

SELECT "Supprimer", permis.abbreviation, permis.description
FROM permis 
INNER JOIN (clients INNER JOIN clients_permis 
  ON clients.ID=clients_permis.id_clients) 
  ON permis.ID=clients_permis.id_permis;

What I want I want my "delete" hyperlinks to delete the datasheet selected record has would pressing the "del" key would

My code for onClick event

Private Sub del_Click()

    DoCmd.RunCommand acCmdSelectRecord
    'SendKeys ("{DELETE}")       
    DoCmd.RunCommand acCmdDeleteRecord

End Sub

If I uncommented, the Sendkeys ("{DELETE}") the record is deleted, but, on my systeme this also triggers the numlock key. Who knows what it can do on other system.

the DoCmd.RunCommand acCmdDeleteRecord command provokes a Run-Time Error: 2046.

At a previous point the above code worked perfectly.

I cannot possibly recommend using SendKeys. You might look at this link if you haven't already. access-programmers.co.uk/forums/archive/index.php/t-60046.html There does seem to be a lack of conclusion about what causes this error your getting when trying to delete a record the way you are. You might try running compact and repair to see if that resolves the problem. — HK1
Are you sure your recordset is updateable? Why are you using a dummy field to delete rather than a command button? I agree that under no circumstances should you use sendkeys, nor should it be necessary. — Fionnuala
Using a command button will set the focus off the datasheet and i won't be able to use "DoCmd.RunCommand acCmdDeleteRecord". — pec
Continuous forms (as opposed to datasheet forms) allow you to include buttons although you cannot allow your users to easily move columns and apply sorting with continuous forms. — HK1

pec pec · Accepted Answer · 2011-09-06T00:17:27

As mentioned in the comments by HK1, here's what can be done (although it's workaround):

Use a continuous form instead of the datasheet.

Delete record from datasheet with vba DatasheetDoCmd.RunCommand acCmdDeleteRecord (error 2046)

1 Answers