Runtime Error '2501'

[mjoachim]

I've reviewed numerous solutions to others with the same error and for some reason I just can't seem to get past my issue... please help!

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.EquipNumber) Then
        Do
            InputEq = InputBox("An equipment number is required.  Please enter a value:", "Missing Equipment Number")
                If InputEq = "" Then
                    If MsgBox("An Equipment Number is required to save." & vbCrLf & _
                    "Would you prefer to delete the current record?", vbCritical + vbYesNo, "Delete Record?") = vbYes Then
                        DoCmd.RunCommand acCmdCloseWindow
                    End If
                End If
        Loop Until InputEq <> ""
        
        Me.EquipNumber = InputEq
    End If
                    
                    
    If IsNull(Me.txtUser) Then
        Me.txtUser = Environ$("Username")
        Me.txtEnterDate = Date
    End If
    
    
End Sub

The objective is to simply verify that a required field has been entered prior to saving/closing the form. If the field is null, it prompts the user for a value. If they click OK or cancel, a secondary prompt asks if the record should be deleted. If they select "No", the code properly loops back to the input request, but if they select "Yes", I get the Runtime error 2501 "The runcommand was cancelled".

All I want it to do is close the active form without saving a record.

[jforbes]

I don't think you can do what you are attempting in the manner you are attempting to do it in Access.

The BeforeUpdate Event provides a last validation check before saving a record. The BeforUpdate Event is nestled into Access' Save Process and provides an option to Abort the Save by setting Cancel to True. So in practice, Access starts to save, sees that there is some code that needs to be ran and runs it, then it checks the value of Cancel and if it hasn't changed it performs the actual Save, then an Endpoint is encountered and control is returned back to the UI. Access is written so that there is only one exit point out of the Save Process, even though there can be some user code run as part of that process, the process needs to finish at the one and only endpoint.

In your code example, you are trying to effectively create a different endpoint with the DoCmd.RunComman d acCmdCloseWindo w and Access won't have it. So in the BeforeUpdate Event, you really only have the option to Cancel the Save.

I think you are going to have to split the Validation check and the Cancel of save. ...Maybe by placing a Cancel button on the screen.

[mjoachim]

jforbes: thanks for that bit of info. Based on what you said, I re-examined my code and moved a few things around so that I was able to use Cancel. The action functions as I would like it to now, but the default Access prompt, "You can't save this record at this time." still pops up prior to actually closing the form. Is there a good way to silence this prompt or to preemptively select yes?

Private Sub Form_BeforeUpdate(Cancel As Integer)
   
    If IsNull(Me.EquipNumber) Then
        Do
            InputEq = InputBox("An equipment number is required.  Please enter a value:", "Missing Equipment Number")
                If InputEq = "" Then
                    If MsgBox("An Equipment Number is required to save." & vbCrLf & _
                    "Would you prefer to delete the current record?", vbCritical + vbYesNo, "Delete Record?") = vbYes Then
                        Cancel = True
                        InputEq = "1"
                    End If
                Else
                    Me.EquipNumber = InputEq
                End If
        Loop Until InputEq <> ""

    End If
                    
End Sub

[jforbes]

You can attempt to put a Me.Undo after Cancel=True to back out the user's changes:

...
Cancel = True
Me.Undo
InputEq = "1"
...

I'm not sure this is the best solution, but it may work for you. If you continue to have difficulty in getting Access to do what you want, it may be better to take a step back and look at your approach again. Access has a particular way it goes about letting a user enter data and if your idea for an application differs too much from Access' approach, you'll either need to change your approach to a more Access centric way or you'll want take much more control and use a Form with unbound controls to get the user input and then when the data is fully validated, build up an insert statement and insert the records yourself instead of letting Access do it.

Just wanted to give you fair warning of the path ahead of you and some options.

[mjoachim]

Thanks again. Adding Undo didn't appear to change anything. I had already started thinking of other approaches as there is always more than one way to accomplish the same task. While reading your comments again, I realized that I had some code elsewhere that was created from a system macro converted to VBA that has essentially the same task, but without the prompt. I was able to borrow that chunk to accomplish exactly what I want.

I'm not going to pretend to fully understand the reason this works, but here is what I was able to do:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   
    If IsNull(Me.EquipNumber) Then
        Do
            InputEq = InputBox("An equipment number is required.  Please enter a value:", "Missing Equipment Number")
                If InputEq = "" Then
                    If MsgBox("An Equipment Number is required to save." & vbCrLf & _
                    "Would you prefer to delete the current record?", vbCritical + vbYesNo, "Delete Record?") = vbYes Then
                        With CodeContextObject
                            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 Not .Form.Dirty) Then
                                Beep
                            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
                        End With

                        DoCmd.RunCommand acCmdCloseWindow
                        InputEq = "1"
                    End If
                Else
                    Me.EquipNumber = InputEq
                End If
        Loop Until InputEq <> ""

    End If
                    
End Sub

Thanks again!