Can't delete record with VBA

[Eljefegeneo]

Another mystery that is driving me crazy. To delete a record from a form, I normally use the following code which has been working just fine:

  If MsgBox("Continue with Delete?", vbYesNo) = vbNo Then
          Exit Sub
              Else
                DoCmd.RunCommand acCmdSelectRecord
                                  DoCmd.RunCommand acCmdDeleteRecord
                      End If
  DoCmd.Close acForm, "frmMyForm"

But on one form I keep getting the same error message:

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

Deletions are allowed in the property box.

This is similar to the post: http://www.access-programmers.co.uk/forums/showthread.php?t=60046
And yes, the toolbar is hidden. So I added the code to unhide it first and still get the same error message. I then first unhide the toolbar manually through code on my startup screen, but still get the error message.

In post #10 of the above reference post I realized that I have a hidden form, but, always a but, this identical code works on other forms/records.

I then tried the following:

  [FONT=&quot]strSQL = "DELETE * FROM tbl[FONT=&quot]MyTable[/FONT] WHERE ID = " & ID.Value

CurrentDB.Execute strSQL, dbFailOnError[/FONT]

[FONT=&quot]And the record is deleted just fine.

[FONT=&quot]I also tried the following which works fine:
[FONT=&quot]

DoCmd.SetWarnings False
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        DoCmd.SetWarnings True
        MsgBox "Record deleted!"

[/FONT]
[/FONT][/FONT]

Also simple macro to delete works fine.

I realize that I have in essence solved my problem and should move on, but I would like to know why the VBA code gives me the error.

 

[missinglinq]

Are you executing your original code by way of a Command Button?

Although we usually think of Forms, and even entire Databases, when we speak of corruption, Controls, such as Command Buttons, can and do become corrupted, and seem particularly susceptible to this during app development. The test/cure for this is short and simple...delete the Control and then re-create it.

Linq ;0)>

 

[Eljefegeneo]

I did exactly as you suggested and the error message still comes up. Created a new command button, set it up with the code from my first post and same error message.

 

[JHB]

Could you post a sample database in which the error still pop up?
Are you sure the form in which you want to delete a record from, has focus?

 

[gemma-the-husky]

maybe its the accmdselectrecord.

I have never needed that to delete the currently selected record.
Just the accmddeleterecord

Note that you can also simply press the del key to delete a record (if your form allows deletes). You may need to have record selectors visible to delete records, and select the record first. Not sure without trying.

 

[missinglinq]

His code, as originally posted, works correctly, so it's not the line

DoCmd.RunCommand acCmdSelectRecord

I think JHB has the right idea...we really need to get 'hands on' if we're going to be able to do any more to help.

Linq ;0)>

 

[The_Doc_Man]

If the SQL DELETE query works, it isn't the underlying record that is at fault.

If the DoCmd.DoMenuItem sequence works, it isn't the linkage between the form and the underlying record.

If the DoCmd.RunCommand sequence works on other forms, then it isn't the command.

This leaves the issue of something else in the command button's OnClick code that is blocking the action here but not elsewhere, perhaps interacting with the form's current state. This is clutching at straws, but is it remotely possible that some other part of the form's code leading up to this point has modified the record, thus making it "dirty"?? Because if I recall correctly, you can't delete a dirty record without using either a SAVE or an UNDO first. (And I'll admit that it was on an older version of Access that I learned about that restriction, so if it has been lifted in recent versions, I wouldn't necessarily know about the change.)

 

[missinglinq]

...is it remotely possible that some other part of the form's code leading up to this point has modified the record, thus making it "dirty"?? Because if I recall correctly, you can't delete a dirty record without using either a SAVE or an UNDO first...

I thought this was true, too, but in testing, in version 2007, it no longer appears to hold true!

Linq ; 0)>

 

[missinglinq]

The only way that I can have this error raised, using the scenario as given to us, is if the Form is Read-Only. Can you edit existing Records or add a New Record?

Linq ;0)>

 

[The_Doc_Man]

Ah, well - another restriction lifted. I'm glad you remembered that it used to be that way even if it isn't now. Helps to know I haven't TOTALLY lost my mind, just bits and pieces.

But your question is relevant. If the form's properties say "Delete Allowed" but the form was opened by a command that says "acReadOnly" (or is it dbReadOnly... one of those two, certainly) then it overrides the "Allow...." properties.

 

[Eljefegeneo]

Thank you all for your input. Too reiterate the problem on this post, (1) JHB: Yes the form has focus and the command button is on the form. (2) Gemma-the-Husky: I did try it without the select record command with any difference in the result. (3) Missingling: I was going to post a watered down version but see below. (4) The Doc Man, I did not make any changes in the data on the form, just opened it and deleted it. And deletes are allowed.
So, thinking about the Doc Man's advice, I inserted the following before the deletion:

  If Me.Dirty Then Me.Dirty = False

And Voilà, the record is deleted. So, there must be something I am doing on the form OnCurrent event that is causing it to be "dirty".
Thank you all again for taking the time to pass on advice. Now marked "solved". And I will certainly remember this for the future.

 

[gemma-the-husky]

i would add record selectors to see when the record gets dirtied.

it might be that in your current event you are explicitly setting a field/control. Even if it isn't changed, it will still dirty the record.

 

[Eljefegeneo]

Yes I am setting some field controls. Some are visible or not depending on other fields. Then there are check box label updates depending if they are true or false. So I guess that was (is) causing the "problem".

Thank you for the suggestion, but I do not know what you mean by "add record selectors to see when the record gets dirtied". How does one do this and how does one see if the record is dirty? I have record selectors on the form header.

 

[jdraw]

eljefegeneo,

What did you do for a solution??? Why is it marked solved??
Please tell us, others may have similar issue.

 

[Eljefegeneo]

All I did was insert the If Dirty code into my old code:

    [FONT=&quot]If MsgBox("Continue with Delete?", vbYesNo) = vbNo Then[/FONT]
  [FONT=&quot]          Exit Sub[/FONT]
  [FONT=&quot]              Else[/FONT]
  [B][COLOR=red][FONT=&quot]If [FONT=&quot]Me.Di[/FONT]rty then Me.[FONT=&quot]D[/FONT]irty  False[/FONT][/COLOR][/B]
  [FONT=&quot]                DoCmd.RunCommand acCmdSelectRecord[/FONT]
  [FONT=&quot]                                  DoCmd.RunCommand acCmdDeleteRecord[/FONT]
  [FONT=&quot]                      End If[/FONT]
  [FONT=&quot]  DoCmd.Close acForm, "frmMyForm"
[/FONT]

[FONT=&quot][FONT=&quot]

[FONT=&quot]As I exp[FONT=&quot]l[FONT=&quot]ained[FONT=&quot], I must be setti[FONT=&quot]ng controls on t[FONT=&quot]he form's OnCurrent event[FONT=&quot] and this was causing the form [FONT=&quot]record to be dirty.

[FONT=&quot]A[FONT=&quot]t l[FONT=&quot]e[FONT=&quot]ast I think so. [/FONT][/FONT][/FONT][/FONT]
[/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]

 

[Eljefegeneo]

Sorry, should have been:

[FONT=&quot]If [FONT=&quot]Me.Di[/FONT]rty then Me.[FONT=&quot]D[/FONT]irty = False[/FONT]

 

[The_Doc_Man]

This sounds like something I ran into once - in a scenario where I set a value in a control to something that happened to exactly match the original value - in the Form_Current routine if I remember this correctly. The fact that the .Value and .OldValue on the control matched had nothing to do with it. The important fact was that I had placed the .Value on the left side of a value assignment statement. Once you target an update to the .Value field, it is dirty even if you didn't actually change anything.

This seems to parallel Dave's (Gemma) experience.

 

[gemma-the-husky]

Yes I am setting some field controls. Some are visible or not depending on other fields. Then there are check box label updates depending if they are true or false. So I guess that was (is) causing the "problem".

Thank you for the suggestion, but I do not know what you mean by "add record selectors to see when the record gets dirtied". How does one do this and how does one see if the record is dirty? I have record selectors on the form header.

in the form properties set recordselectors = Yes/True

You get a thin strip down the left hand side of the form.

If it's not a "dirty" record, you see a black triangle. If it's dirty/edited, it changes to a pencil. That's what I meant. Especially useful in a continuous form.

If you have record locking, and it's locked, I think you see something else.

 

[Eljefegeneo]

I see the pencil. Never knew what it was for. Now I know. Thank you. another lesson learned.