Openform action was canceled in MS-Access VBA code

Question

Hi, I am supporting an application which was running for the past 3 years. It was developed completely in MS Access and written in 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

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.OpenForm command are giving the error in a message box saying:

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.

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

Answer 1

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 think you will get this error if macros are disabled.

Answer 2

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?

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.

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.

Answer 3

• 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?

Answer 4

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 that number!).

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.

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.

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__."

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.

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.

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.

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).

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