Deleting Record issues 1

[bcooler]

Ok, this one is really weird. I am having problems deleting a record using my own code, so I:

1.) created a temporary command button using the wizard) to delete a record
2.) ran it a few times (worked)
3.) pasted this code into my code and ran it. The difference being that my code has a small if/then structure to ask the user if they are sure (lines 1-3 and last one before the end).

At this point, I started seeing a problem. It started giving me errors when trying to find the previous control saying it couldn't find it.

I said, "Ok, I don't really know why I need to move to the previous control anyway, so I'll comment it out." I did this, then got a Error 2046 "The command or action 'DeleteRecord' isn't available now." during the DoCmd.RunCommand acCmdDeleteRecord action.

So, I can turn on and off this error by commenting out my portions of the code (turning the code back to the code given by the wizard).

Any ideas what I am doing wrong? I need to ask before I delete the file, so I don't just want to do it as given by the command wizard.

Private Sub cmdCancel_Click()
    If MsgBox("Do you wish to cancel this entry?", vbYesNo, "Cancel Confirmation") = vbNo Then
        Exit Sub
    Else
        'On Error Resume Next
        DoCmd.GoToControl Screen.PreviousControl.Name
        'Err.Clear
        If (Not Form.NewRecord) Then
            DoCmd.RunCommand acCmdDeleteRecord
        End If
        If (Form.NewRecord And Form.Dirty) Then
            DoCmd.RunCommand acCmdUndo
        End If
        If (MacroError <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
        End If
        DoCmd.Close , ""
    End If
End Sub

 

[lameid]

I am going to assume your form is bound since it works some of the time...


Nothing is really jumping out at me especially if it is failing on...

DoCmd.runcommand acCmdDeleteRecord

Although the MacroError object is new to me and I have not looked it up yet and I expect to use Not as function with parenthesis rather than it being enclosed within parethesis with the test as you have it.

Have you tried compacting and repairing your database, compiling your code and trying again?

Next I would try opening the database with the decompile command line switch...

Msaccess.exe <Path and file of database> /decompile

The last trick in dealing with what feels like corruption is to try importing everything to a new file.

 

[bcooler]

I work on this one at night, so I'll let you know. However, a few thoughts:

1.) The code you see is all from the wizard (except those lines mentioned) so MacroError is not something I use. I imagine this part is correct since Microsoft created it?

2.) I compact and repair on close and I did close and re-open to see if this helped, but no luck. I will decompile when I get home, but I don't know.

3.) It almost seems like the focus has shifted to a different form or subform (there is one in this form). Then it has a hard time recognizing this form to delete records from. However, I added a watch in VBA and verified this is not the case.

 

[lameid]

Macroerror was apparently added in 2007.... I've been using 2010 for a while skipping Access 2007 but still use the Err object. Not sure what the reason is in having a new object or what the difference is. The name and help topic would suggest that it is for macros vs. VBA by the name. It is also possible that it shows all errors and is needed to support error trapping in macros thereby supporting more than the Err object and the help topic tech writing is less than thoughtful or informed.

About the focus.... since you are clicking something (Click event) you are moving the focus back to the appropriate form. Or I say that and there is the goto previouscontrol... I can't fathom why that is beneficial. I would kill it rather than figure out if it jumps between the main form and subform.

 

[bcooler]

No good. I decompiled it, then closed it, allowing the auto-compact and repair to work. Still the same problem. (tried it twice just to be sure, one time with manual compact and repair).

 

[bcooler]

Ok, not exactly sure why, but I figured it out (I think).

I began by opening the form directly without the automation. The code worked (even with what I added).

So, I started messing around with the automation trying to figure out how I was calling the form. I open it using a continuous form acting as a spreadsheet type form.

Private Sub txtOrderForm_ID_Click()
Dim varWhereClause As String
    varWhereClause = "OrderForm_ID = " & Me.txtOrderForm_ID
    DoCmd.OpenForm "frmOrder-Form", , , varWhereClause, acFormEdit
End Sub

I was wondering if the acFormEdit was enough to open this form correctly, and that led me to considering how it might be getting confused about which form I am trying to delete records from, so for fun, I explicitly close the previous form as below:

Private Sub txtOrderForm_ID_Click()
Dim varWhereClause As String
    varWhereClause = "OrderForm_ID = " & Me.txtOrderForm_ID
    DoCmd.OpenForm "frmOrder-Form", , , varWhereClause, acFormEdit
    DoCmd.Close acForm, "frmOrder-Incomplete"
End Sub

This seems to work, but I'm only guessing that it's no longer confused about which form it's talking to.

Thoughts?

 

[lameid]

When you say automation you simply mean you are opening the form from another form correct? Typically automation means opening an instance of another application programatically and perorming some tasks. It is possible to automate Access from another Access instance (very rarely is there cause to do so). With that cleared up...

I see no reason you should have to close an existing form... Unless you are getting hung up by the previous control thing. Like I said when you click the button you know what has the focus and those commands are going to run on the form with the focus...

Immeditately before the delete statement try adding this line...

Msgbox Screen.ActiveControl.parent.name & vbcrlf & "has the focus."

That will at least tell you what form as the focus.

 

[bcooler]

Ok, did that, but with and without closing the secondary form gives the same results (focus on main form). Not sure, but I guess what I have works.

It will probably bite me in the rear, but I'm just going forward. Thanks for your help!