Error 2501 OpenForm action cancelled

Question

I'm creating two Forms in Access to use as data entry forms. Both are unbound. The data is on assessment "measures" that are part of a standard my company follows. "Measures" is in quotes because a "measure" is often (thought not always) made up of multiple reportable values (called "submeasures" in the specs). That being the case my intent is to use one form is to enter in the portion of data that is related to the "measure", while the other captures the "submeasure" data when needed.

The plan is use a CommandButton to open the Submeasure Form when I need to capture data for a submeasure related to the parent measure. In trying to get that "Add Submeasure" button to work though I keep getting the error mentioned in the title.

Since A) the forms are unbound & B) there's not any data in any of the tables anyway I don't believe that corruption is the issue (which is what this article linked to in another question on RT Error 2501 discusses).

I'm also certain that it's nothing wrong in the Submeasure Form's Form_Load code, since I put a break point on the first line but didn't hit it before getting the error prompt.

The code for the Add Submeasure button follows:

Private Sub New_Sub_Click()
'

'Ensure we have a Key for this measure already NOTE: This Key is associated to each 
    submeasure so a ListBox can query for it in a table that holds the submeasure data
    while I'm doing the data entry
If Me.Msr_Key.Value = "" Then
    'notify
    MsgBox "New submeasures cannot be created until a Key has been provided for the parent measure.", vbOKOnly, "Unable to Create Submeasures"

Else
    'Open the form
    DoCmd.OpenForm "New_SubMsr_Form" 'THIS IS THE LINE THAT ERRORS

    'Set the form mode & Parent_Key values
    Forms![New_SubMsr_Form]![Form_Mode].Value = "New"
    Forms![New_SubMsr_Form]![Parent_Key].Value = Me.Msr_Key.Value

    'Hide the Msr Form
    Me.Visible = False
End If

End Sub

The full code for the New_Msr_Form is below:

Option Compare Database

Private Sub Cancel_Btn_Click()
'

'Clear everything and hide form
Me.Msr_Key.Value = Me.Msr_Key.DefaultValue
Me.Measure_Name.Value = Me.Measure_Name.DefaultValue
Me.Spec_Section.Value = Me.Spec_Section.DefaultValue
Me.Spec_Year_Created.Value = Me.Spec_Year_Created.DefaultValue
Me.Complexity.Value = Me.Complexity.DefaultValue
Me.Inc_Amb.Value = Me.Inc_Amb.DefaultValue
Me.Inc_Medicaid.Value = Me.Inc_Medicaid.DefaultValue
Me.Inc_Medicare.Value = Me.Inc_Medicare.DefaultValue

DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
'

'Restore defaults
Me.Msr_Key.Value = Me.Msr_Key.DefaultValue
Me.Measure_Name.Value = Me.Measure_Name.DefaultValue
Me.Spec_Section.Value = Me.Spec_Section.DefaultValue
Me.Spec_Year_Created.Value = Me.Spec_Year_Created.DefaultValue
Me.Complexity.Value = Me.Complexity.DefaultValue
Me.Inc_Amb.Value = Me.Inc_Amb.DefaultValue
Me.Inc_Medicaid.Value = Me.Inc_Medicaid.DefaultValue
Me.Inc_Medicare.Value = Me.Inc_Medicare.DefaultValue

End Sub

Private Sub Inc_Amb_Click()

'Update the "CheckBox" value correctly
Call Module1.Check_Uncheck(Me.Inc_Amb)

End Sub

Private Sub Inc_Medicaid_Click()

'Update the "CheckBox" value correctly
Call Module1.Check_Uncheck(Me.Inc_Medicaid)

End Sub

Private Sub Inc_Medicare_Click()

'Update the "CheckBox" value correctly
Call Module1.Check_Uncheck(Me.Inc_Medicare)

End Sub

Private Sub Denom_Only_Click()
'

'Update the "CheckBox" value correctly
Call Module1.Check_Uncheck(Me.Denom_Only)

End Sub

Private Sub Msr_Key_AfterUpdate()
'
Dim RowSrc_Str As String

'Is the field empty?
If Me.Msr_Key.Value = "" Or IsNull(Me.Msr_Key.Value) Then
    'Make sure the row source is empty
    RowSrc_Str = vbNullString
Else
    'Populate the Row source string
    RowSrc_Str = "SELECT Temp_SubMsrs.ID" & _
        "   , Temp_SubMsrs.Submeasure Name" & _
        "   , Temp_SubMsrs.Key" & _
        "   , Temp_SubMsrs.Complexity" & _
        "   , Temp_SubMsrs.Denom_Only" & _
    "FROM Temp_SubMsrs" & _
    "WHERE Temp_SubMsrs.Parent = " & Me.Msr_Key.Value & _
    "ORDER BY Temp_SubMsrs.Submeasure Name;"
End If

'User RowSrc_Str to update the Submeasures field appropriately
Me.Submsrs.RowSource = RowSrc_Str
Me.Submsrs.Requery
End Sub

Private Sub New_Sub_Click()
'

'Ensure we have a Key for this measure already
If Me.Msr_Key.Value = "" Then
    'notify
    MsgBox "New submeasures cannot be created until a Key has been provided for the parent measure.", vbOKOnly, "Unable to Create Submeasures"

Else
    'Open the form
    DoCmd.OpenForm "New_SubMsr_Form"

    'Set the form mode & Parent_Key values
    Forms![New_SubMsr_Form]![Form_Mode].Value = "New"
    Forms![New_SubMsr_Form]![Parent_Key].Value = Me.Msr_Key.Value

    'Hide the Msr Form
    Me.Visible = False
End If

End Sub

And the full VBA from the New_SubMsr_Form is here:

Option Compare Database

Private Sub Cancel_Btn_Click()
'
'Clear the form
Me.SubMsr_Name.Value = Me.SubMsr_Name.DefaultValue
Me.SubMsr_Key.Value = Me.SubMsr_Key.DefaultValue
Me.Complexity.Value = Me.Complexity.DefaultValue
Me.Denom_Only.Value = Me.Denom_Only.DefaultValue

'Hide the form
Me.Visible = False

'Unhide the Msr Form (if there is one)
If Not (Me.Parent_Key.Value = "" Or IsNull(Me.Parent_Key.Value)) Then
    'Make the form visible
    'Forms![New_Msr_Form].Visible
End If 'else there is no form to make visible

End Sub

Private Sub Denom_Only_Click()
'

'Update the "CheckBox" value correctly
Call Module1.Check_Uncheck(Me.Denom_Only)

End Sub

Private Sub Form_Load()
'

'Make sure the fields start on the correct values
If Me.Form_Mode.Value = "" Or IsNull(Me.Form_Mode.Value) Then
    'First creation, use default values
    Me.SubMsr_Name.Value = Me.SubMsr_Name.DefaultValue
    Me.SubMsr_Key.Value = Me.SubMsr_Key.DefaultValue
    Me.Complexity.Value = Me.Complexity.DefaultValue
    Me.Denom_Only.Value = Me.Denom_Only.DefaultValue
ElseIf Me.Form_Mode.Value = "New" Then
    'Default values
    Me.SubMsr_Name.Value = Me.SubMsr_Name.DefaultValue
    Me.SubMsr_Key.Value = Me.SubMsr_Key.DefaultValue
    Me.Complexity.Value = Me.Complexity.DefaultValue
    Me.Denom_Only.Value = Me.Denom_Only.DefaultValue
Else
    'Update, get correct values
    'Me.SubMsr_Name.Value = access.
    'Me.SubMsr_Key.Value = Me.SubMsr_Key.DefaultValue
    'Me.Complexity.Value = Me.Complexity.DefaultValue
    'Me.Denom_Only.Value =
End If

'Set focus on name
Me.SubMsr_Name.SetFocus

End Sub

Private Sub Save_Btn_Click()
'
Dim Valid_Sub As Boolean
Valid_Sub = False

'Validate that the submeasure is unique


If Valid_Sub Then
    'Create the Temp_SubMsrs record
    DoCmd.OpenQuery Temp_SubMsr_Create

    'Short cut to clearing & exiting SubMsr form is to now invoke Cancel_Click
    'Me.Cancel_Btn.Click
Else
    'Notify user
    MsgBox "temp prompt text", vbOKOnly
End If

End Sub

Check_Uncheck is a function that basically turns a TextBox into a scalable CheckBox (since the native ones are entirely too small). It's code for context:

Public Sub Check_Uncheck(ByRef TBox As TextBox)
'

'Determine current value
If TBox.Value = "X" Then
    TBox.Value = " "
Else
    TBox.Value = "X"
End If

'Deselect
TBox.SelLength = 0
End Sub

Code can get corrupted too. I suggest a full [Decompile](http://stackoverflow.com/a/3268188/3820271). — Andre, Nov 04 '16 at 17:27
Hmm. What happens if you open "New_SubMsr_Form" manually? -- My next try to solve this would be to import everything into a new database. Or post the `Form_Load` code (are there any other form events with code?) — Andre, Nov 04 '16 at 18:10
If I manually open the New_SbrMsr_Form it launches the form's window just fine. There are several other events with VBA code on both forms. Added the code from both forms to original question — JMichael, Nov 04 '16 at 18:14
Please put [`Option Explicit`](https://msdn.microsoft.com/en-us/library/bw9t3484%28v=vs.84%29.aspx) at the top of each module. It enforces variable declaration and reports undeclared or misspelled variables/constants at compile time. Then run Debug - Compile until no errors are shown. -- To have this automatically in new modules, set the [Require Variable Declaration](http://www.fmsinc.com/microsoftaccess/modules/options/index.html) option in the VBA Editor. — Andre, Nov 04 '16 at 19:57
What is the data type of *Me.Msr_Key.Value* of parent form? In a row source query you treat it as numeric but in `If` logic you treat as string: `Me.Msr_Key.Value = ""`. Try changing to `IsNull()`. — Parfait, Nov 04 '16 at 20:27
@Andre Unfortunately also a bust on adding `Option Explicit`. @Parfait It's string. Not sure how simply referencing the field is treating it as numeric though, in any event I added `IsNull(Me.Msr_Key.Value)` as a part of that same `If`. — JMichael, Nov 04 '16 at 20:36
Parfait forgot to include that adding the `IsNull` check fixed the issue. Submit as an answer so I can accept & get you your credit. Side bar, no earthly reason I can think of that that should have done squat based on all my Excel & Word scripting. MS consistency for the win :| — JMichael, Nov 04 '16 at 20:42

Error 2501 OpenForm action cancelled

0 Answers0