在 MS-Access VBA 代码中取消了 Openform 操作

Openform action was canceled in MS-Access VBA code

提问by chinnagaja

I am supporting an application which was running for the past 3 years. It was developed completely in MS Access and written in VBA.

我正在支持一个运行了 3 年的应用程序。它完全在 MS Access 中开发并用 VBA 编写。

Suddenly the application is facing the mentioned error at the following lines:

突然，应用程序在以下几行面临上述错误：

DoCmd.OpenForm FormName:="frmNewPeerGroup", View:=acNormal, windowmode:=acWindowNormal, OpenArgs:=5

FrmNewPeerGroup code

FrmNewPeerGroup 代码

Private Sub Form_Open(Cancel As Integer)

    Dim lDept As Long, lDiv As Long

    lType = OpenArgs 'Supplied by caller
    lAssmtVer = 1 'Current
    sName = ""
    sDescription = ""
    dtCreatedDate = Format(Now(), "dd/mm/yyyy")
    sCreatedBy = UCase(userPerms.NTLoginName)
    lSupervisorID = userPerms.userID
    lTeam = 0

    With cmbBxType
        .RowSourceType = "Value List"
        .RowSource = GetValueListDict(pgType)
        .Value = lType
        .Enabled = (OpenArgs = 1)
    End With
    With cmbBxVersion
        .RowSourceType = "Value List"
        .RowSource = GetValueListDict(pgAssmtType)
        .Value = lAssmtVer
    End With

    mgLogoDesc.Visible = False
    txtBxCreatedDate.Value = dtCreatedDate
    txtBxCreatedBy.Value = sCreatedBy

    If OpenArgs = 5 Then
        lTeam = oActiveAssmt.TeamID
        lDept = GetParentID(aTeams(), CInt(lTeam))
        lDiv = GetParentID(aDepts(), CInt(lDept))
        With cmbBxDivision
            .RowSourceType = "Value List"
            .RowSource = GetValueListArray(aDivs())
            .Value = lDiv
            .Enabled = False
        End With
        With cmbBxDepartment
            .RowSourceType = "Value List"
            .RowSource = GetValueListArray(aDepts())
            .Value = lDept
            .Enabled = False
        End With
        With cmbBxTeam
            .RowSourceType = "Value List"
            .RowSource = GetValueListArray(aTeams())
            .Value = lTeam
            .Enabled = False
        End With
    Else
        With cmbBxDivision
            .RowSourceType = "Value List"
            .RowSource = GetValueListArray(aDivs())
            .Enabled = False
        End With
        cmbBxDepartment.Enabled = False
        cmbBxTeam.Enabled = False
    End If

End Sub

Many instances of the DoCmd.OpenFormcommand are giving the error in a message box saying:

该DoCmd.OpenForm命令的许多实例都在消息框中给出错误消息：

The expression On Click you entered as the event property setting
produced the following error: The OpenForm action was canceled.

- The expression may not result in the name of macro, the name of 
  a user-defined function, or [Event procedure].
- There may have been an error evaluating the function, event, or macro.

This is the error message I am receiving.

这是我收到的错误消息。

My problem is, the same code was running around 3 years, but suddenly some updates to Microsoft or Office might be giving trouble to this code.

我的问题是，相同的代码运行了大约 3 年，但突然间对 Microsoft 或 Office 的一些更新可能会给此代码带来麻烦。

Did anyone come across this error in the past weeks? Please let me know what else we can do to make this work again.

过去几周有人遇到过这个错误吗？请让我知道我们还能做些什么来使这项工作再次发挥作用。

采纳答案by David-W-Fenton

I don't know if this qualifies as an answer, but the code in that OnOpen event is dependent on a lot of outside functions. Specifically, the code is assigning value lists for the RowSources of a bunch of combo boxes. The immediate red flag that occurs to me is that non-SQL Rowsources have a finite length, and in Access 97, that limit was 2048 characters (in Access 2003, it's 32,750 -- don't ask me why it's thatnumber!).

我不知道这是否符合答案，但 OnOpen 事件中的代码依赖于许多外部函数。具体来说，该代码正在为一堆组合框的 RowSources 分配值列表。我立即想到的危险信号是非 SQL 行源的长度是有限的，而在 Access 97 中，该限制是 2048 个字符（在 Access 2003 中，它是 32,750 —— 不要问我为什么是这个数字！）。

So, the immediate thing I see is that perhaps what ever data drives the functions that create those value lists has begun to exceed 2048 characters in length.

因此，我立即看到的是，可能驱动创建这些值列表的函数的数据的长度已经开始超过 2048 个字符。

If that's the actual answer, then you can write a callback function that will return the values in the arrays, and it won't have the limitation on the returned length. You'd set the RowsourceType to the name of your callback function and leave the Rowsource property blank.

如果那是实际答案，那么您可以编写一个回调函数，该函数将返回数组中的值，并且它对返回的长度没有限制。您可以将 RowsourceType 设置为回调函数的名称，并将 Rowsource 属性留空。

An example of the callback function is found in the A97 help (though I can't find the same example in the A2K3 help). In A97 help, you get there by searching for RowsourceType, and then in the help window, click on the link in the sentence reading "You can also set the RowSourceType property with a ____user-defined function____."

在 A97 帮助中找到了回调函数的示例（尽管我在 A2K3 帮助中找不到相同的示例）。在 A97 帮助中，您可以通过搜索 RowsourceType 到达那里，然后在帮助窗口中，单击“您还可以使用 ____ 用户定义的函数____ 设置 RowSourceType 属性”这句话中的链接。

To check this out, you just need to find out the length of the string returned from GetValueListArray() by each of the arrays referenced in the OnOpen event.

要检查这一点，您只需找出 OnOpen 事件中引用的每个数组从 GetValueListArray() 返回的字符串的长度。

It also might be helpful to add an error handler to the OnOpen event, particularly given that there are so many outside dependencies in the code in that particular sub.

将错误处理程序添加到 OnOpen 事件也可能会有所帮助，特别是考虑到该特定子中的代码中有如此多的外部依赖项。

And last of all, let me say that it looks like horrible programming. Most of this ought to be settable with default properties, seems to me. I also question that kind of dependency on OpenArgs with such an undocumented input value. What does "5" mean? And what does "1" mean? Is that documented somewhere? It's just terrible, terrible code, in my opinion.

最后，让我说它看起来像可怕的编程。在我看来，其中大部分应该可以使用默认属性进行设置。我还质疑这种对 OpenArgs 的依赖与这种未记录的输入值。“5”是什么意思？“1”是什么意思？那是在某处记录的吗？在我看来，这真是糟糕透顶的代码。

I'd likely do this with a standalone class module instead, because that will be self-documenting in terms of what does what. You'd set a particular named property to 5 and that would control what the form gets from the class module methods for populating the combo boxes. It would all be in one place, and you could use a meaningful property name to make it clear what the values 5 and 1 represent. It's particularly helpful to do this if you have the same kind of code in the OnOpen event of multiple forms. In that case, it's a no-brainer to move it out of the form modules, and the only question is whether you put it in a regular module or in a standalone class module (as I'm suggesting).

我可能会用一个独立的类模块来做这件事，因为这将在做什么方面自我记录。您将一个特定的命名属性设置为 5，这将控制表单从用于填充组合框的类模块方法中获取的内容。它将全部放在一个地方，您可以使用一个有意义的属性名称来明确值 5 和 1 代表什么。如果您在多个表单的 OnOpen 事件中有相同类型的代码，则这样做特别有帮助。在这种情况下，将它从表单模块中移出是不费吹灰之力的，唯一的问题是您是将它放在常规模块中还是放在独立的类模块中（正如我所建议的）。

Anyway, perhaps none of this is on point, but it might give you some ideas.

无论如何，也许这些都不是重点，但它可能会给你一些想法。

回答by foxfuzz

This thread is very old but I came across the same error and spent a few hours looking for an answer. I was able to find the cause after some time and thought of posting my answer as it may help someone in a similar situation. Creating a application using Access Forms is new to me, so the error message was not directly intuitive.

这个线程很旧，但我遇到了同样的错误，并花了几个小时寻找答案。一段时间后，我找到了原因，并考虑发布我的答案，因为它可能对处于类似情况的人有所帮助。使用 Access Forms 创建应用程序对我来说是新的，所以错误消息并不直接直观。

My forms were Master table data entry forms and configured to be Pop-upand Modalwith Me.Form.Namesent as parameter in the DoCmd.OpenFormcommand using a button (OnClickevent) placed next to the Combo controls on a transaction form to allow user to quickly add new records. This parameter value was picked up in the Form_Open(Cancel As Integer)event and used later to refresh the combo box (Forms!<formname>.Controls!<controlname>.Requery) once data was submitted to the master table using the pop-up form.

我的形式是主表数据录入形式和配置成Pop-up和Modal与Me.Form.Name在发送作为参数DoCmd.OpenForm使用按钮（命令OnClick放在旁边的组合控制交易形式事件），让用户可以快速添加新记录。此参数值在Form_Open(Cancel As Integer)事件中被选取，稍后在Forms!<formname>.Controls!<controlname>.Requery使用弹出表单将数据提交到主表后用于刷新组合框 ( )。

It appears that the Open event doesn't occur when you activate a form that's already open (ref: https://msdn.microsoft.com/en-us/library/office/aa211439(v=office.11).aspx). Each time I received the error, my data entry form was open in Design view in Access. So I closed the form in design mode, and repeated the steps. And Voila! no error!

当您激活已打开的表单时，似乎不会发生 Open 事件（参考：https: //msdn.microsoft.com/en-us/library/office/aa211439( v=office.11) .aspx） . 每次收到错误时，我的数据输入表单都会在 Access 的设计视图中打开。所以我在设计模式下关闭了表单，并重复了这些步骤。瞧！没有错误！

Since I will have more than one forms open, I now need to test and try to use Form_Activate()as recommended in the above MSDN reference link.

由于我将打开多个表单，因此我现在需要Form_Activate()按照上述 MSDN 参考链接中的建议进行测试并尝试使用。

回答by Rune Grimstad

Could it be the security settings is Access? All recent versions of Access has a security settings dialog where you can enable (or disable) macros in the application. I thinkyou will get this error if macros are disabled.

可能是安全设置是Access？所有最新版本的 Access 都有一个安全设置对话框，您可以在其中启用（或禁用）应用程序中的宏。我认为如果禁用宏，您会收到此错误。

回答by Fionnuala

What is the code on the form frmNewPeerGroup? What version of Access are you using? If it is 2003, sp3 causes problems for which there is a hotfix. Have you tried decompile and / or compact and repair?

表单 frmNewPeerGroup 上的代码是什么？您使用的是哪个版本的 Access？如果是 2003，则 sp3 会导致问题，因此有一个修补程序。您是否尝试过反编译和/或压缩和修复？

If you have an original mdb, check the references to make sure that none of them are marked MISSING. This is quite a likely reason for problem in that it has suddenly occurred.

如果您有原始的 mdb，请检查参考以确保它们都没有标记为 MISSING。这很可能是问题的原因，因为它突然发生了。

To check the references, look at Tools->References on the menu for a code window.

要检查引用，请查看代码窗口菜单上的工具->引用。

If no references are missing, you could try stepping through the form code to get a more exact idea of where the error is occurring.

如果没有缺少引用，您可以尝试单步执行表单代码以更准确地了解错误发生的位置。

回答by Renaud Bompuis

• Are you sure one of the required references (VBA IDE > Option > References) isn't missing?

• If you're referencing Excel/Word or external objects, are you sure that the references to the type libraries are the right ones (if you're using specific versions instead of doing late binding)

• Are you building the MDE on a 64 bit machine by any chance?

• 您确定没有缺少所需的参考资料之一（VBA IDE > Option > References）？

• 如果您正在引用 Excel/Word 或外部对象，您确定对类型库的引用是正确的（如果您使用的是特定版本而不是后期绑定）

• 您是否有机会在 64 位机器上构建 MDE？