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

Posted on 2006-04-26
Medium Priority
Last Modified: 2007-12-19
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
 DoCmd.RunCommand acCmdDeleteRows
Forms![purchaseorders]![PurchaseOrdersSubform].Form.AllowDeletions = False
...plus some other code....
Question by:Ricky11
  • 2
LVL 11

Expert Comment

ID: 16544637
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


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


LVL 58

Accepted Solution

harfang earned 2000 total points
ID: 16549990

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

Try this:

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

But the really simple solution is just:



Author Comment

ID: 16585046
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

 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.


LVL 58

Expert Comment

ID: 16588042

You apparently haven't read my comment at {http:#16549990}. You are still using acCmdDeleteRows, which is wrong.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question