Cannot delete selected rows with push of a button in subform datasheet view.

I have set the allowdeletions propety default to false, as I dont want users to highlight and hit delete by them selves.

I wish to do the following

have a button that does this :

but i get a runtime error 2406
"The comamnd or action deleterows isn't available now"  any ideas...


Forms![purchaseorders]![PurchaseOrdersSubform].Form.AllowDeletions = True
Forms![purchaseorders]![PurchaseOrdersSubform].Form![ProductID].SetFocus
 DoCmd.RunCommand acCmdDeleteRows
Forms![purchaseorders]![PurchaseOrdersSubform].Form.AllowDeletions = False
...plus some other code....
Ricky11Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RgGray3Commented:
Please clarify...  you want a button to do the deletion?

If so simply place a button on the detail record
and write a Delete SQL statement and build the where portion in the on click

Assuming that yor details are in a table called PurchaseOrdersDetails it would be something like the following

Dim strSQL as string

strSQL = "Delete * from PurchaseOrdersDetails Where InvoiceNum = " & Me.invoiceNum & " AND ProductID = " & Me.ProductID & ";"

DoCmd.RunSQL strSQL

Me.requery

The form properties have no control to prevent you from running SQL

Rich

harfangCommented:
Hello,

You are using the wrong constant. It's acCmdDeleteRecord, not acCmdDeleteRows...

Try this:

    Me.PurchaseOrdersSubform.SetFocus
    With Me.PurchaseOrdersSubform.Form
        .AllowDeletions = True
        RunCommand acCmdDeleteRecord
        .AllowDeletions = False
    End With

But the really simple solution is just:

    Me.PurchaseOrdersSubform.Form.Recordset.Delete
    Me.PurchaseOrdersSubform.SetFocus

Cheers!
(°v°)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ricky11Author Commented:
Still cant manage to do it.

I need to delete only the selected row in the the subform datasheet view.


the delete button is comming from another form on the main form..

thus in my original question i had done as follows


Forms![purchaseorders]![PurchaseOrdersSubform].Form![ProductID].SetFocus
 DoCmd.RunCommand acCmdDeleteRows
Forms![purchaseorders]![PurchaseOrdersSubform].Form.AllowDeletions = False

i can see that the focus has now change to the correct row on the datasheet subform but i am still getting the same error deleting it... and i dont think it has anything to do with the allow deletions property... cuz i change it to true in the original subform..

i will try and see if i can use a update query instead.

tks.

harfangCommented:
Hello,

You apparently haven't read my comment at {http:#16549990}. You are still using acCmdDeleteRows, which is wrong.
(°v°)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.