What's wrong with my delete code?

[SteveC24]

Hello,

I am using this code on my delete buttons (throughout the DB):

 Private Sub cmdDelete_Click()
DoCmd.SetWarnings False
    If MsgBox("This equipment record will be irreversibly deleted.  Are you sure you wish to continue?", vbCritical + vbYesNo, "Delete Equipment Record") = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord
    End If
cmdDelete_Click_Exit:
    DoCmd.SetWarnings True
    Exit Sub
    
cmdDelete_Click_Err:
    If Err = 2046 Then 'The command or action 'Delete Record' isn't available now
        Exit Sub
    ElseIf Err = 2501 Then 'The Delete Record action was canceled
        Exit Sub
    Else
        MsgBox Err.Number & " - " & Err.Description
        Resume cmdDelete_Click_Exit
    End If
End Sub

It works fine if the database window is shown, but if I open the database normally (without pressing shift on startup - so no db window is shown, and standard tooldbars are disabled) it doesn't work, coming up with:

Runtime error 2046:
The command or action 'DeleteRecord' isn't available now.

Deletions are allowed in the properties box, so there is no reason I can see why it doesn't work.

Help!?!

 

[Rich]

It doesn't work because you've disabled the toolbar, enable it or run a delete query insted

 

[SteveC24]

Thank you.

I would prefer not to enable the toolbars if I can help it.

I'm afraid I am not fantastically well versed in delete queries, I have just had a look, but cannot figure out how to make it only delete the selected record.

Alternatively, is there a way to make my original code work by simply adding the delete button to my existing menu bar, or does it have to be the whole standard toolbar?

Thanks

 

[ghudson]

I recognize my code [I applogize if I am wrong] for I use that all the time in my forms.
I always have all of the menu bars and tool bars hidden and I never have any prolems
with deleting records with DoCmd.RunCommand acCmdDeleteRecord.
I use the DoCmd.RunCommand methods quite often without any problems with all tool
& menu bars hidden [disabled]. Are you sure that the current record is selected so
that it can be deleted? Can you press the Delete key to delete the current record?
Also, I noticed that you were missing the On Error GoTo err_cmdDelete_Click
line in your code. You should compile your VBA to check for errors. Be careful when
your turn the warnings off for you need to ensure that they are turned back on if
the code fails. Maybe this will help since it will select the current record then delete it...

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
    
    DoCmd.SetWarnings False
    
    If MsgBox("This equipment record will be irreversibly deleted.  Are you sure you wish to continue?", vbCritical + vbYesNo, "Delete Equipment Record") = vbYes Then
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdDeleteRecord
    Else
        MsgBox "Delete aborted"
    End If
    
Exit_cmdDelete_Click:
    DoCmd.SetWarnings True
    Exit Sub
    
Err_cmdDelete_Click:
    If Err = 2046 Then 'The command or action 'Delete Record' isn't available now
        DoCmd.SetWarnings True
        Exit Sub
    ElseIf Err = 2501 Then 'The Delete Record action was canceled
        DoCmd.SetWarnings True
        Exit Sub
    Else
        MsgBox Err.Number & " - " & Err.Description
        Resume Exit_cmdDelete_Click
    End If
    
End Sub

HTH

 

[SteveC24]

Hello ghudson,

I put that code in, but still get exactly the same error message???

I really don't understand why! What possible ways can it be stopped, and I will go and double check?

Thank you!

 

[ghudson]

Odd that you are getting the runtime error 2046 since you are trapping for it. I added the trap for 2046 to account for the bozos who hit my custom delete button yet they are on a new [unsaved] record.

Can you post a sample of your db so that I can play with it. I am using Access 97.

 

[SteveC24]

ok - this is weird!

Whilst making the sample database for you, I just imported the necessary table and form for you to see, to keep the size down, and it now works?!?

Only in the sample! I have no idea what is going on now!

 

[SteveC24]

I have also just tried making a copy of the database, deleting all the unnecessary forms/tables/macros etc, and that also works fine!

WHAT is going on!?!

 

[ghudson]

In your db, can you delete records by pressing the keyboards Delete key?

Is the forms record source a simple query?

Is it possible that there is a lock on the record source that will not allow deletions?

For the heck of it...import all of your db objects into a new db.

 

[SteveC24]

OK - problem source identified!

It is because there is always another form open. I have a "background" form, which just displays some simple instructions (telling the user to use the menu bar to navigate around), and it displays the time.

IN the samples I have been creating for you, I left that form out, so the form in question was running by itself.

Why would that make a difference?

 

[SteveC24]

I have fiddled and faddled, and decided that it is only that one "background" form.

I have tried deleting with a different form open as well, and that works fine.

Most strange! What could cause this background form to stop other forms from deleting?

Thanks!

 

[Rich]

Is the form your teying to delete records from A PopUp ?

 

[SteveC24]

Nope, no popup forms being used

 

[Mile-O]

Just as an aside:

If MsgBox("This equipment record will be irreversibly deleted.  Are you sure you wish to continue?", vbCritical + vbYesNo, "Delete Equipment Record") = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord
    End If

You would put the DoCmd.SetWarnings immediately on either side of the DoCmd.RunCommand acCmdDeleteRecord statement.

If MsgBox("This equipment record will be irreversibly deleted.  Are you sure you wish to continue?", vbCritical + vbYesNo, "Delete Equipment Record") = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True
    End If

 

[SteveC24]

Hello everyone,

Thank you very much for your help! It is greatly appreciated!

I have stopped using that form now, and everything is hunky-dorey!
I have also moved that setwarnings as per Mile-o-phile's suggestion!
Thanks again!