Vba – Openform action was canceled in MS-Access VBA code

ms-accessvba

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.

Best Answer

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.

My forms were Master table data entry forms and configured to be Pop-up and Modal with Me.Form.Name sent as parameter in the DoCmd.OpenForm command using a button (OnClick event) 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.

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!

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.

Related Topic