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]

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

[puppydogbuddy]

It could be lines 8 thru 12 of the above code that is causing your problem.

Change this:

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

To this:

If nz(Me.NumberDeleted,0) > 1 Then 
       DeletedQty = Me.NumberDeleted 
ElseIf nz(Me.NumberDeleted,0) = 1 Then  
        DeletedQty = 1 
Else
        DeletedQty = 0             'no records to delete
        Exit Sub
End If 

[busterbaxter]

Thanks puppydogbuddy,

For some reason, I do not receive the errors anymore. I've been testing it a lot today. I'll make the changes if I run into the error again. Thanks again for all of your help. You were so patient with me and gave me a bunch of solutions. Thanks for your time.

Kevin