Delete Command has error when deleting multiple records.

[busterbaxter]

Hi All,

I got a multiple delete working here but for some reason I occassionally get this error. The way the delete works is there is a text box where the user enters the quantity to delete. If it is blank it will delete 1 item. It never fails when I delete just 1 item but if I delete more than one item it fails but only some times. Here is the error:

"Run-time error '2046':

The command or action 'DeleteRecord' isn't available now."

Here is my code:

Private Sub cmdMultiDeleteR ecord_Click()
Dim DeletedQty As Integer

If Me.Dirty Then Me.Dirty = False

DoCmd.SetWarnin gs (WarningsOff)

If Not IsNull(Me.Numbe rDeleted) Then
DeletedQty = Me.NumberDelete d
Else
DeletedQty = 1
End If

If MsgBox("Are you sure you want to delete " & DeletedQty & " record(s)?", vbQuestion + vbYesNo, "Confirm Delete?") = vbYes Then

For I = 1 To DeletedQty
DoCmd.RunComman d acCmdDeleteReco rd *** Error Occurs Here ****
RunCommand acCmdSelectReco rd

Next I

MsgBox DeletedQty & " record(s) deleted!", vbOKOnly, "Record Deleted"

End If

Exit_cmdMultiDe leteRecord_Clic k:

DoCmd.DoMenuIte m acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.SetWarnin gs (WarningsOn)

NumberDeleted.V alue = Null

End Sub


Can somebody help me out and find out why it's not working?

Thanks,

Kevin

[puppydogbuddy]

Kevin,
You have the delete and select in the wrong order....select comes before delete.

For I = 1 To DeletedQty
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord 

Next I

[busterbaxter]

Thanks a lot! That fixed my problem. I love this forum.

[busterbaxter]

The problem still exists but only when I'm deleting a large quantity like 200. Anything under 100 seems to work fine.

Any other ideas?

Kevin

[puppydogbuddy]

I thought you were using this code to delete few records that were being selected by the user. If you are trying to do a bulk delete, create a delete query that places any constraints for the delete in the criteria row of the query grid. Then run the query using the DoCmd.OpenQuery method as needed. If you need more help on this, let me know.

[busterbaxter]

I'm pretty new at this stuff, so I'm going to need more help. So right now my form has a list box that lists all of the records in my table. Once I select one record I delete the selected record and then the next record. This just continues until the number of records is deleted. My records are sorted by Item description so you can delete mutliple records of one item.

As for the query, would i just create a query that list the records by item number?

Can you give me an example of the code for the open query cmd?

Thanks,

Kevin

[puppydogbuddy]

I'm pretty new at this stuff, so I'm going to need more help. So right now my form has a list box that lists all of the records in my table. Once I select one record I delete the selected record and then the next record. This just continues until the number of records is deleted. My records are sorted by Item description so you can delete mutliple records of one item.

As for the query, would i just create a query that list the records by item number?

Can you give me an example of the code for the open query cmd?

Thanks,

Kevin

You can execute a direct sql statement or save the sql as a query by pasting the sql (without the ending ;) to the sql view of a query and save as qryDelete.

Here is an example of executing an sql statement directly
. The syntax assumes you have a link named [ID] between the table and the form that is numeric....also , note that you don't need the where clause if you are deleting all the records from the table.

DoCmd.RunSQL ("Delete * from [YourTable] where [YourTable].[ID] >= " & Me!ID & ";")

Me.Refresh

_______________ _______________ _______________ _

Here is example of executing a saved query via the openquery method:

DoCmd.OpenQuery "qryDelete", acNormal, acEdit

[busterbaxter]

Thanks for your help,

I got the DCmd.OpenQuery to work. The only problem I have is that when I open the query the data sheet view opens and then does the delete. I'm able to close the form after it's done but this looks bad to the user because the datasheet opens over the form. Is there anyway to keep the datasheet view in the background so the user can't see it.

Thanks again,

Kevin

[puppydogbuddy]

Thanks for your help,

I got the DCmd.OpenQuery to work. The only problem I have is that when I open the query the data sheet view opens and then does the delete. I'm able to close the form after it's done but this looks bad to the user because the datasheet opens over the form. Is there anyway to keep the datasheet view in the background so the user can't see it.

Thanks again,

Kevin

Kevin,

Yes, there is a way. Change your OpenQuery syntax to the following:

DoCmd.OpenQuery "yourQueryName"

You can also avoid getting any confirmations or prompts by doing the following:

DoCmd.SetWarnings False
DoCmd.OpenQuery "YourQryName"
DoCmd.SetWarnings True

[busterbaxter]

Hi,

I tried DoCmd.OpenQuery "qryAssetListBo x" and the query still shows up.

Kevin

[puppydogbuddy]

Hi,

I tried DoCmd.OpenQuery "qryAssetListBo x" and the query still shows up.

Kevin

Hi Kevin,
That should have worked.

Did you try the code I gave you that includes the setWarnings (below)?

DoCmd.SetWarnings False
DoCmd.OpenQuery "YourQryName"
DoCmd.SetWarnings True

If the above syntax does not work, then use this syntax. The thing that bothers me is that I can't understand why the above syntax did not work. There must be something else going on in your code that is effecting this.

    Application.Echo False
    DoCmd.Hourglass True
    DoCmd.OpenQuery "qryAssetListBox"
    Application.Echo True
    DoCmd.Hourglass False

[busterbaxter]

I tried all of the solutions and the qry still pops up over the form. Here is my code for the delete. I'm using access 2007, not sure if that has anything to do with it.

Private Sub cmdMultiDeleteRecord_Click()
Dim DeletedQty As Integer

If Me.Dirty Then Me.Dirty = False

DoCmd.SetWarnings (WarningsOff)

If Not IsNull(Me.NumberDeleted) Then
DeletedQty = Me.NumberDeleted
Else
DeletedQty = 1
End If

If MsgBox("Are you sure you want to delete " & DeletedQty & " record(s)?", vbQuestion + vbYesNo, "Confirm Delete?") = vbYes Then

For I = 1 To DeletedQty
      
      DoCmd.OpenQuery "qryAssetListBox"
      RunCommand acCmdSelectRecord
      DoCmd.RunCommand acCmdDeleteRecord

Next I

Me.Requery

End If

MsgBox DeletedQty & " record(s) deleted!", vbOKOnly, "Record Deleted"

Exit_cmdMultiDeleteRecord_Click:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.SetWarnings (WarningsOn)

NumberDeleted.Value = Null


End Sub

Thanks again,

Kevin

[puppydogbuddy]

Kevin,
Boy are you confused. Not only did you mix code I gave you for a"bulk" delete, you did a select query instead of a delete query. I will get back to you with revised code as soon as I can (by tomorrow at the latest).

[puppydogbuddy]

Kevin,
After looking at your original code, I don't see how it ever worked for more than one record because you left out the "DoCmd.GoToReco rd , , acNext" as shown in the revised code below. Try the code below. If it works the way you want, then you won't need the bulk delete query that I mentioned previously. Let me know if this code does what you intended.

Private Sub cmdMultiDeleteRecord_Click()
Dim DeletedQty As Integer

If Me.Dirty Then Me.Dirty = False

DoCmd.SetWarnings (WarningsOff)

If Not IsNull(Me.NumberDeleted) Then
       DeletedQty = Me.NumberDeleted
Else
        DeletedQty = 1
End If

If MsgBox("Are you sure you want to delete " & DeletedQty & " record(s)?", vbQuestion + vbYesNo, "Confirm Delete?") = vbYes Then

      For I = 1 To DeletedQty
           DoCmd.RunCommand acCmdSelectRecord 
           DoCmd.RunCommand acCmdDeleteRecord  
           If I < DeletedQty Then
	           DoCmd.GoToRecord , , acNext
           Else
	           Exit For
           End If
     Next I

     MsgBox DeletedQty & " record(s) deleted!", vbOKOnly, "Record Deleted"

End If

Exit_cmdMultiDeleteRecord_Click:
      Me.Refresh
      DoCmd.SetWarnings (WarningsOn)
      NumberDeleted.Value = Null

End Sub

[busterbaxter]

Thanks for the code,

I still get the same error , The command or action 'DeleteRecord' isn't available.

I did use my old code with the open query and added your solution to not show the query and it seemed to work.

I do like the way your code works though. You can actually see the item being deleted from the form.

Is there any other reason I would be getting this Run-time error '2046' The command or action 'DeleteRecord' isn't available now.

Thanks for all of your help. I really appreciate it.

Kevin