Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-10-2004, 12:34 PM   #1
SteveClarkson
Newly Registered User
 
Join Date: Feb 2003
Location: Kent, SE UK
Posts: 435
Thanks: 0
Thanked 2 Times in 2 Posts
SteveClarkson is on a distinguished road
Question What's wrong with my delete code?

Hello,

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

Code:
 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!?!

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Steve
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
SteveClarkson is offline   Reply With Quote
Old 01-10-2004, 12:37 PM   #2
Rich
Guest
 
Posts: n/a
It doesn't work because you've disabled the toolbar, enable it or run a delete query insted
  Reply With Quote
Old 01-10-2004, 12:50 PM   #3
SteveClarkson
Newly Registered User
 
Join Date: Feb 2003
Location: Kent, SE UK
Posts: 435
Thanks: 0
Thanked 2 Times in 2 Posts
SteveClarkson is on a distinguished road
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

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Steve
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
SteveClarkson is offline   Reply With Quote
Old 01-10-2004, 01:16 PM   #4
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 83 Times in 48 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
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...
Code:
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
__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......

Last edited by ghudson; 01-10-2004 at 01:24 PM.
ghudson is offline   Reply With Quote
Old 01-10-2004, 01:23 PM   #5
SteveClarkson
Newly Registered User
 
Join Date: Feb 2003
Location: Kent, SE UK
Posts: 435
Thanks: 0
Thanked 2 Times in 2 Posts
SteveClarkson is on a distinguished road
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!
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Steve
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
SteveClarkson is offline   Reply With Quote
Old 01-10-2004, 01:26 PM   #6
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 83 Times in 48 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
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.
__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......

Last edited by ghudson; 01-10-2004 at 01:28 PM.
ghudson is offline   Reply With Quote
Old 01-10-2004, 01:34 PM   #7
SteveClarkson
Newly Registered User
 
Join Date: Feb 2003
Location: Kent, SE UK
Posts: 435
Thanks: 0
Thanked 2 Times in 2 Posts
SteveClarkson is on a distinguished road
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!

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Steve
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
SteveClarkson is offline   Reply With Quote
Old 01-10-2004, 01:37 PM   #8
SteveClarkson
Newly Registered User
 
Join Date: Feb 2003
Location: Kent, SE UK
Posts: 435
Thanks: 0
Thanked 2 Times in 2 Posts
SteveClarkson is on a distinguished road
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!?!
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Steve
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
SteveClarkson is offline   Reply With Quote
Old 01-10-2004, 01:37 PM   #9
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 83 Times in 48 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
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.
__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......
ghudson is offline   Reply With Quote
Old 01-10-2004, 01:53 PM   #10
SteveClarkson
Newly Registered User
 
Join Date: Feb 2003
Location: Kent, SE UK
Posts: 435
Thanks: 0
Thanked 2 Times in 2 Posts
SteveClarkson is on a distinguished road
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?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Steve
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
SteveClarkson is offline   Reply With Quote
Old 01-10-2004, 02:05 PM   #11
SteveClarkson
Newly Registered User
 
Join Date: Feb 2003
Location: Kent, SE UK
Posts: 435
Thanks: 0
Thanked 2 Times in 2 Posts
SteveClarkson is on a distinguished road
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!
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Steve
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
SteveClarkson is offline   Reply With Quote
Old 01-10-2004, 04:27 PM   #12
Rich
Guest
 
Posts: n/a
Is the form your teying to delete records from A PopUp ?
  Reply With Quote
Old 01-11-2004, 02:34 AM   #13
SteveClarkson
Newly Registered User
 
Join Date: Feb 2003
Location: Kent, SE UK
Posts: 435
Thanks: 0
Thanked 2 Times in 2 Posts
SteveClarkson is on a distinguished road
Nope, no popup forms being used
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Steve
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
SteveClarkson is offline   Reply With Quote
Old 01-11-2004, 09:19 AM   #14
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 117 Times in 107 Posts
Mile-O will become famous soon enough
Just as an aside:

Code:
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.

Code:
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
Mile-O is offline   Reply With Quote
Old 01-11-2004, 09:34 AM   #15
SteveClarkson
Newly Registered User
 
Join Date: Feb 2003
Location: Kent, SE UK
Posts: 435
Thanks: 0
Thanked 2 Times in 2 Posts
SteveClarkson is on a distinguished road
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!

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Steve
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
SteveClarkson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 06:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World