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