By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,494 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Delete Command has error when deleting multiple records.

P: 24
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 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 acCmdDeleteRecord *** Error Occurs Here ****
RunCommand acCmdSelectRecord

Next I

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

End If

Exit_cmdMultiDeleteRecord_Click:

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

NumberDeleted.Value = Null

End Sub


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

Thanks,

Kevin
Oct 31 '08 #1
Share this Question
Share on Google+
16 Replies


puppydogbuddy
Expert 100+
P: 1,923
Kevin,
You have the delete and select in the wrong order....select comes before delete.
Expand|Select|Wrap|Line Numbers
  1. For I = 1 To DeletedQty
  2. DoCmd.RunCommand acCmdSelectRecord
  3. DoCmd.RunCommand acCmdDeleteRecord 
  4.  
  5. Next I
Nov 1 '08 #2

P: 24
Thanks a lot! That fixed my problem. I love this forum.
Nov 4 '08 #3

P: 24
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
Nov 4 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
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.
Nov 4 '08 #5

P: 24
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
Nov 4 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
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.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL ("Delete * from [YourTable] where [YourTable].[ID] >= " & Me!ID & ";")
  2.  
  3. Me.Refresh
______________________________________________

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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "qryDelete", acNormal, acEdit
Nov 5 '08 #7

P: 24
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
Nov 5 '08 #8

puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "yourQueryName" 

You can also avoid getting any confirmations or prompts by doing the following:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.OpenQuery "YourQryName"
  3. DoCmd.SetWarnings True
Nov 5 '08 #9

P: 24
Hi,

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

Kevin
Nov 5 '08 #10

puppydogbuddy
Expert 100+
P: 1,923
Hi,

I tried DoCmd.OpenQuery "qryAssetListBox" 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)?
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.OpenQuery "YourQryName"
  3. 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.
Expand|Select|Wrap|Line Numbers
  1.     Application.Echo False
  2.     DoCmd.Hourglass True
  3.     DoCmd.OpenQuery "qryAssetListBox"
  4.     Application.Echo True
  5.     DoCmd.Hourglass False
Nov 5 '08 #11

P: 24
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Private Sub cmdMultiDeleteRecord_Click()
  4. Dim DeletedQty As Integer
  5.  
  6. If Me.Dirty Then Me.Dirty = False
  7.  
  8. DoCmd.SetWarnings (WarningsOff)
  9.  
  10. If Not IsNull(Me.NumberDeleted) Then
  11. DeletedQty = Me.NumberDeleted
  12. Else
  13. DeletedQty = 1
  14. End If
  15.  
  16. If MsgBox("Are you sure you want to delete " & DeletedQty & " record(s)?", vbQuestion + vbYesNo, "Confirm Delete?") = vbYes Then
  17.  
  18. For I = 1 To DeletedQty
  19.  
  20.       DoCmd.OpenQuery "qryAssetListBox"
  21.       RunCommand acCmdSelectRecord
  22.       DoCmd.RunCommand acCmdDeleteRecord
  23.  
  24. Next I
  25.  
  26. Me.Requery
  27.  
  28. End If
  29.  
  30. MsgBox DeletedQty & " record(s) deleted!", vbOKOnly, "Record Deleted"
  31.  
  32. Exit_cmdMultiDeleteRecord_Click:
  33.  
  34. DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  35. DoCmd.SetWarnings (WarningsOn)
  36.  
  37. NumberDeleted.Value = Null
  38.  
  39.  
  40. End Sub
  41.  
  42.  
Thanks again,

Kevin
Nov 5 '08 #12

puppydogbuddy
Expert 100+
P: 1,923
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).
Nov 6 '08 #13

puppydogbuddy
Expert 100+
P: 1,923
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.GoToRecord , , 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdMultiDeleteRecord_Click()
  2. Dim DeletedQty As Integer
  3.  
  4. If Me.Dirty Then Me.Dirty = False
  5.  
  6. DoCmd.SetWarnings (WarningsOff)
  7.  
  8. If Not IsNull(Me.NumberDeleted) Then
  9.        DeletedQty = Me.NumberDeleted
  10. Else
  11.         DeletedQty = 1
  12. End If
  13.  
  14. If MsgBox("Are you sure you want to delete " & DeletedQty & " record(s)?", vbQuestion + vbYesNo, "Confirm Delete?") = vbYes Then
  15.  
  16.       For I = 1 To DeletedQty
  17.            DoCmd.RunCommand acCmdSelectRecord 
  18.            DoCmd.RunCommand acCmdDeleteRecord  
  19.            If I < DeletedQty Then
  20.                DoCmd.GoToRecord , , acNext
  21.            Else
  22.                Exit For
  23.            End If
  24.      Next I
  25.  
  26.      MsgBox DeletedQty & " record(s) deleted!", vbOKOnly, "Record Deleted"
  27.  
  28. End If
  29.  
  30. Exit_cmdMultiDeleteRecord_Click:
  31.       Me.Refresh
  32.       DoCmd.SetWarnings (WarningsOn)
  33.       NumberDeleted.Value = Null
  34.  
  35. End Sub
  36.  
Nov 7 '08 #14

P: 24
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
Nov 7 '08 #15

puppydogbuddy
Expert 100+
P: 1,923
It could be lines 8 thru 12 of the above code that is causing your problem.

Change this:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.NumberDeleted) Then 
  2.        DeletedQty = Me.NumberDeleted 
  3. Else 
  4.         DeletedQty = 1 
  5. End If 
To this:
Expand|Select|Wrap|Line Numbers
  1. If nz(Me.NumberDeleted,0) > 1 Then 
  2.        DeletedQty = Me.NumberDeleted 
  3. ElseIf nz(Me.NumberDeleted,0) = 1 Then  
  4.         DeletedQty = 1 
  5. Else
  6.         DeletedQty = 0             'no records to delete
  7.         Exit Sub
  8. End If 
Nov 7 '08 #16

P: 24
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
Nov 7 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.